Appending rows to a SharePoint list from Excel spreadsheet

[LMDev]

I have a system that I have no API inputs to. I have it dropping spreadsheets into a document library. I need it to take those reports and drop them into a list.

Since I do not know the Table dimensions of the reports as they drop, how do I make this dynamic?

[v-yetonggu-msft]

Hi @LMDev ,

Please insert all data into a table in the worksheet.

Delete the 'List rows present into a table' control and re-add this control to achieve synchronous updates of flow and data source. Flow can retrieve your newly inserted table.

Best Regards,

Sunshine Gu

[LMDev]

Hi @v-yetonggu-msft, I already did that, and it still couldn't find it.

Is there a way I can automate turning the spreadsheet into a table and get all rows after the header without manually working on the spreadsheet?

[AlexEncodian]

@LMDev 

I think you have two options.

Use Excel's "Create table" action to convert your data into a table. As you say, you need to input your table dimensions so this may not work. If the number of columns is fixed, you could have an arbitrarily large row number and then try to filter out empty values in the flow. One possible issue is creating the table in the flow and later listing its rows...sometimes the Excel file doesn't refresh.

Your second option is using Encodian's Get Rows from Excel action which does not require tables. Again, insert an abritrarily large row number to cover all scenarios but then use the "Exclude empty rows" configuration to automatically capture populated rows only.

[v-yetonggu-msft]

Hi @LMDev ,

Theoretically it's possible, but you don't know the dimensions of your table. That is to say, in the step of creating the table, you need to fill in the dimensions of the table, but you don't know it, so you can't use the create table and then traverse every row in the table.

My guess is that your flow and data source are not updated synchronously. You inserted a table, and the 'list rows present into a table' control cannot retrieve it. Delete the 'list rows present into a table' control and re-add this control to retrieve it. But I'm curious, you can put complete data into a table, why don't you know the dimensions of the table? If it's convenient, can you post a screenshot of your worksheet?

Best Regards,

Sunshine Gu

[LMDev]

Hi @v-yetonggu-msft ,

I can't give a screenshot of the worksheet because it is filled with people's info.

I know it couldn't find the file name because it was dynamic. I inserted a custom value into the Table field of Table1 and am hoping that will work.

 

I know the columns; they are the same every time. I just do not know the quantity of rows.

@AlexEncodian 's idea of selecting the first 5000 rows and skipping the empty records may be best. I am seeing anywhere from 5 - 3000 results coming through currently. I'm guessing Encodian's features are not free/included so not my first choice of solution in this case.