Unanswered

Merge two Excel files received in an email

(0) Share

Report

Posted on by zacharie_touama

Hello,
Here is my problem, I can't find out how from Power Automate.
- When I receive an email
- Merge the two Excel files received
- Send them to another recipient

For the reception and sending of mail, no problem,
However, I can't merge the files, because it treats the problem in "apply to each" mode and so I receive two emails with two Excel files, instead of one email with the merged Excel files.

How to solve this problem? Thank you in advance for your help.

Categories:

General topics

I have the same question (0)

All responses (5)

Answers (0)

David- 1,254 on at

Like (0)

Report
Copy link

Link copied!
I don't have time to build a flow to show an example of this, but all of the actions exist to do this. You would use these actions:
I assume your trigger is When a new mail arrives
Follow this with an Apply to each that has output of the Attachments
Use the Create file action to save the two Excel files somewhere
Then use List rows present in a table to get the data from one of the spreadsheets
Then use another Apply to each with the data from getting the rows above
Next use Add a row into a table of your second spreadsheet
The two are now combined and you can send the second spreadsheet via e-mail
You will need to do some checks along with way. You'll probably need to check that both attachments are Excel files, and you will probably need to check to make sure there are only two Excel files attached. It will look something like this (none of these actions are configured and I have not tested this):

Was this reply helpful? Yes No
zacharie_touama 4 on at

Like (0)

Report
Copy link

Link copied!

Thank you for your reply, but how can I find out the name of the table in the Excel file I received by email?

Was this reply helpful? Yes No
David- 1,254 on at

Like (0)

Report
Copy link

Link copied!

I hadn't thought about that, as that will complicate things. You could check to see what the tables are named in your existing files. If there is only one table and nobody has renamed them, the table should be named Table1, which you can then enter as a custom value in the Excel actions. Otherwise, there is a Get tables action you can use to get them from each spreadsheet.

That will greatly complicate things if there are more than one table in each, because then you will need to do an apply to each for each table and then also do checks to make sure the table exists in both spreadsheets as well.

Hopefully someone sees your post and has actually created and tested a flow with your use case.

Was this reply helpful? Yes No
zacharie_touama 4 on at

Like (0)

Report
Copy link

Link copied!

Thanks @David- , I have another problem, i can't select the ID of the sharepoint file previously created (on apply to each section) , it doesn't appear on dynamic content on "list raw to table" section

Was this reply helpful? Yes No
David- 1,254 on at

Like (0)

Report
Copy link

Link copied!

As I mentioned, this is not something I have done before, but what I provided was a suggestion. However, I put something together that may work but I have not tested as so far my flow is not getting triggered when the e-mail arrives with the conditions I set.

But I put two files in the location where you are going to create the files from the attachments with predefined names and set them up with a table that contains data for your process. You could name the files mergefile1.xlsx and mergefile2.xlsx.

The in your flow initialize a number variable named AttachmentCount with the default value of 1. When you create the file, hard code the filename and use the AttachmentCount variable to set the file number: mergefile[AttachmentCount].xlsx.

Then in your get file set it up to use the existing file in your document library and in the add rows do the same thing. Your flow will look like this:
The one thing I don't know what will happen since I have not done something like this will be how the create file action reacts to the existing files. I am hoping that they'll overwrite them and not create a new file with a number attached to the end, as there are no options to tell the flow what to do if the file exists already.

Was this reply helpful? Yes No