Hi,
My process uses OLEDB to select a data-subset from an Excel sheet. Originally I used the Filter Collection action but this turned out to be way too slow.
The used OLEDB steps are:
- Set Connection
Connection string: Provider=Microsoft.ACE.OLEDB.12.0;Data Source="C:\MyFolder\MyXL.XLSX";Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1;";
- Open
- Get Collection
SQL: select * from Sheet1 where (Column1 = 'ABC') and (Column2='1' or Column2='2')
- Close
This results in my collection being populated with data visually matching the SQL statement.
The above seems to work fine but for a minor flaw: If my Excel sheet contains more than 20.000 rows, and most do, the selection is only executed on rows 1 to 20.000, while the rows 20.001 until the end are completely ignored. The result collection from the SQL only contains data from rows 1 to 20.000. When I check the Excel content, the rows past 20.000 do contain data that was asked for in the SQL. So, when I apply the SQL selection as data filters in Excel all relevant data is chosen, also past row 20.000. The OLEDB only delivers some 200 rows where Excel with same filters results in over 10.000 rows.
As this is obviously not what I want, I spent time on Google to find a solution to my little problem. Unfortunately, to no avail.
So my questions to you are:
- Does anyone recognize this tricky behavior in OLEDB?
- Are there any useful tips I can apply to either fix the problem or analyze it further?
Thanks in advance!
------------------------------
Happy coding!
Paul
Sweden
------------------------------
Happy coding!
Paul, Sweden
(By all means, do not mark this as the best answer!)