cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

Excel rows to SharePoint list

Hi, I'm a new flow user and created a flow to take the rows of an Excel sheet and transfer it to a SharePoint list. When I ran my flow, however, all of the rows from my Excel sheet transferred to my SharePoint list except for the last 13 rows. I tried refreshing the SharePoint list and running the flow a few times after, but I have no luck in getting the remaining rows to my list. I'm confused by this considering that I have only 270 rows in my Excel sheet, which is well below the maximum number of items for SharePoint lists. Do you have any suggestions for how I can remedy this? Any help is appreciated. Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Hi @Anonymous the issue is that the Excel connector will only return a maximum of 256 rows before it starts to paginate.

 

The way that I have approached this is to essentially keep calling the spreadsheet until I have got all of the rows. This almost feels like old school programming, having to paginate until your detect EOF (end of file).

 

In my Flow, I have declared 3 variables to start off with:

  • EOF - This is a boolean which represents whether I am at the end of the file or not
  • Iteration Count - This is a number which will increment by 1 each time I go round. This will be used to calculate the number of rows to skip on each loop.
  • RowCount - An integer which will count the number of rows that are retrieved on each call to Excel

Flow1.PNG

 

Next, I have a "Do Until" loop which will keep looping until the EOF variable changes from false, to true. Within my loop I have my "List rows present in table" action which is configured in the standard way. The only addition is that I have expanded "Advanced Options" and supplied an expression into the skip count. The expression is:

mul(256,variables('Iteration Count'))

The idea of this is that on the first iteration, the iteration count is 0, therefore the call will start from Row 0 (the first row) in the table. On the second iteration, iteration count is 1, therefore it will start at row 256 (1 x 256) and so on.

 

For my own sanity, I Set the RowCount variable to display the number of rows which are found, by using the expression:

length(body('List_rows_present_in_a_table')?['value'])

In my test I had 300 rows, therefore the first time through this was 256, the second time it was 44.

 

I can then do a condition on this value, and I am assuming that if my row count is 256, then I'm not at the end of the table. If it is less that 256, then I'm at the end and I can switch the EOF value from false to true. This will then break the loop. If I do need to go round again, then I increment the iteration count by 1.

Flow2.PNG

So looking at your Flow, your "Apply to each" loop will need to sit insde the "Do until" loop, so that it will keep checking and creating rows within SharePoint until all rows in Excel have been processed.

 

 

 

 

 





Did my reply help? Please give it a thumbs up.
Did I answer your question? Please mark my post as a solution!
If you liked my response, please consider giving it a thumbs up. THANKS!


Proud to be a Flownaut!





Community Leader: Black Country PowerApps & Flow User Group

View solution in original post

5 REPLIES 5
Highlighted
Super User
Super User

Hi @Anonymous . When you look at the run for your Flow, does it show successes for creating all of the rows? I.e. does it show 300 successful iterations of the loop or is it erroring after a specific entry?

 

To find this, navigate to your Flow management screen and select a run from the history list. You'll then be able to expand out each step to try and aid with debugging.

 

Also if possible, could you please supply a screenshot of how you have built your Flow?





Did my reply help? Please give it a thumbs up.
Did I answer your question? Please mark my post as a solution!
If you liked my response, please consider giving it a thumbs up. THANKS!


Proud to be a Flownaut!





Community Leader: Black Country PowerApps & Flow User Group

Highlighted
Anonymous
Not applicable

Hi @MattWeston365, thank you for your suggestions. The flow actually ran successfully, which I'm not sure as to how if rows were excluded. I'll post my screenshots below: 

 

flow 1.PNGflow 2.PNGflow 3.PNG

In Condition, the first function is: empty(body('Get items')?['value']). Thank you!

Highlighted

Hi @Anonymous the issue is that the Excel connector will only return a maximum of 256 rows before it starts to paginate.

 

The way that I have approached this is to essentially keep calling the spreadsheet until I have got all of the rows. This almost feels like old school programming, having to paginate until your detect EOF (end of file).

 

In my Flow, I have declared 3 variables to start off with:

  • EOF - This is a boolean which represents whether I am at the end of the file or not
  • Iteration Count - This is a number which will increment by 1 each time I go round. This will be used to calculate the number of rows to skip on each loop.
  • RowCount - An integer which will count the number of rows that are retrieved on each call to Excel

Flow1.PNG

 

Next, I have a "Do Until" loop which will keep looping until the EOF variable changes from false, to true. Within my loop I have my "List rows present in table" action which is configured in the standard way. The only addition is that I have expanded "Advanced Options" and supplied an expression into the skip count. The expression is:

mul(256,variables('Iteration Count'))

The idea of this is that on the first iteration, the iteration count is 0, therefore the call will start from Row 0 (the first row) in the table. On the second iteration, iteration count is 1, therefore it will start at row 256 (1 x 256) and so on.

 

For my own sanity, I Set the RowCount variable to display the number of rows which are found, by using the expression:

length(body('List_rows_present_in_a_table')?['value'])

In my test I had 300 rows, therefore the first time through this was 256, the second time it was 44.

 

I can then do a condition on this value, and I am assuming that if my row count is 256, then I'm not at the end of the table. If it is less that 256, then I'm at the end and I can switch the EOF value from false to true. This will then break the loop. If I do need to go round again, then I increment the iteration count by 1.

Flow2.PNG

So looking at your Flow, your "Apply to each" loop will need to sit insde the "Do until" loop, so that it will keep checking and creating rows within SharePoint until all rows in Excel have been processed.

 

 

 

 

 





Did my reply help? Please give it a thumbs up.
Did I answer your question? Please mark my post as a solution!
If you liked my response, please consider giving it a thumbs up. THANKS!


Proud to be a Flownaut!





Community Leader: Black Country PowerApps & Flow User Group

View solution in original post

Highlighted
Anonymous
Not applicable

@MattWeston365  This solution worked, thank you very much for your extensive help!

Highlighted

@Anonymous  you are most welcome. If there's anything else we can help with, please don't hestitate to post.





Did my reply help? Please give it a thumbs up.
Did I answer your question? Please mark my post as a solution!
If you liked my response, please consider giving it a thumbs up. THANKS!


Proud to be a Flownaut!





Community Leader: Black Country PowerApps & Flow User Group

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

Top Solution Authors
Top Kudoed Authors
Users online (11,585)