Excel rows to SharePoint list

[Anonymous]

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!

[MattWeston365]

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?

[Anonymous]

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: 

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

[MattWeston365 · Accepted solution]

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

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.

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.

[Anonymous]

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

[MattWeston365]

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