Power Automate Desktop - Merging/Appending 1,400 Excel Files

[pawlowski6132]

I'm sure I'm not the first person to try this (maybe the dumbest??) so, perhaps someone can lend some guidance. I have many Excel workbooks in a local folder I need to merge into one Excel workbook. I've been struggling to complete this using Power Automate Desktop.

Has anyone done this? Can they provide some advice or examples?

Thanx in advance

Joe

[mattias1172]

So long as your data is consistent or between columns on each spreadsheet, this should be relatively easy. 

I'm only going to do a small "proof of concept" so there's not telling what kind of curve ball 1,400 files can have. 

Basically, we want to get a list of all the files (with their fill paths), iterate through each one, copy the data, and paste to a new "appended" worksheet. If not already, make sure all the files are in the same directory to make this easier. 

This initial setup is pretty simple. "Get Files in folder" to get a list of all the file paths.

"Launch Excel" with blank document to append all the data to. 

A "For Each" loop iterating over the variable "Files" we created in the first step. This will give us the individiaul file paths for iteration. 

Inside the loop, our first step is to get the first free row of the new document we are creating. This is to make sure we don't overwrite any data. 

Next a second launch excel now opening the current existing file we want the data from.

We then want to read the data inside. This action has an additional option we can select if your files have headers. If so, check off the "First line...." slider under "Advanced"

Next we write the data to our new appended file. We start on the A column and for row, grab our variable from the "Get first free row" action.

Last, we close the current existing excel file. 

After the loop, add a second close excel with the option to save the document with a path and name. 

Here is the data in the spreadsheets:

And the final result:

Full Flow:

[MichaelAnnis]

Personal Opinion:  PAD will take forever to do this by itself.  I have attached a Macro Workbook that was created specifically for this purpose.  It assumes that all columns in each workbook are fixed and that each workbook you have has a title row (so data starts at row 2).  In short, the macro reads the list of files in Column A&B (you can use PAD to write them - see below) and combines them all to a single file specified in E2 and F2.

So, start by opening the file and making sure macros are enabled.  Update your output path in E2 and you output filename in F2, save and close this file.

Then, all PAD needs to do is:

Good luck!

[Alphadrag]

Hi, may i ask where did you get the "Current_Excel_File" from?

[mattias1172]

Current_Excel_File is the Excel instance opened within the For Each loop. It's created by the "Launch Excel" action inside the loop. 

[UK_Mike]

I can only presume its 1 sheet per workbook.

Power Query will easily combine all these books plus anytime a new workbook gets dropped into the folder its just a matter of refreshing the query, will do it in milliseconds 

Me personally keep PAD out of the equation or just use it to refresh the query.

[pawlowski6132]

Thanx much for all the time you took to answer this. I'm going to give this a whirl and see if it candle my workload.

[pawlowski6132]

Hi. This might work too. I'm going to work on this today using this Macro. I'll let you know how it worked out.

[pawlowski6132]

Good question. So, the Excel Workbooks are output from running an SAP Transaction/report and reside on a shared file server on our corp network.

[pawlowski6132]

Actually, it's three sheets per workbook but, I only need one. 

[mattias1172]

If you do end up using PAD for this, i would recommend disabling the option to visibly open the workbook in the "Launch Excel" action. This will prevent you from seeing what PAD is doing, but will make it process a little faster. Since were talking in the 1,000+ range, this may help. 

[UK_Mike]

Doesn't matter, just combine the sheets required.

Whether its a one off or you're going to be adding more workbooks down the line I highly recommend power query.

I see MichaelAnnis gave you a Macro specifically for this purpose so you're spoiled for choice.

[pawlowski6132]

Ok, so, thought I'd provide an update.

Power Automate or Power Query in Excel didn't seem to be the best solution for this problem.

We ended up building a pipeline in Azure Datafactory to move the files from blob storage to a SQL Table.

However, what we discovered was that what appeared to be normal Excel Worbooks were instead 'Strict Open XML Spreadsheets'. Stupidest thing I've ever seen. 

Now, we are looking to Power automate to take all these stupid workbooks, and convert them all (almost 2k now) to Microsoft Excel file types.

I can configure PAD to do for one file but, am now researching how to Loop or iterate through each file in a local folder to do this.

[UK_Mike]

Re Power Query, I have a query that iterates through several hundred workbooks and pulls whatever I want into a single Excel file PLUSSSSSSS anytime a new Excel file gets dropped in the folder its just a matter of refreshing the query. 

Can you elaborate why Power Query didnt work ?

Was it because of these 'Strict Open XML Spreadsheets'

Well if you can do it for 1 file you can put in a loop / for each to do millions.