cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tylerwasson123
Frequent Visitor

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

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.

 

tylerwasson123_0-1639073541056.png

 

 

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

 

tylerwasson123_1-1639073541057.png

 

 

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

 

tylerwasson123_2-1639073541058.png

 

 

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. 

 

tylerwasson123_3-1639073541058.png

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-yujincui-msft
Community Support
Community Support

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()

vyujincuimsft_0-1639547391596.png

2.This is the master data excel file.

vyujincuimsft_1-1639547391598.png

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')

 

vyujincuimsft_2-1639547469671.png

 

vyujincuimsft_3-1639547469674.png

 

vyujincuimsft_4-1639547469682.png

Result Screenshots:

TestFile.xlsx

vyujincuimsft_5-1639547558272.png

Master-data.xlsx

vyujincuimsft_6-1639547565790.png

 

Best Regards,

Charlie Choi

View solution in original post

6 REPLIES 6
v-yujincui-msft
Community Support
Community Support

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()

vyujincuimsft_0-1639547391596.png

2.This is the master data excel file.

vyujincuimsft_1-1639547391598.png

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')

 

vyujincuimsft_2-1639547469671.png

 

vyujincuimsft_3-1639547469674.png

 

vyujincuimsft_4-1639547469682.png

Result Screenshots:

TestFile.xlsx

vyujincuimsft_5-1639547558272.png

Master-data.xlsx

vyujincuimsft_6-1639547565790.png

 

Best Regards,

Charlie Choi

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.

 

 

tylerwasson123_0-1639774386387.png

tylerwasson123_1-1639774386388.png

 

 

tylerwasson123_2-1639774386389.png

 

 

tylerwasson123_3-1639774386390.png

 

Hi @tylerwasson123,

 

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

vyujincuimsft_0-1640239751299.png

 

Best Regards,

Charlie Choi

Hello 

 

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.

 

tylerwasson123_0-1640280385200.png

 

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.

 

tylerwasson123_1-1640280385201.png

Below is my final product using 2 separate flows:

 

Flow 1:

 

tylerwasson123_2-1640280385202.png

 

Flow 2:

 

tylerwasson123_3-1640280385204.png

 

tylerwasson123_4-1640280385203.png

 

Thank-you again!

 

l4u124
New Member

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.

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?

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Users online (2,904)