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

4 REPLIES 4
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!

 

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Power Automate Designer Feedback_carousel.jpg

Help make Flow Design easier

Are you new to designing flows? What is your biggest struggle with Power Automate Designer? Help us make it more user friendly!

Top Solution Authors
Top Kudoed Authors
Users online (3,038)