Read an excel with 100000+records and put the data into an array

(0) Share

Report

Posted on by gr84

Hi,

from onedrive for business, how to read an excel with 100000+records and put the data into an array?

Thanks

Categories:

Building flows

I have the same question (0)

All responses (5)

Answers (0)

Sundeep_Malik 6,484 on at

Like (0)

Report

Hey @gr84

Maximum rows you can get in Power Automate is 100000 only and then put in an array.

You will have to into the settings of list rows present in a table and open the option of pagination and add 100000 there, but you will have to be sure you have the license of it.

For free users, 5000 is the limit.

Then for medium tier its around 10k.

And for High tier its 100k.

But if you want to handle more than 100000, you will have to use another list rows present in a table action and in skip count you will have to add 100000 so that it skips first 100000 rows and captures rows after that.

But I will be really honest, excel automation is really slow in power automate, so handling so much data would be really too much. Probably it would be better to use scripts.

@takolota He is an expert in excel automation and might be able to help. And am I correct @takolota ?

I hope this helps 🙂

Was this reply helpful? Yes No
takolota1 4,974 Moderator on at

Like (0)

Report

@Sundeep_Malik

You’re right and 100,000 may take up to 50 minutes to load for the regular connector. And unlike SharePoint there is no easy Excel version of the SharePoint HTTP +nometadata type of method to really speed it up.

If this process is not scheduled / at all reliant on a user waiting for outputs, then it’s really recommended yo switch to at least a SharePoint List.

Was this reply helpful? Yes No
gr84 10 on at

Like (0)

Report

Hi @Sundeep_Malik @takolota,
thank you for your answer, the time of processing isn't a problem because the script start and work in the night.
The problem is that I don't know exactly number of row that probably change every day.
You have an idea?
My purpose is read an insert in array the excel row and execute a query for count number of determinate fields.

Regards

Was this reply helpful? Yes No
necsa 24 on at

Like (0)

Report

Hi @gr84 ,
I hope the following link helps you.

https://www.matthewdevaney.com/fastest-way-to-read-large-excel-table-in-power-automate/

Was this reply helpful? Yes No
takolota1 4,974 Moderator on at

Like (1)

Report

@Necdet @gr84 @Sundeep_Malik

Also if anyone wants a way to do faster Excel reads beyond 100,000 rows without any premium graph calls, you can check this new template method: Faster Excel Reads - Power Platform Community (microsoft.com)

Video: https://youtu.be/W0Yp69ByzGI?si=gE38JKlnIVGxNT7t

Was this reply helpful? Yes No