Read huge excel range into a data table

Help Studio
, ,
1

Hi,
I am trying to read an excel data sheet of size about 150000 rows into a data table with Read Range activity. UiPath is not returning from this activity. Is it okay to read huge data files using Read Range. Are there any better ways of doing this? Thanks a lot,

2

@A_Learner
Fine
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

3

@A_Learner Its valid solution for 51 lakh file. you can try this one

4

Thanks a lot @raja.arslankhan
Will try this solution.

5
6

@A_Learner yeah You can ping me, I am there we can discuss. If you faced any issue we can implement too

7

Hello @A_Learner

As per my understanding based on your system capability read range can read upto 16,777,216 rows. But it can take more time.

Thanks

8

150,000 rows isn’t really that much data. Have you tried waiting for it to finish?

9

Throwing an exception after sometime. Remote Exception wrapping System.Exception. Copied data to another workbook and tried. Same issue. It could be problem with data.

Thank you,

10

Yeah, it could be a problem with the data.

Is this file on a local drive or is it trying to read across a network and/or VPN?

11

Show the entire error. It can’t just be that little bit.

12

@postwick
It is a local file. Thank you,

13

@postwick
Remote Exception wrapping System.Exception
Job stopped with an unexpected exit code 0xE0434352

Thank you

14

@Rahul_Unnikrishnan i
Thank you. Are there any limits on number of columns? I am reading about 80 columns.

Regards,

15

the trigger we often see / get reported from members who shifted then to the package shared with you above

16

Hi @ppr
Is this free package? Can I install it with no legal issues? Is this recommended package?

Thanks a lot,

17

these things can be checked for offered packagages on its marketplace page:

grafik
grafik1428×486 90.3 KB

18

Thank you @ppr

19

Hi @A_Learner

Try to read the file with Excel Application Scope and Read Range inside it.
Avoid Workbook Read Range.

image

image

20

80 columns will not make any problem.

Thanks