Excel OLEDB connection

[hacky (NotHacker)]

Hi Team,
(@aksh1yadav, @arivu96, @KarthikByggari, @Karthick_Settu, @ClaytonM, @pathrudu, @PrankurJoshi, @Priya_Pandey, @Divyashreem, )

This is regarding using large excel\CSV files as a Database so as to trigger queries faster and hence get the results without Uipath Robot crashing issue.

So we have very large (excel or csv) files (2 files) (10GB) with 50+ columns and 10,00,000+ rows. Lets say (Excel or csv ) file A and (Excel or CSV )file B.

So I need to trigger few queries and get result in Datatables.

Traditionally, I was using read range and then for each rows activities but since the data was too large, the robot was crashing.

Later, I removed for each rows loops and tried using LINQ queries, there was a positive change but still extremely large files cannot be handled/

Later, I tried using few thousands of chunks as a data input but output was incorrect as the comparisons were wrong.

Now, I learnt from somewhere that using excel as a database and firing the queries will be great approach so I think this is what I shall be doing for accurate results.

Lets say I have two large files in hands (LargeFileA and LargeFileB). Now I need to have these as a database and perform operations over them to achieve the output.

I need to use Excel OLEDB connection so as to achieve the above mentioned action item.

Please help/

Thanks and Regards,
@hacky

[Ellboy (Elliott)]

Is it possible for you to use a SQL database instead? Importing the data into a SQL database is simple and you can use the Uipath.Database.Activities packe to then connect to the database and perform queries very quickly and efficiently. Excel tends to not be too great at dealing with large datasets and SQL on the other hand is pretty much designed to deal with this scenario.

[Vijay_Tulsalkar (Vijay Kumar C Tulsalkar)]

@hacky

Please Refer to the below Thread

How to use Excel as datasource and execute SQL Query?

Hi Team, I am trying to connect Excel as datasource, but not able to connect. I did some research and found following post. But still it is not helpful getting same error as mentioned. I have attached the excel file. I am looking for correct connection string for xlsx or xls and SQL query. Source.xlsx (8.6 KB) Kindly assist.

Connection string for excel

Hi Can any one help me connection strings for excel. what needs to be provided in the connection string for excel. I have scenario where i need to query the excel. I had huge data with multiple columns, i need to use filters for multiple columns and base on each filter i need to set a value for the filtered results. Thanks for the help in advance.

[Divyashreem (Divyashree Muddagangaiah)]

check this out

Sample to use Excel as Database

Hi All, Please find the sample project. This project has all the Insert/Update/Select database queries on Excel file as Database. You have to install the Database activities from the manage package. This will be useful to update/Read a common file in case we are running multiple BOT’s without Orchestrator on multiple machine that file can be placed in the share drive. ExcelAsDB_SampleCommands.zip (20.2 KB) Hope this will be useful for all. Regards HJ