How to use OLEDB query component.
I have a large excel file with around 300k rows. Issue - loading it to a lookup table, and looping through it, takes hours.
Can someone explain me how to use the OLEDBquery component to run a SQL in pega robotics studio?
Sample query;
(Select A,B,C from (\\network\abc.xlsx : sheet1) where X column = key)
Note: excel file is in a network location.
***Moderator: Pallavi to move from Discussion to Question***
Accepted Solution
Updated: 3 Sep 2020 16:15 EDT
Evonsys
US
Thanks, Thomas! I tried this in my local and it works. But in the client environment, I had to reference more dlls just so that I can use the OLEDb component. So ended up using 'findcelladdress' method in excel connector.
Pegasystems Inc.
US
Here is an example with a simple XLSX file attached to the project. If you are using XLS or another format, you'll need to search for how to create the connection string. The article below shows an example for both XLS and XLSX.
https://www.codeproject.com/articles/1088970/read-write-excel-file-with-oledb-in-csharp-without
Accepted Solution
Updated: 3 Sep 2020 16:15 EDT
Evonsys
US
Thanks, Thomas! I tried this in my local and it works. But in the client environment, I had to reference more dlls just so that I can use the OLEDb component. So ended up using 'findcelladdress' method in excel connector.