03-13-2020 12:48 PM - last edited 04-02-2020 12:21 PM
Excel read rows unlimit
This flow helps you read excel data rows beyond your limit for any plan.
The data is read in chunks, written to temporary text files, then read back in and processed.
In loop get all rows control the number of loops by setting the value is equal to "number".
The change limits should match the equal to value. I could not find documentation on this but this also controls the number of loops.
To come up with the number of loops, you will need to do some math.
For example, I want to read 10,000 rows at 500 each loop, so that makes 20 loops. 20 x 500 = 10,000.
You will also need some math in the list rows present action step.
The top count and skip counts will control which set of data is read each loop.
Using the same example of 500 each loop, set top count to 500 and the skip with the formula to multiply the loop by 500
The JSON action is where you need to set your own columns and types.
You should be able to figure out the column name and data type structure by looking at the Parse JSON.
Then you can set your columns and data types and even add more columns.
This flow writes each record into sharepoint, the destination can be changed.
Please post any questions.
Anything else we should know:
Note that your hourly, items per 5 minute, and other limits can not be over come.
That is why this flow does not use parallelism to help keep it under the those limits, and it can't use parallelism due to the use of variables within the loops.
Yes, the flow can take a long time to run on large data, yet it achieves the goal of automating it for you while you sleep.
So it is probably best suited for plans where you can only read less than 5,000 rows and the file has less than 100,000 rows.
My sample run of 50,000 rows written to sharepoint from excel
Yes, I was waiting for you reply 🙂
well, my issue is I have a client who has an excel file which he exports from his on-prem database sql and do some calculations there and then it send that excel to a mail merge and it starts sending email to the people which are in there.
so, they wanted this to be done by Power Automate, like if they give the excel file to PowerAutomate then flow should run and it starts sending emails to the user and also the attachment of pdf of their email body too.
the Excel rows would be 8000+ or less
so, can you please tell me about that? Its really needed and I am stuck!!! Please help!!
@MustH How will you/the flow receive the file? Will it be dropped in a sharepoint folder? will it be sent by email?
This is all i can help, this is what i would TRY:
YOU NEED TO CREATE A SOLUTION, and there create 2 flows:
1. Parent Flow:
2. Child Flow:
Haven't Tested it, but in theory it should Work...
hope it helps to get you started...
check here for the pdf part (https://www.youtube.com/watch?v=Rk-FV3CMLw0) (i've never done this)
Sorry, for late reply
The file maybe uploaded to Sharepoint document library which will have those 8000+ rows. and then the flow should run and send emails to those 8000+ people according to a criteria
Well, the same thing i sent you should work but changing the initiation by "when a new file is created in a sharepoint folder", everything else pretty much the same...
You can use this exact flow to send the emails.
At the step where I write to sharepoint, instead send the email. At this step you have the necessary columns to grab and insert into the email body.
This step is the key to the flow, the data can be inserted anywhere at this step. It can be sharepoint, email, sql, streaming PBI dataset, anything.
Also, not sure about your plan but try running sets of 250 or 500 instead of 1000.
I have exactly used your flow and changed it according to my needs
I was also creating records in SharePoint from my Excel which has 3000 records... I tried doing it... but the flow only created 745 rows only and after it got finished.
I don't know whats the problem? and what step count should I set as I tried changing the Threshold value too..
can you guide me onto that?
My requirement is to send 8000 emails quaterly and the emails will be from Excel sheet.
The ammount of repeats times the "TOP count" you set in the List rows connector HAS to be equal or above the maximum possible rows, then you are set...
@MustH Make sure the pagination is on in excel like @MauroViano said and the top count in excel action as well and within the skip formula.
I would also try leaving the threshold empty in pagination.
Also check that the for each and do until loop has concurrency control turned off. Double check the looping settings.
And don't run more than 256 per loop if that is all you are getting.
Instead set it to 250 per loop.
So you will need at most 40 loops. 250 * 40 = 10,000 records max.
You may need to change the loop until from PT1H (one hour timeout) to more hours.
Also show us your excel output action details after a run. That could reveal the reason it only reads 256 rows.
But that is the point of this flow however, so you can set it at 250 and let it loop. It may be your plan Max of 256.
I will show you the excel file but I had created a dummy excel file with like 3000 rows just to test the flow and I have set my email on row no 1000, 2500, 3000
if the excel file is read completely I should have got 3 emails? as if all the rows are being read by the flow?
can you check my excel file from this link (it's not the actual one, but I just made a dummy excel file.)