Excel VBO - Write Collection action crashes with large volumes of data

[PatrickOttery1]

We're having some issues where the Write Collection action in the Excel VBO object crashes if writing large amount of data. It's often only 20,000 records of not huge data, so I'm sure there's a more efficient way to manage this. I've looked as using a two dimensional array and assigning that to the range.Value, and have also tried converting the data table to an ADO record set and then using the CopyFromRecordset method. Both of these cause an exception to be thrown, HRESULT: 0x800A03EC. Has anyone else tried to use either of these methods to write data to an excel worksheet?  

[GertLõhmus]

Are you using a .xslx file? Or a really old Excel?   I would advise to try this: Login to the server as a administrator. Go to ""Start"" -> ""Run"" and enter ""taskmgr"" Go to the process tab in task manager and check ""Show Processes from all users"" If there are any ""Excel.exe"" entries on the list, right click on the entry and select ""End Process"" Close task manager. Go to ""Start"" -> ""Run"" and enter ""services.msc"" Stop the service automating Excel if it is running. Go to ""Start"" -> ""Run"" and enter ""dcomcnfg"" This will bring up the component services window, expand out ""Console Root"" -> ""Computers"" -> ""DCOM Config"" Find ""Microsoft Excel Application"" in the list of components. Right click on the entry and select ""Properties"" Go to the ""Identity"" tab on the properties dialog. Select ""The interactive user."" Click the ""OK"" button. Switch to the services console Start the service automating Excel Test you application again.

[PatrickOttery1]

Hi Gert,   Appreciate the response. For your first question, I'm using an xlsx file with Excel 2010. For your second point, I'm unfortunately not an administrator on that machine, but I will test on a different environment and see if your suggestion helps.   Regards, Patrick.

[PatrickOttery1]

No difference with the DCOM change :( I'm going to setup a visual studio project and try reproduce. The inability to debug code stages in depth is often very challenging with Blue Prism!  

[PatrickOttery1]

OK, I sorted it out.  Using visual studio I was able to see that my conversion from a datatable to array was incorrect. Once converted to a 2 dimensional array correctly, everything worked when assigning the array to the range value. What previously crashed Excel after~20 minutes now takes under 15 seconds to complete 😄

[BenLyons1]

Hi Patrick, I'm having a similar issue and my attempts to write the collection to the clipboard for pasting are so far, unsuccessful. Are you able to share the code you used for your method?

Thanks

------------------------------
Ben Lyons
Automation Specialist
Allianz
Europe/London
------------------------------

[AndreyKudinov]

It is much faster indeed, only other option for handling large datatables with excel is using OLEDB.

There is some minor things you need to take into account. For example when your text value starts with =, you need to escape it or you get error.
Here is my action for writing collection as array (select it all, copy and paste into Excel VBO as Action, it should work):
https://raw.githubusercontent.com/aikudinov/BlueprismVBO/master/Excel%20actions/Write%20Collection%20Direct.txt
It is missing in/out collection "Collection_GC", because I made it global and clean it up manually when memory is an issue or when process ends, but you can just create in on that action page - next thing you end up is blueprism getting OOM ​on large collections. It does batching too - helps to lower rowlimit sometimes when you get OOM, but makes things somewhat slower.

------------------------------
Andrey Kudinov
Project Manager
MobileTelesystems PJSC
Europe/Moscow
------------------------------

[BenLyons1]

Thanks for the code. I've got it working, but for some reason it's really slow. When I set the max row to 50,000 it only writes the column headers and when I set the max row to 0 it runs, but takes a long time.

I'm writing about 50,000 rows and 20 columns. Previously it's taken less than an hour, but now the Excel VBO action's not working for me. So I wanted to try something else, like this.

I'd love to get it running via OLEDB, but my SQL is weak. Just fees like there should be a way of doing it with out going 1 row/cell at a time.

------------------------------
Ben Lyons
Automation Specialist
Allianz
Europe/London
------------------------------

[AndreyKudinov]

@Ben Lyons Default rowlimit (batch of rows is 5000), you can leave it blank and it should work. You should only set it lower if you are getting out of memory errors. If you set rowlimit to 1 or less, it will write 1 row at a time... you dont want that.

You created Collection_GC inside that vbo action, right?




------------------------------
Andrey Kudinov
Project Manager
MobileTelesystems PJSC
Europe/Moscow
------------------------------

[BenLyons1]

Hi Andrey,

Ah, I thought it was a total row limit, I clearly read the code wrong.

Thanks, it works really well, perhaps something worth adding to the DX?

------------------------------
Ben Lyons
Automation Specialist
Allianz
Europe/London
------------------------------