cancel
Showing results for
Did you mean:
Highlighted
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.

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

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

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.

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.

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.

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.

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.

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
Level 8

2 REPLIES 2
Level 8

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.

Announcements

#### Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

#### Power Platform 2019 Release Wave 2 Plan

Features releasing from October 2019 through March 2020.

#### Flow Community User Group Member Badge

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

#### Microsoft Learn

Learn how to build the business apps that you need.

#### Power Platform World Tour

Find out where you can attend!

#### Webinars & Video Gallery

Watch & learn from the Flow Community Video Gallery!

Top Kudoed Authors
Users Online
Currently online: 177 members 5,103 guests
Recent signins: