How to read large amounts of data of Excel

[Julian (Julián Pérez)]

Hi all,
I am trying to read an excel file and save it data in a DataTable of UiPath, but my datatable does not have all the entries of the excel file when i read it.
For example:

The file has 16409 rows

But the Data table is too short, don’t have all the entries.
I need help with that, if someone know the answer please tell me, thanks all.

[Madhavi (K)]

Hi @Julian

When the data is huge, there will be chances that the system might hang as well. Considering the processing time, I would suggest you to go with batch record processing.

• Use read range activity and read first n records.
• Process the records
• Read next n records for processing
• Repeat same until you reach empty row

In other way, you can also figure out the last row index by opening the excel, goto first cell, send hot key for ctrl+shft+down arrow followed by ctrl + down arrow
This will take you to last row. on the top left edit combo box read the cell address and repeat the batch processing until that address is reached.

Hope this is helpful!

[sreenivasm]

@Julian - Did you check the output from Datatable.Rows.Count and see if it is matching the number of rows in your excel? I think the max rows a Datatable can hold is 16,777,216.

[Julian (Julián Pérez)]

@sreenivasm I checked the output from dataTable.rows.count and apparently all it’s okay, but when i did debugin the datatable seems so short or is just a visual effect?

[sreenivasm]

@Julian - My guess is when debugging, Studio is not showing you all the entries.

[nimin (nimzz)]

Hi @Julian,

Just write the datatable back to an excel file to verify that the datatable holds all your row values. This may be an issue with the studio like @sreenivasm said.