How to Combine Multiple Excel Files with Power Automate

[@EssentialExcel]

I hope you enjoyed this tutorial 😃 If you did please give it a LIKE... and don’t forget to SUBSCRIBE for even more content! For More 👉 kzbin.info

[@cswann8]

Liked and subbed. This happened to be exactly what I need for a project my boss has me doing. Power Automate is a legit Superpower.

[@MaryAnn-yi1pw]

Awesome video. I was able to accomplish my objective. Thanks.

[@QuynhNguyen-op6tx]

This video is great! Is there a video for combining Excel files in OneDrive with an Instant Cloud?

[@TheBonelessGame]

Bro, you are the best! Thanks

[@EssentialExcel]

Thanks Sebastian! Glad it was helpful 😃

[@karlahillam]

Hi There, Your Power automate screen looks completely different to mine. How do I get to the ACTIONS side bar? Would some help getting to the start screen. Please and thankyou

[@mdz8888]

Hi, who is this video intended for? Is it beginner or intermediate or expert? I got very confused trying to follow the logic. Could you do another with the interface that is easier to follow?

[@EssentialExcel]

Hi Mduduzi, sorry to hear you found this video hard to follow. You could say this is more intermediate level but is intended for all levels being able to follow step by step and replicate for their own needs. This scenario required us to use the desktop version of Power Automate, if you are referring to the cloud version as being the easier interface?

[@mohabassam]

Hi, Thank you man it was very helpful I tried it on my files which have two sheets in each workbook each sheet have diffirent table and the template file have the same structure as the separated files when running the flow it only gets the second sheet data and combined them in the result output file first sheet was skipped. is there a fast solution for this? to go through all sheets one by one moving all rows to the right output sheet in the template file.

[@TwiztidWicked]

Great tutorial! I have a question and maybe its not possible as I cannot find this without using software, but is there any way to have each of the files you are combining to become their own worksheet in the workbook? I have logs that get created daily as separate CSV's and I don't want them all one on worksheet when combine, I need them to be tabulated at the bottom. Any help would be greatly appreciated.

[@KevinMiller-g3q]

Isn't there an option to say there is a header in each excel doc or start the copying on row 2? It is duplicating the header for each excel doc merged. I did use the header template. Do you only use the header template when there are no headers in those getting imported?

[@KevinMiller-g3q]

Actually, I think I found it. Under "Read from Excel worksheet" under Advanced, there is a slider to say that the "First line of range contains column names.

[@KevinMiller-g3q]

Which didn't work. So be it. I can easily delete the copied headers. I'm sure someone more familiar with this will save me.

[@jithin1868]

Hello, How to write the next excel file data in to the another column?

[@Dermawanas]

Is it possible to get image from cell to be merged into one file?

[@davidcampling1686]

Thanks for sharing. A great use case and one that I am needing to implement. Dates are part of the import. However, they appear in different formats 12/01/2023 if perfect but I am getting some 12/01/2023 12:00:00 AM. I have made sure the template date fields are formatted correctly and the original ( Source ) files are formatted correctly. Despite this it continues to do this action. I have to double click into each cell and hit return for it to change to the correct format. Is there an easy fix ?

[@davidcampling1686]

Also this flow is pulling in dates and completely changing the format. 10th May 2023 is in the original as 10/05/2023 and in the new file it is 05/10/2023. Not only that but in the original field with two entries it has changed only one of the dates in this way. This means this is unusable as we are introducing random errors into the data. I have checked the date formatting in the input file and the header template used . they are both correct for Aus date. I have no idea what to do from here but cannot use this

[@Siiggyy]

@@davidcampling1686 Same for us have you found any solution or other method?

[@davidcampling1686]

@@Siiggyy I found this, and it worked kzbin.info/www/bejne/q5u7ZGewhKt-gLssi=7Jpp4Mma9jAUDXqQ

[@csaa2540]

I have the exact same issue. Please let me know if you found a solution to fix this.

[@mdnelson85]

I am wanting to merge files where the first 6 rows have headings, is there a way to compensate for that? I saw where at the end where you were able to toggle that the first row has headings, but can it go more advanced?

[@jasoncox1955]

If the files explored in initial step have multiple sheets does it only take data from the first sheet? If so, How do you make it retrieve from more than one sheet?

[@LearnLearn-o6v]

can we do it from Power automate online version ?

[@anandraichura3796]

Dear, Thanks a lot for amazing tutorial video. I have used Power Automate for first time and was able to create merge file following steps mentioned here. However, even though last step is save as but template file is getting saved with data of merging.

[@anandraichura3796]

Resolved. issues was, file was getting auto save in one drive folder.

[@EssentialExcel]

Hi Anand, apologies for delay getting to your question! Glad to see you have resolved and you make a great point highlighting the potential issue caused by auto save!

