Flow to append a table into a excel file in sharepoint

[DhineshKumar]

Currently, I'm have created a flow that can upload the file to SharePoint. The challenge is that I have an excel file, say a.xlsx present in the SharePoint and I have a b.xlsx in my local repository with the same columns but totally different data. When I upload the b.xlsx through Powerapps it should append the b.xlsx into a.xlsx.

Is this possible?

can some help me from scratch?

[v-litu-msft]

Hi @DhineshKumar,

Yes, it is possible, you could create a flow to monitor a SharePoint folder. When b.xlsx file is created in the folder, Get its tables + Lists rows action to get all rows. Then use Apply to each action + Add a row into a table to append these rows into a.xlsx file.

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DhineshKumar]

The flow executes successfully but I don't see any rows add in the a.xlsx file. Did I miss something? I have done the exact representation of yours.

for example, I have a production data in monthly order. The file existing in the Sharepoint folder is jan_production.xlsx and when I add the file (i. e) feb_production.xlsx in SharePoint, the table in the feb_production.xlsx must be appended into the jan_production.xlsx.

[DhineshKumar]

In the action of adding a row how to get the values of each row. Do I have to use any expression to get the value? if so what is the expression used?. I think is happening due to the first trigger action of getting the properties only

[v-litu-msft · Accepted solution]

Hi @DhineshKumar,

After get rows, create an Apply to each action to loop through them, then add Add a row into a table action to add them into the master excel file. 

The expression should be item()?['columnName'], it means get current column value:

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[webbrewer]

The answers above require that a table exists in the excel file, which is often not the case especially with files exported from 3rd party software. If your file has no tables, you can create a table following this guide:

http://webbrewers.com/microsoft-flow-append-excel-data-to-a-sharepoint-list/

[Anonymous]

Hi, 

This works fine for me, but it takes quite a long time (I guess power automate adds row by row). Is there a way of doing something similar, but just append directly a whole table? 

[takolota]

@Anonymous 

If the data you want to append to the table is less than 16,000 rows, then this template has a batch create action that can do it in a minute.

https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Excel-Batch-Create-Update-and-Upsert/td-p/1624706