Large Data set handling

Help Studio
1

I have a excel with huge data how to read in UiPath with out system getting hanged,please help me in some approaches

2

Hi @Sidharth_S_Kadri
You can convert your excel file to csv and try reading the data it’ll be faster else if you have database you can store the excel file in db and read it from db it might help

3

Hi @Sidharth_S_Kadri

You will hit the memory limitation when you deal with Large File excel files

Excel can be treated like a database table using OLEDB or ODBC connections. This allows you to run SQL queries directly on Excel, which is perfect for large datasets

Please follow the steps

  • Use Database activites
  • Create a DB connection with the following connection string
Provider=Microsoft.ACE.OLEDB.12.0;
Data Source=C:\Users\YourFile.xlsx;
Extended Properties="Excel 12.0 Xml;HDR=YES";
  • You can go ahead query your excel like a database like below
    SELECT * FROM [Sheet1$] Where [Your condition]
4

@Sidharth_S_Kadri

If the excel is too large, you can follow below methods:

  1. Split the file into chunks and process
  2. Read excel in finite range incrementally(example: 1 to 100, 101 to 200, next 201 to 300 likewise)
  3. Use excel as Database (has depenacy on oledb/odbc drivers on VM)
5

@Sidharth_S_Kadri

Use excel as database. Refer this article on how to do it.
https://medium.com/globant/using-excel-as-a-database-in-uipath-e5206120d4a5

6

Hi @Sidharth_S_Kadri

You can try reading the excel using VBA invoke code activity and perform the operations like Read etc. However its execution abit slow,

The optimal way is read the excel as DB, this helps you to get to fetch data writing simple queries.

7

Hi @Sidharth_S_Kadri,

You can try the following approaches:

  • Check with your client if you can get direct access to the data source. This is usually the most reliable and scalable option.
  • You can also check if they can provide it in another format.

Otherwise,

  • If the file contains static data, converting it into CSV or JSON, and then use that file in your automation.
  • If not, you can use Excel as a database and work with it accordingly.

Thank you!