Copy Large Data Set from Excel to New Excel Workbook

[Anonymous]

Hi all I have an Excel workbook with a large dataset (~20,000 lines, 10 columns), which I need to copy into another separate Excel workbook. I have tried getting the data to a collection and writing it to the new workbook but this is very slow. I have also tried extracting the collection as a CSV, however I cannot import it into the workbook as that action inserts new columns, which I can't have. Also, the inbuilt "Copy and Paste Worksheet Range" does not work. Does anyone have any ideas? Thanks!

[Denis__Dennehy]

For very large datasets like that you may have problems with collections - .Net datatables (which a collection actually is behind the scenes) have issues trying to store such massive datasets because of how they reside in memory.  Options for you are therefore to play around with copying over your data in smaller chunks, or use the Copy and Paste actions within the Excel VBO (so that the data is never brought into Blue Prisms memory - just within the clipboard), or otherwise try using the OLEDB VBO to interface with Excel (there are other threads in the forum about that).

[TomaszAdamski]

Last days I had issues with using collection with less than 400 rows. I get out of memory message. I do nothing sophistcated, just looping through the collection (and eventually remove rows). So I believe it is a good practice to design the process from the beginning to work with chunks of data not all of them at once.

[MelissaSuarez_G]

Also, you might want to disable the logging for the stages that loop through a collection, otherwise all that data would be stored in Blue Prism database.