How to read huge excel in Uipath

Hi,

Excel file is having four sheets .in one sheet 51 lakh data count is there.Read range is taking more than 30 minute time .please suggest what needs to be done.

2 Likes

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

2 Likes

I have tried with App integration excel activities also.while reading the excel sheet by using Read range ,below error is coming.
“Uipath robot has stopped working”.please suggest

Yah that’s what I was wondering like the maximum limit is 10 lakhs records only which can processed with UiPath but you said it’s working with 50 lakhs records but in a slower pace
Fine it happened
It’s due to large number of records buddy
That’s the reason for this issue

Cheers @Priyambada_Panda

Sir, I checked total count which is having 5155444 count .But while checking column wise count is showing 863383. Please let me know if we can process to get the value to put in to the data table by using uipath

Sir, As you said Maximum limit of excel is 10 lakhs.Are you referring to Excel sheet total count or column wise count?

1 Like

That rows count @Priyambada_Panda
And MS Excel itself is limited to 1,048,576 rows by 16,384 columns

Cheers

3 Likes

Hi @Priyambada_Panda,

I would recommend that you read the data in chunks, if you’re looking to filter the result you could use the importrow method to build up the results datatable.

You could break the read scope to read from one row range to another and loop it in to append it to the data-table… Hope this works with you :slight_smile:

1 Like

Fine
I would go @Shubham_Varshney
Like we can split the data and read them
The sequence of activities will be like
— use a Build datatable activity and create a same structure datatable as in excel with same column name and order of columns
and get the output as Finaldt
And followed by this use CLEAR DATATABLE ACTIVITY and mention the input as Finaldt

—the inside excel application scope use Read range activity we can set the range like this
“A1:G1000000”
Which will give us first set of datatable and name it as dt1 and here in the property panel enable add headers

—then use again a READ RANGE activity and disable the add headers property and mention the range like this
“A1000001” so that it will start from were we left
Where get the output as datatable named dt2

—now use a For each row loop and pass dt2 as input
And inside the loop use Add Datarow activity and mention the ArrayRow as row.ItemArray and datatable as final dt
So all the records from dt2 will now be added to Finaldt

—then finally use MERGE DATATABLE activity where mention the source as dt and destination as Finaldt

So Finaldt will be our datatable with all records from excel
Though it takes the same time or even less than normal excel application scope and read range activity, it will work without any fail or error

Hope this would help you
Cheers @Priyambada_Panda

7 Likes

Just a thought @Palaniyappan

I think this would work if we convert the whole thing to the Database :slight_smile:

Easy to fetch and process the data as well :slight_smile:

4 Likes

@Priyambada_Panda

Hi Priyambada_Panda,

You can make chunks of datatable and later you can use.

ChunkDataTable.zip (155.4 KB)

Hope this helps !!!

Thanks,
Aman Sheik

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.