How to read large excel xlsx files in Uipath

[ma.beascoa]

I have problems when reading a huge excel *.xlsx file. The excel xlsx file has 640.000 rows and 72 columns.

I want to read the excel file and upload the output DataTable to SQLserver or local DataBase to use it instead of normal Uipath Activities due to performance.

When using Uipath.Excel.Activities the activity crashes in a computer with 20Gb RAM.

I have used Excel Aplication Scope

I get the following error: Job stopped with an unexpected exit code : 0xE0434352

Also I have used from marketplace the UipathTeam.XLExcel.Activities
(Read Extra Large SpreadSheets - RPA Component | UiPath Marketplace)

but I got the error :
Read range: The specified package is invalid. The main part is missing.

I have being reading through the forum but didn´t get a clear idea of the right solution : efficient in time and robust.

Hope you have a clear idea on how to solve the issue.
After reading the excel .xlsx file If I want to write the output DataTable to a CSV file should I get the same error? .

I want to operate with the Output DataTable using data manipulation filters. Is this a good idea? Or just send that to an external Database and operate apart from Uipath?

Is this solution working? Large Data and DataTable to DataSet - RPA Component | UiPath Marketplace

Thanks in advance for all your efforts.

[mosa_sleeman (Mousa Suleiman)]

Hi,
Please try to change the extension to.csv and try

Thanks,
Mousa

[Smars]

Using data table is a good start to sort out undesired values.

have you tried Read Range (see documentation)?

I would suggest splitting the excel in several files and looping through it.

Feel free to ask additional questions

[Sarkari_Result]

csv is one of the best or use google doc

[ushu (Usha kiranmai)]

@ma.beascoa Did you try excel sheet as a database

How to use excel sheet data as a database in UiPath - RPA Learners

[muhammedyuzuak]

Hi @ma.beascoa,

Welcome to community.

I’m attaching a link to a similar problem. If the problem persists, please let us know.

Read Large excel file

Hello, I’d like to know how I can work with excel files with more than 70k lines without getting hours to read the lines and set them to a data table. With the actual activities, I’m getting more than 2 hours to read it.

Regards,
MY

[mosa_sleeman (Mousa Suleiman)]

Hello @ma.beascoa
Please mark it as solution if it solved

[ma.beascoa]

I edited the original sample.xlsx file and changed the name to sample.csv
Then I ran the Excel Aplication Scope, Read Range from Uipath.Excel. Activities, as with normal excel files and got the same error.

[ma.beascoa]

I used the ExcelAplicationScope, and Read Range activity from Uipath.Excel.Activities and also a imported a UipathTeams.XLExcel.Activities from marketplace and could not solve the problem.

[Rahul_Unnikrishnan]

Hello @ma.beascoa ,

please refer to the below post as well. Ignore if you have already gone through it.

Can you try to create an excel with 4lac rows and try to open using excel scope and read range activity?

Also will this file size increase or is this the maximum?

How to read huge excel in Uipath

Hi Welcome back to uipath community Hmm we can use EXCEL APPLICATION SCOPE and READ RANGE ACTIVITY in it Though it’s slower (obvious because of 50 lakhs plus records) but is more reliable Even with python or vb net it does the same and won’t differ much Kindly have a view on this thread for more details Cheers @Priyambada_Panda

[mosa_sleeman (Mousa Suleiman)]

@ma.beascoa Can you please try read csv activity?

[fernando_zuluaga]

Hey

standarize your xlsx file as csv file, then use a read CSV file activity, it will be futher realiable and faster as xlsx

Regards

[ma.beascoa]

@How to read huge excel in Uipath

we can use EXCEL APPLICATION SCOPE and READ RANGE ACTIVITY in it Though it’s slower (obvious because of 50 l

I used the EXCEL APPLICATION SCOPE and READ RANGE ACTIVITY using UiPath.Excel.Activities, and also the activities placed in market place from UipathTeams.XLExcel.Activities.
And there is a crash in all of them, It´s not possible to handle such a big excell file without having errors. I use to kill excel process and delay 3 minutes before using these activities.
My CRM aplication only gives me excell files no CSV files. so I need to convert excel to csv files in UiPath .
Thanks for your answer.

[ma.beascoa]

My CRM aplication is providing excel files, nos csv files. My source is this huge excel file that is difficult to manipulate.
I wan to pass the data from excel to database or csv file in order to import it
Thanks for your help.

[Rahul_Unnikrishnan]

@ma.beascoa

Will it be possible to split the excel to 2 different sheets and read each sheet? If the number of rows is making the issue, then can you think of splitting it to 2 different sets and make a try?

[ma.beascoa]

Read csv activity reads xlsx excel files? My source is a huge xlsx excel file.
I want to convert this huge excel file to a csv

I don´t want to work with this excel file for data manipulation because it takes so many time, the robot is taken more than 4 hours…

[ma.beascoa]

Could be a possible solution if Uipath would read the excel file, the issue is that with this file I can not work, even manage to divide in 2 small sheets and work with them separately.
It takes 5 minutes to open the excel file doing it manually. But when applying EXCEL APPLICATION SCOPE it crashes.

I wan to load the excel data to a database, so I can handle those data more efficient.

Thanks for your reply

[connor.orzen]

Hey There! The solution that has worked for me is not using other activity packs, but rather writing code in VBS. In instances with tens of thousands of rows, read ranges take hours. That said, utilizing VBS, I have been able to read an excel table and paste into another sheet in under 40 seconds.

See the below sample code where I read a workbook sheet and copied it to two sheets in another workbook (where each argument represents a filepath):

Dim objArguments
Set objArguments = WScript.Arguments

'Argument testing workflows
'MsgBox objArguments.Count
'MsgBox objArguments(0)
'MsgBox objArguments(1)
'MsgBox objArguments(2)
'Wscript.Quit

Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True

’ Open source workbook
Set objWorkbook1 = objExcel.Workbooks.Open(objArguments(0))

’ Open destination workbook
Set objWorkbook2 = objExcel.Workbooks.Open(objArguments(1))

’ Select the range of cells you want to copy from source workbook. For this example, I’m assuming you want to copy the entire used range.
objWorkbook1.Sheets(1).UsedRange.Copy

’ Paste the data into the “Raw Data Extract” sheet of the destination workbook starting from cell A1. Adjust if you want to paste somewhere else.
objWorkbook2.Sheets(“Raw Data Extract”).Cells(2,1).PasteSpecial
objWorkbook2.Sheets(“Working Data”).Cells(7,3).PasteSpecial
'Delete duplicate headers
objWorkbook2.Sheets(“Raw Data Extract”).Rows(“2:2”).Delete
objWorkbook2.Sheets(“Working Data”).Rows(“7:7”).Delete

’ Save the destination workbook
objWorkbook2.SaveAs objArguments(2)

’ Close the workbooks
objWorkbook1.Close
objWorkbook2.Close

’ Quit Excel
objExcel.Quit

’ Clear the objects
Set objExcel = Nothing
Set objWorkbook1 = Nothing
Set objWorkbook2 = Nothing