Question
2
Replies
52
Views
Posted: August 23, 2020
Last activity: September 3, 2020
Solved
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
Posted: 5 months ago
Updated: 5 months 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