Hi Vrat,
When extracting data from an Excel to a Collection, you generally have two options:
1. Use OleDB with SQL
2. Action 'Get Worksheet As Collection' on the 'MS Excel VBO'
With the OleDB option you can at the same time select what data you want to have. With the 'Get Worksheet As...' option you get all data and have to filter your collection later to remove the data you do not want to have.
I only use the OleDB option for Excels that contain a large number of rows and a lot of selection criteria. The reason for this is that filtering large collections with the 'Filter Count Collection' (the action I use) does not work so smooth as you might think. It does not handle large numbers or rows well and ends up in OutOfMemory exceptions. As a result of that, I divide my collection in lesser collection s and filter each small collection instead of the big one. This works nice but is not fast.
After a recent battle with OleDB, I concluded that even OleDB does not handle large number of rows well and crashes. A general BP problem perhaps? So even when working with OleDB I split up the original Excel in smaller Excels and run OleDB on each smaller Excel to get the results I need. Selecting rows with OleDB is always fast.
What is a
large Excel and what are
many rows?
Depends from case to case. In my case, 15k rows for a 10-column Excel was the upper-limit to avoid errors in OleDB. SO I split my 60k+ row Excel in 15k Excel files that I feed in a loop to OleDB. That worked fine and fast.
Remark:
When posting your question as specific as you did, with a description and the SQL you use, make sure to describe more exactly what you want, as any answer will only be as good as the question.
🙂------------------------------
Happy coding!
Paul
Sweden
------------------------------
Happy coding!
Paul, Sweden
(By all means, do not mark this as the best answer!)
