cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
juresti
Level 8

excel get all rows - read write limits

This post solves reading all rows in an excel table regardless of limits up to a certain extent.

 

Note: this looping could also be used in sharepoint, etc.. or where ever there is a read limit, write limit, or record related limit.

 

This is a simple flow that will show us how to read all rows from an excel table and write them to a destination or what ever else needs to be done.

 

1. set up variables

In this flow we only need two variables.

One to track a loop and another to collect the excel rows.

 

pic1.PNG

 

2. Get your excel file

First get your excel file since we will need the content value to loop get all rows.

 

3. loop through the rows and collect

This is where we overcome any limits, whether they are reading or writing with the excel connector.

Note: Pagination can be set and limit can be set however it does not result to the specified number of rows.

 

The loop is the key to get all rows in the excel table whether it is 25,000 or 50,000.

Loop set up

pic2.PNG

 

In the loop we use our loop track variable as a flag to run the loop and it will also help us collect the different rows of the excel table.

The Count in the loop should be set to the same number of variable loops. The count can also protect you from infinite loops so it is safe to always use it.

 

4. get the rows inside the loop

Set up your excel connector

pic3.PNG

This is where we tell excel to get all the rows in the excel table.

Notice the skip count formula. This will control which set of rows to get.

 

The formula looks like so:

mul(variables('loopTrack'),2000)

 

Explanation: What I do here is multiply the current loop by the number of rows to skip. Since the first loop is 0 we skip 0 rows at first 0 x 2000. So excel will read from row 0 to the maximum which is 2000 for me regardless if I set pagination it is always 2000 rows read for me. 

So your multiplier should be set to the number of rows your connector can read.

This will cause the second run to skip the first set (1 X 2000 skips 2000) and read the next set, the next 2000.

And so on.... In loop 2 I would have read 2000 so I skip 2000 (2 x 2000) and the flow will read rows 4001 to 6000.

 

As the rows are getting read we will store them in our rows collection array.

We simply append the item. 

pic4.PNG

 

With each loop we collect the sections of the excel table rows until it reaches the end of the rows.

Notice I used filters so my append rows is using the Body of the filter array. You may use the value list of items from excel.

The filters can help your flow identify that there are no more rows.

 

5. set next loop

After the rows are appened we set our variable to the next loop number. This will cause the multiplier to skip to the next set of rows allowing us to overcome the limits.

The limit may limit you to a number of rows per read but it can't limit you if you loop to the next set on a new read.

 

pic5.PNG

This should be below the append rows and inside the loop to get rows.

 

6. process your collected rows array

Now that you have your rows collected in a single array you can process them just as the excel table rows.

Make an apply to each using your collection array and flow will handle it for you.

pic6.PNG

In my case I write them to an sql table.

 

Note: I set my apply to each to use the rowsCollection array.

To reference the columns you can select the list rows present in table fields and flow maps it with the array since it is within the apply to each.

 

 

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
juresti
Level 8

Re: excel get all rows

3 REPLIES 3
juresti
Level 8

Re: excel get all rows

juresti
Level 8

Re: excel get all rows

If you need to overcome a write limit you can probably also use the skip multiplier and write the first set, then the second set, etc...

Of course this probably would require to somehow remove the written rows from the array during the loops.

 

It would work the same way as the read.

suzanna
Level: Powered On

Re: excel get all rows - read write limits

Thank you so much for sharing!! I could not find anything to get over the row limit of 5000, you are a genius! Instead of 'write data', I used 'Add a row into a table' to add the rowCollection into an excel, and this still worked perfectly.

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Kudoed Authors (Last 30 Days)
Users online (4,499)