armando.rueda

New Member
Hello, I have a collection of 20,000 rows and I can´t write them in an excel, I don´t know how to write it by code or if there is some other way, thank you.
 

VJR

Well-Known Member
Hi armando.rueda,

Have you used the 'Write Collection' action of the Excel VBO?
If you are facing any transfer issues due to large volume of data then you can split the collection.
And this can be done by using the 'Copy rows' action of Collection Manipulation to transfer multiple chunks of data one by one into another collection and then writing that collection to Excel each time.
 

Marck12

New Member
Collections: These are a very important part of VBA. If you have used the language for any length of time then you will have used Collections. The most common ones are the Workbooks, Worksheets, Range and Cells collections.
 

armando.rueda

New Member
Hi armando.rueda,

Have you used the 'Write Collection' action of the Excel VBO?
If you are facing any transfer issues due to large volume of data then you can split the collection.
And this can be done by using the 'Copy rows' action of Collection Manipulation to transfer multiple chunks of data one by one into another collection and then writing that collection to Excel each time.
That was exactly what I did, but writing the 20k is very slow, is there any other way?
I really appreciate your response, thank you Sr.
 

VJR

Well-Known Member
That was exactly what I did, but writing the 20k is very slow, is there any other way?
I really appreciate your response, thank you Sr.
Can you give some details like-
i. how many rows have you tried splitting?
ii. what is the time it takes to write those many rows (as in No i.) to Excel?
iii. what is the time it takes to write the entire 20K rows in spite of splitting?
iv. how many columns have you got in the collection?
 

armando.rueda

