Share
ReportHi,
from onedrive for business, how to read an excel with 100000+records and put the data into an array?
Thanks
Categories:
You’re offline. This is a read only version of the page.
10
Hi,
from onedrive for business, how to read an excel with 100000+records and put the data into an array?
Thanks
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)
Hi @gr84 ,
I hope the following link helps you.
https://www.matthewdevaney.com/fastest-way-to-read-large-excel-table-in-power-automate/
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
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.
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 🙂
All responses (
Answers (
