Excel read rows unlimit 03-13-2020 12:48 PM - last edited 04-02-2020 12:21 PM

[juresti]

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

[usiala]

Hi,

I haven't tested it yet

but, if it does what you say, then you are a life saver.

Thanks

i will test and come back to you.

BR

[MustH]

@juresti @Yi66 

My Flow is only creating 768 Rows, why is this happening? and the flow ran successfully.!!

The Items are getting created 256 why is this happening?

I have 3000 Rows in my excel and I have set the Top Count to 1000, Skip count to  >> mul(variables('loopcount'),1000)

Help!!!!

[MauroViano]

@MustH in the "List rows present in a table" steps. go to the 3 dots, enable pagination up to 3000. Happened to me too before, that fixes it.

[MustH]

@MauroViano  Well, What if I have 10,000 rows?
it will read only 256 rows? 

Help @juresti 

[MauroViano]

@MustH  as long as you have enough repetitions its ok, the "List rows present in a table" connector comes with a default limit of 256 you can change that with pagination as i explained above.

What you MUST make sure of is that the connector can fetch more or equal the amount of records that you have as "Top count" otherwise you'll mess up (which is what happened to you).

Be aware that you might want to separate your process into smaller steps or have 1 process call child process every X records, as you might probably reach the action Limit quite fast if you want an action on every row and the file is big...  (see https://www.youtube.com/watch?v=DLhwnZ5JRvE for help)

[MustH]

@MauroViano  Ok I understand that

But I have set the top count to 1000, and Pagination to 5000

Now, will I be getting 10,000 records in excel? as I want to get the records and send them email 1 by 1 

[MauroViano]

@MustH 

You'll get your flow stopped by the Action Limit, but yes, with 10 loops of 1000, it should with no problem go to 10.000, the problem you'll face is the action limit as i assumed.

if you wish to act on the 10.000 records you will have to go with parent/child flows (see https://learn.microsoft.com/en-us/power-automate/limits-and-config).

This workaround works for skipping the 5000 pagination limit on free accounts, but does nothing on the "Action Limit" which is 500 actions per flow.

What im trying to say is that this flow has no use for you if you are going to run an action on every row, you will be forced to go with parent/child flows because of the amount of actions you are trying to process in a single flow... So power automate will disable your flow as soon as you get to the 501th record.

My advice: You need to take the same concept of skipping x*500 (x=repetitions) and taking first 500 results, but doing it from a parent flow, executing a child flow for every 500 records on the file, and the child flow should get those 500 records and send the email, and the parent flow should continue with the next 500 and so on...

I hope i have been of assistance. Regards.

[MustH]

@MauroViano  

Well, I understood your point but I am confused in making that Parent child flows. because I am very new to Power Automate and I have to do this task.. 

and one more thing. the person who made this flow is also performing action ( creating an item in SharePoint from Excel file) of 50,000 rows?

how this flow is not getting stopped at 501th? record??? 

[MustH]

@MauroViano  could you please help me making this flow.. 

I will be very thankful to you !!! Please!!

[MauroViano]

@MustHsorry, been caught on work. ill help. First, go to power automate (https://make.powerautomate.com)

Go To Solutions, create a new solution. Create a Flow there that starts with your trigger, then set up a "List Rows Present in a Table", enable pagination to 2k, set up a Variable as the Length of the step "List Rows present in a table".

Create an instant flow(in the same solution) which starts off of a button, add input on that initial step as Number, named "Runs".

now create an action "List rows present in a table", set "Top Count" as 200, Set Skip count as mul( "Runs",200)

Set your send mail action here with the rows from the previous step.

After that set up an action "Respond to Powerapps or Flow"

BACK TO THE ORIGINAL FLOW.

Now you can create the final "Call a Child flow" action, and referr this first flow to the second, once you pick the second flow it will enable a field so you can define "Runs" for the other flow, Runs should be your variable (Length of list rows) plus 1.