Title:
Excel read rows unlimit
Description:
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.
Detailed Instructions:
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
mul(variables('loopTrack'),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.
Questions:
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
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)
@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.