New Member
Can you give some details like-
i. how many rows have you tried splitting?
ii. what is the time it takes to write those many rows (as in No i.) to Excel?
iii. what is the time it takes to write the entire 20K rows in spite of splitting?
iv. how many columns have you got in the collection?
Yes sure,
i. I divided the collection into 10 sub collections, each one of little more than 2k.
ii. It takes about 5 minutes each sub collection (it's too much).
iii. Almost 50 minutes, but almost always fails before it ends, the client freezes and no longer responds.
iv. From A to AR, 44 columns.
 

VJR

Well-Known Member
Hi armando.rueda,

I created a collection with 20K rows and 44 columns and ran it to write to Excel. I faced the same issues as yours - took too long and had to terminate BP because I had get back to more researching.
So this appears to be by design since it is happening on both of our machines (mine is even slower on a Virtual
machine).
So then I thought to research on splitting the collection columns into 22 each to see if that is causing the problem.
That too produced the same issue of taking too long before terminating the process.
This implied that it is the number of rows that are causing the issues.
So I ran the process to write a chunk of 1K records to excel this time will all the 44 columns. This took 4 mins 12 secs.
Then ran with a chunk of 500 records which took 01 mins 03 secs and this one was comparatively quick without any system issues.
Now using the same chunk logic of 500 records per "write to collection", I wrote 2K records which took 4 mins 30 seconds. If we go by this logic then below could be the timelines.

4 mins 30 secs - 2K rows when broken into chunks of 500
9:00 for 4K
13:30 for 6K
18 for 8K
22:30 for 10K
27 for 12K
31:30 for 14K
36 for 16K
40:30 for 18K
45 min for 20K which is almost the same as you encountered ie; 50 mins for the 20K records.

Also I noticed that even through Blue Prism "appeared" to be frozen it was actually running the process in the background as I was able to scroll Excel and literally see the records being written to each row of Excel.
So based on the above observations may be a chunk of 500 records looks good from a system stability and quickness point of view OR you could increase it to 1K or you can also stick to your original approach as the total time is almost the same.

One thing to be noted is that you do not have to create those many collections based on the chunks you make. You can maintain a Counter data item and use the Copy Rows action to copy 500 rows in each iteration to another collection and then write that collection to Excel maintaining the position from where to write in the Excel which is basically the number of rows transferred + 1.
 

armando.rueda

New Member
Hi armando.rueda,

I created a collection with 20K rows and 44 columns and ran it to write to Excel. I faced the same issues as yours - took too long and had to terminate BP because I had get back to more researching.
So this appears to be by design since it is happening on both of our machines (mine is even slower on a Virtual
machine).
So then I thought to research on splitting the collection columns into 22 each to see if that is causing the problem.
That too produced the same issue of taking too long before terminating the process.
This implied that it is the number of rows that are causing the issues.
So I ran the process to write a chunk of 1K records to excel this time will all the 44 columns. This took 4 mins 12 secs.
Then ran with a chunk of 500 records which took 01 mins 03 secs and this one was comparatively quick without any system issues.
Now using the same chunk logic of 500 records per "write to collection", I wrote 2K records which took 4 mins 30 seconds. If we go by this logic then below could be the timelines.

4 mins 30 secs - 2K rows when broken into chunks of 500
9:00 for 4K
13:30 for 6K
18 for 8K
22:30 for 10K
27 for 12K
31:30 for 14K
36 for 16K
40:30 for 18K
45 min for 20K which is almost the same as you encountered ie; 50 mins for the 20K records.

Also I noticed that even through Blue Prism "appeared" to be frozen it was actually running the process in the background as I was able to scroll Excel and literally see the records being written to each row of Excel.
So based on the above observations may be a chunk of 500 records looks good from a system stability and quickness point of view OR you could increase it to 1K or you can also stick to your original approach as the total time is almost the same.

One thing to be noted is that you do not have to create those many collections based on the chunks you make. You can maintain a Counter data item and use the Copy Rows action to copy 500 rows in each iteration to another collection and then write that collection to Excel maintaining the position from where to write in the Excel which is basically the number of rows transferred + 1.
I am really impressed with your research, I thank you very much and I hope that soon Blue Prism can solve this problem, since it is really necessary to handle large volumes of data in Excel.
 

Debduti M

New Member
Is there any other alternative to this? Splitting collection and then ultimately writing it seems redundant. I have a dynamic collection from SAP. need to write it to excel.. but each line takes one sec. So any new idea / help ?
 

to_mas_re

New Member
Hi all,
I came across this problem and this is my solution.

Use Strings::Get Collection as CSV to transform your large collection into CSV text data item. Use File Management::Write Text File to create .csv file from your CSV-like text data item. Use MS Excel VBO::Import CSV to import your new .csv file into excel (you can set specific cell to start from).

I also changed the Import CSV action to overwrite whatever is in selected range (.RefreshStyle = 0 'xlOverwriteCells) and I have created new parameter to set starting row of your .csv file, because I don't need headers for example (.TextFileStartRow = StartRow ... StartRow is new input)

Waiting time for 1000 rows is less than a second - I didn't try this for larger collections but I can imagine it is scalable solution.

I think this is so far the best solution out there.

Good luck ;)
 

to_mas_re

New Member
I tried this solution, and it worked perfectly fine. Can you also please tell me how can I remove the first row from csv before importing it in actual sheet?

Hi,

you can either start importing from different row by setting StartRow variable, or if you need to delete the row from CSV itself, you can delete the row from collection from which you have created the CSV. Use Collections:Remove Row (this will remove current row, so probably use Loop to get to row you want to remove).

Hope I was of any help.
 

ashibm

New Member
Hi all,
I came across this problem and this is my solution.

Use Strings::Get Collection as CSV to transform your large collection into CSV text data item. Use File Management::Write Text File to create .csv file from your CSV-like text data item. Use MS Excel VBO::Import CSV to import your new .csv file into excel (you can set specific cell to start from).

I also changed the Import CSV action to overwrite whatever is in selected range (.RefreshStyle = 0 'xlOverwriteCells) and I have created new parameter to set starting row of your .csv file, because I don't need headers for example (.TextFileStartRow = StartRow ... StartRow is new input)

Waiting time for 1000 rows is less than a second - I didn't try this for larger collections but I can imagine it is scalable solution.

I think this is so far the best solution out there.

Good luck ;)
Perfectly explained and worked flawlessly! thank you! You are a life saviour
 
Top