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***
Posted: 1 month ago
Accepted Solution
Posted: 1 month ago
Updated: 1 month ago
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.
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