[@hindmusallam]

Can I do the same task with word file to excel sheet instead of excel to excel?

[@cathylammertijn6021]

Hi, thanks for the video, works perfectly except ... my Excel file contains several time fields, while running the flow fields are converted to a decimal number. Can you prevent this? Can you also dynamically determine the filename you are creating? With for example the current date in the name?

[@siriuspotter4743]

Hi, this flow is working fine, but I am not able to open the excel which I have consolidated all the files to, once the flow is done. Any idea why this is happening ? Thanks!

[@d0zxe]

Hi, this is amazing stuff, however my question is what if one of the files you are trying to combine is dynamic; so in my case, I have two files I would like to combine. They have same properties etc etc. One of the files is generated via an API using power automate, however it only creates 2 months worth of data when the flow runs once every month. My second file is a master file that contains historical data. at the moment, I do this manually once every month where the new data generated from the API flow to the master file. Is there a way to automate the process of appending the API generated file to the master file, master file should only have new API generated data added to it. Any help would be appreciated, thanks.

[@daniellebezuidenhout8156]

Ive done all the steps but i need a certain range to be copied from the files but nothing gets copied to the new file , how can i rename new file to have a new name based on the flow run date so each new run has its own excel sheet created ,

[@Mavryn]

Hello, I seem to be having the problem where it saves as a new file and overwrites the template header so if I run it twice the output is doubled. How do I stop this?

[@Mavryn]

Figured out the issue! One thing that wasn't mentioned in the tutorial is the header file cannot have Auto Save turned on as it will save the output to both the header file and combined output file. Great Tutorial though!

[@RobertStipic-pw7nr]

Thank you for a great video! Is it possible to combine cell colors also?

[@Simon-vc1wk]

Great process. Is there a way to add a step to add meta data such as file path, filename, for each file stacked and check for duplicate records?

[@EssentialExcel]

Hello. Yes these could be captured with additional steps.

[@aimeelister9028]

Mine has worked fine, but has only taken x1 workbook from each file, is there a way to read all 3 tabs in each workbook?

[@imamuddin6891]

It also works for copying/merging csv files into an excel file.

[@EssentialExcel]

🙂👍

[@rogeriopenna9014]

Some of the Excel files I want to merge have empty rows in them (they are generated elsewhere, which is exactly why I need to merge them) Will that result in `the GET FIRST FREE ROW ON COLUMN instruction finding rows in the middle middle of the filled rows? Example: 1 - finds first free row (row 2), copy contents of file A and pastes on Output file. 2 - file A however, 5th row was empty and then had 100 filled rows. 3 - iterates to 2nd file 4 - finds first free row on Output file. It should be row 106 (file A's 4 filled rows, 1 empty row, 100 filled rows), 5 - pastes content of 2nd file starting at 6th row instead of 106th row! ??

[@anuradhachaturvedi3731]

Hi Can you share steps if the files are in different folder how we will merge them

[@anuradhachaturvedi3731]

By different folder i mean , my files are in different folders on sharepoint. Your early response would be highly appreciated

[@ITSmeCHEESE]

Hi, great video! Quick question - I have a main file with all the data in it and this then feeds out to multiple files. Is it possible, using power automate, to ask the computer to open and read the main file when any of the "multiple files" are opened? Thanks

[@EssentialExcel]

Hello. Yes this scenario would be fine as power automate will store your main file as a set instance so that it can identify the file when working with multiple files. Hope that helps?

[@CarFinanceSimplified]

Hi. Are you able to explain how one can use the LIST ROWS PRESENT IN A TABLE flow TWICE, i.e. for two different tables and then get both sets of fields to then add to a new dataverse table using ADD A ROW inside an APPLY TO EACH function? I need to create a new table using rows from two separate Excel tables. Many thanks.

[@PK-nt8ld]

Hi, How to set run this flow when file is modified. (on OneDrive)

[@EssentialExcel]

Hello and thanks for your question! It is very similar to this video however you will be able to find a trigger action for when a file is modified on OneDrive… although be aware this are separate triggers depending on whether you are using a personal or business OneDrive account.

[@d.Noriko]

I got a Warning in step 6 (write in a excel) "Deferred type provided" How can I fix that?

[@brendaloo4589]

i am facing the same issue as well for a different flow where the steps are all the same. No idea whats happening.

[@modr2944]

Why I can't find my onedrive in getfile

[@shubhabratadey]

The only issue is in Power Query, you don't need the Columns in the same order but here we would need them to be in a specific order

[@Simon-vc1wk]

What advantage is there to doing this in power query?

[@EssentialExcel]

Hi Simon, apologies for not seeing your comment earlier! You certainly could do this in power query 🙂

[@OMAKEM]

Interesting but power query is much more easy to get the same result

[@EssentialExcel]

It certainly is 🙂