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

Append data from saved excel into other pre-existing excel file

Hi everyone - I'm very new to Power Automate so hopefully this isn't a silly question.  I have a set of 4 emails that get sent to my inbox every day, each with an Excel file attachment.  I have currently implemented a flow that saves each of these Excel files to my OneDrive folder.  From this point, I'd like to take the data in each of the 4 Excel files and append that data into the 4 corresponding sheets of another, pre-existing Excel file (columns are the same between the emailed Excel and pre-existing Excel).  Is this possible?  (Currently I do this as a completely manual process, i.e. I open one Excel file from my email, then copy/paste the rows from that file into one of the four tabs that corresponds to the data in my pre-existing Excel file).

 

Thanks!

Alison

1 ACCEPTED SOLUTION

Accepted Solutions
v-yiwenxie-msft
Microsoft
Microsoft

Hi @alisonmeininger 

 

You need 2 flows to meet your need.

The first flow is ,as you implemented, to save each of the emailed Excel files to your OneDrive folder.

 

The second flow is to append emailed Excel data into the 4 corresponding sheets of another pre-existing Excel file.

 

And to match the emailed Excel data with the columns in another pre-existing Excel file, there is an easier way to check whether they are matched---just ensure the table names are the same.

You can edit table name by selecting the table, choosing ‘Table Design’ and then editing the table name:

1.21-8.PNG

I’ve create the second flow as below:

 

First, if a file is created in a specific folder in OneDrive, get file metadata and check if the file is an excel file.

If yes, get tables in the excel file and add an ‘apply to each’ action.

If no, do nothing.

1.21-1.PNG

 

For the ‘apply to each’ action:

1.21-2.PNG

 

Add a ‘switch’ action:

The switch action is used to find matched case. And if there is a matched case, then append the emailed excel data into pre-existing excel file.

There are four cases and an default one.

1.21-3.PNG

 

For each case in ‘switch’ action:

1.21-4.png1.21-5.PNG1.21-6.PNG1.21-7.PNG

 

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

View solution in original post

4 REPLIES 4
v-yiwenxie-msft
Microsoft
Microsoft

Hi @alisonmeininger 

 

You need 2 flows to meet your need.

The first flow is ,as you implemented, to save each of the emailed Excel files to your OneDrive folder.

 

The second flow is to append emailed Excel data into the 4 corresponding sheets of another pre-existing Excel file.

 

And to match the emailed Excel data with the columns in another pre-existing Excel file, there is an easier way to check whether they are matched---just ensure the table names are the same.

You can edit table name by selecting the table, choosing ‘Table Design’ and then editing the table name:

1.21-8.PNG

I’ve create the second flow as below:

 

First, if a file is created in a specific folder in OneDrive, get file metadata and check if the file is an excel file.

If yes, get tables in the excel file and add an ‘apply to each’ action.

If no, do nothing.

1.21-1.PNG

 

For the ‘apply to each’ action:

1.21-2.PNG

 

Add a ‘switch’ action:

The switch action is used to find matched case. And if there is a matched case, then append the emailed excel data into pre-existing excel file.

There are four cases and an default one.

1.21-3.PNG

 

For each case in ‘switch’ action:

1.21-4.png1.21-5.PNG1.21-6.PNG1.21-7.PNG

 

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

Hi,

Is there any other way to merge data or add data from one workbook to another? Because in the case of huge amount of data, this method takes hours to be completed.

Thank you so much for the very detailed response!  I will try out this solution and let you know if it works.  Thanks again!

vaampeere
Regular Visitor

Hi,

What if the exported file has no table format. it is simply an excel sheet that needs to be appended?

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,646)