topic Excel VBO except standard interop VBO and OLEDB in Product Forum

Excel VBO except standard interop VBO and OLEDB

MayankGoyal2 — Sun, 19 Jul 2020 23:51:00 GMT

Hi,
I am looking for excel VBO that can read large excel effectively, I face crashing issues with standard excel VBO which is using interop and OLEDB. One VBO I found - https://digitalexchange.blueprism.com/dx/entry/7888/solution/excel---open-xml. It works great and read excel very fast, however it needs EPPlus.dll which is paid library.

I also referred microsoft website and found this - https://docs.microsoft.com/en-us/office/open-xml/how-to-parse-and-read-a-large-spreadsheet, looks like this library is free, however not sure if there is any standard VBO available in BP for this or need to create one with custom code?

Any similar VBOs will be helpful. All such library don't need excel software, rather read excel data from underlying XML files and hence very fast by skipping excel related formatting.

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

RE: Excel VBO except standard interop VBO and OLEDB

KieranJohnson1 — Tue, 21 Jul 2020 12:12:00 GMT

Hi Mayank,

I have recently worked with large excel files and experienced the same issues. I found that the OLEDB object was much more stable when working with large excel spreadsheets.

You can find documentation on how to use OLEDB on the Blue Prism Portal: https://portal.blueprism.com/system/files/2017-09/Blue%20Prism%20-%20Guide%20to%20OLEDB%20v2.pdf

Let me know if you have any questions.

------------------------------
Kieran Johnson
Finance Digital Worker Champion
Blue Prism
Europe/London
------------------------------

RE: Excel VBO except standard interop VBO and OLEDB

MayankGoyal2 — Tue, 21 Jul 2020 14:59:00 GMT

@Kieran Johnson - Thanks a lot for your response, I agree OLEDB is better. Kindly suggest how you managed huge volumne of data after getting it in BP collection stage. I asked the same question here as well - https://community.blueprism.com/communities/community-home/digestviewer/viewthread?MessageKey=811b7327-90d5-41dc-aa22-944d26aea3ea&CommunityKey=3743dbaa-6766-4a4d-b7ed-9a98b6b1dd01&tab=digestviewer#bm811b7327-90d5-41dc-aa22-944d26aea3ea but not able to get a response.

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

RE: Excel VBO except standard interop VBO and OLEDB

KieranJohnson1 — Tue, 21 Jul 2020 16:41:00 GMT

I would look to reduce the amount of data you pull into the collection and only pull the data that is absolutely necessary for your automation. For example, I was able to use unique identifiers to establish which lines of the data I need to action that work queue item. I'm afraid without knowing the context of the automation I'm not sure how I can help with this.

------------------------------
Kieran Johnson
Finance Digital Worker Champion
Blue Prism
United Kingdom
------------------------------