Get Worksheet as Collection (Fetch Data With Method = "Text" is super slow)

[NicolasSahli]

So if I use the MS Excel VBO (Version 10.1.10) Action "Get worksheet as collection" with the Fetch Data With Method = "Text", it takes forever to read an Excel file with 3k rows. If I use the Fetch Data With Method = "Value" it just takes seconds. 

My problem is, that in this Excel file i have numbers that start with 0 (ex. 0235312). And if I use the Method "Value", 0 at the start of the number will be deleted (Number in collection = 235312). In the process I need the complete Numbers including the 0. The same issue occurs if I use the Action "Get worksheet as collection (Fast)"

Is there any solutions to this Problem? Such that i can Read the Excel file quick and the "0" doesn't get lost.

[ChrisCutting]

Does it really matter how long it takes? Although annoying when you are testing, in real working a minute or so waiting for a collection to load rarely matters as it's only robot time.

Couple of options . . . 

Add logic to your process so that data missing the leading zero has it added back in at runtime

Adjust your data feed so that the excel values all have N_ added as a prefix, the use the Replace function to remove it at runtime

Try using Utility - File Management which has 'Get CSV as Text' / 'Read lines' / 'Read All Text' 

[PvD_SE]

Hi Nicolas,

While 3k rows is not that many, the data volume depends also on the number of columns. A moderate row count combined with many columns may result in the observed slowness. If the data volume becomes too large, you'll make acquaintance with the notorious 'Out Of Memory' error.

Your quantification 'for ever' perhaps is a local term to indicate a precise timespan. But, as remarked earlier, it doesn't matter all that much how long things take. After all, time is relative, at least in theory (https://en.wikipedia.org/wiki/Theory_of_relativity).

For the ideal combination of highest speed and minimal memory usage, I'd like to suggest using the OLEDB function that will get the data from the XL using SQL. There are quite a few posts on this forum explaining how it work.

Happy Coding!
Paul
Sweden

[NicolasSahli]

The Excel file has 30 columns which are mostly filled out. And with forever i mean it takes around 10 mins to execute this action. The weird thing is on the older version of the VBO it was possible to read the entire file in matter of 2-3 seconds (I cant tell exactly what version it is). Now the new (10.1.10) Version takes this long for the same action.

And thank you Paul for your suggestion. I will look into this OLEDB function.