Copy Excel file data to different Excel file in SharePoint Document Library (dynamic)

[tylerwasson123]

Every day an excel file is emailed to me, and I save the email to a SharePoint document library manually, copy it's contents, and append the data to a master data excel file in a different document library. 

The excel file that is emailed to me does not come in a table, has the same column count for each file but dynamic rows.

I add a date column to the file and manually populate today's date for each row.

The master data file is in a table with all the same columns to include the date column.

If I do the steps individually in Power Automate, as a test, everything works like a charm as shown:  

Creating the table which is always Test1, using a fixed amount of rows that I know is well over the count I would receive in my email.  Adding the Date column to the table.

Filtering the table to only rows that have data and adding today's date.

Copying/appending the rows with data to the master data file.

But when I try to add all pieces together into one I cannot even get by List Rows Present when trying to filter the rows. 

[v-yujincui-msft · Accepted solution]

Hi@tylerwasson123,

Do you want to append the data in the excel file you uploaded to another excel file?

Maybe your issue is caused by the delay. After the Create table Action is completed, the List rows present in a table Action maybe does not get the data immediately. I suggest you could try to add a Delay Action.

I have made a test for your reference.

1.This is the excel file I will upload to a SharePoint document library()

2.This is the master data excel file.

3.Create a flow.

items('Apply_to_each')?['Item']
items('Apply_to_each')?['Item Description']
items('Apply_to_each')?['Qty']
items('Apply_to_each')?['Status']
formatDateTime(utcNow(),'yyyy/MM/dd')

Result Screenshots:

TestFile.xlsx

Master-data.xlsx

Best Regards,

Charlie Choi

[tylerwasson123]

Hello v-yujincui-msft,

Thank you for your reply and help.  Yes, I am interested in appending the data in the master data file table.

I added a few parts to this request prior to creating the table which all works fine.  The name of the attachment is dynamically changed for each email attachment based on a date and time stamp and the file is properly added to the SP site.

I now receive an error when trying to "Create Table" stating the item cannot be found.  I do not have the option to choose x-ms-file-id from the dynamic content as in your example.  I appreciate your or anyone's help in the matter, as after this step I'm looking to filter the table array to only non-null rows and appending those rows to the master data file.

PREVIEW

[v-yujincui-msft]

Hi @tylerwasson123,

You can change the ItemId in Create table Action to Id, and then try again。

Best Regards,

Charlie Choi

[tylerwasson123]

Hello v-yujincui-msft,

I went back to the drawing board after your first post and based on your post was able to get it work.  I'm very appreciative of your time to do that, thank-you very much.

Using x-ms-file-id was a major fix, because once I made that change things started to fall into place.

The last major change was your recommendation below because before that I struggled with populating the table.  I was also able to populate the Date column with the days date at the same time eliminating a step.

Below is my final product using 2 separate flows:

Flow 1:

Flow 2:

Thank-you again!

[l4u124]

Hi - I'm attempting tp try this and I keep getting a table cannot overlap another table error. Any ideas? I've tried looking this up elsewhere but nothing seems to give me resolution for this error in relation to this solution.

[tylerwasson123]

Hello I4u124,

Does the file you are copying the table to have more than 1 table?  Can you provide more information on the example you are trying and maybe a few screen shots?

[grantw76]

sorry to hijack an old thread but what step do i need to do next to add rows into another excel. I tried Apply to All, Add Rows but got some sort of For Each error 😞 

[tylerwasson123]

Hello grantw76,

From your photo it looks like you are doing the right steps.  After you enter the table, the columns in the table should be listed where you would add the Items formula.  I did blog post and YouTube video on it, have a look and if you have anymore questions, feel free to reach out.

Copy Excel Content (Dynamic) from one Excel file to Another using Power Automate-Series 2 of 4 – TW-...