Very large excel with MS access

[MayankGoyal2]

Hi, We have a very large excel for which native Interop based Excel VBO or OLEDB object for excel is not working. Kindly suggest some alternates to handle it. One way I was thinking was to load data from excel to ms access and then apply query to filter required data and bring in in another excel as after filter the data will be quite less. Kindly suggest if there is any standard access VBO provided in blue prism which provides commonly used access capabilities like - to load data from excel and execute some simple query on access tables, export data back to excel etc.
I am not sure if this is right approach.

------------------------------
Mayank Goyal
------------------------------

[PratyushGarikap]

It might be easier if you read the file in batches. E.g. Load the first 100 rows in Collection 1, apply filter, push the results into Collection 2, then read the next 100 rows into Coll 1...and so on.

If you believe that the MS Access approach works best for you than you'll have to write a VBO to do that for you. I don't believe there is a VBO to do that already. This link should help - https://social.msdn.microsoft.com/Forums/en-US/ba1f1926-3524-4375-8812-3ff300293b12/import-excel-sheet-to-access-database-using-vbnet

------------------------------
Pratyush Garikapati
ROM Architect
Blue Prism
Asia/Kolkata
------------------------------

[MayankGoyal2]

@Pratyush Garikapati - Thanks a lot for your response. To read file in batches, we first have to open the file and that's when the large excel files crashes. I was however able to develop an alternate approach - I wrote a macro in MS access to load data from excel, execute a query on it, export resultset of query back to another excel and drop access table. To trigger access macro, I wrote a code stage in BP.

------------------------------
Mayank Goyal
------------------------------

[PratyushGarikap]

You don't have to "show" the excel to read it, if that's what you are referring to. Nothing wrong in using a macro, but just watch out for its long term maintenance. Keep an eye out for garbage collection too if you creating and dropping the access database in quick intervals.

------------------------------
Pratyush Garikapati
ROM Architect
Blue Prism
Asia/Kolkata
------------------------------

[MayankGoyal2]

@Pratyush Garikapati ​- I am creating and dropping access table within macro written in access file. With BP code stage, i am opening access file, running macro of access and closing access file. Macro don't have any option for explicitly calling Garbage collection other than to mark all objects as Nothing in macro code. Kindly suggest how can I call Garbage collection in blue prism VB code stage after closing access and will that make any difference?

------------------------------
Mayank Goyal
------------------------------

[PratyushGarikap]

See this - http://portal.blueprism.com/customer-support/support-center#/path/1141772192


------------------------------
Pratyush Garikapati
ROM Architect
Blue Prism
Asia/Kolkata
------------------------------