What is the best practice to deal with large excel files with rows above 50k-100k etc. I have a couple of scenarios where I need to read this excel file in the collection and then perform the lookup, filter, etc.

[Tejaskumar_Darji]

What is the best practice to deal with large excel files with rows above 50k-100k etc. I have a couple of scenarios where I need to read this excel file in the collection and then perform the lookup, filter, etc.

A solution like - Do we need to divide data into multiple parts? Or any other better solution to handle large excel files.

------------------------------
Thanks & Regards,
Tejaskumar Darji
Sr. RPA Consultant-Automation Developer
------------------------------

[mkumar407]

Hi Tejas,

For large files, I try to split data or lower down volume using macro to perform lookup and filters.
Also, you can create your own DLL and later refer it.

Best!

------------------------------
Manish Kumar
------------------------------

[shashank.kumar280]

Hi Tejaskumar ,

There could be different ways like importing data into external database, running macros etc. You can also use OLEDB to retrieve only relevant data from Excel using SQL.

These links have the steps and relevant asset on DX.

https://community.blueprism.com/HigherLogic/System/DownloadDocumentFile.ashx?DocumentFileKey=56f3f77f-6c92-4a75-9e66-0b0ed2f6e054 

https://digitalexchange.blueprism.com/dx/entry/3439/solution/data---oledb

------------------------------
Shashank Kumar
DX Integrations Partner Consultant
Blue Prism
Singapore
+6581326707
------------------------------

[GuggsKia]

Hi Tejas,
I had a same use case, I would suggest you to use OLEDB query to filter out the required data into collection.
Or do you need the whole 50-60k data in the collection?
( I am assuming you might be performing some operations later on to remove duplicates and filter out the required data)

------------------------------
Guggs Kia
------------------------------

[KotaSuto1]

Hi,
it's so inefficient to manipulate such a large data in collection (DataTable),
so if I were you I created temporary table in access or SQL Server, and execute SQL query to them.

To use them, you could use "Data - OLEDB" VBO.
For more information, see the document below:
https://portal.blueprism.com/system/files/2017-09/Blue%20Prism%20-%20Guide%20to%20OLEDB%20v2.pdf

------------------------------
Kota Suto
Japan
------------------------------