Merge Excel files

[Terkelsen]

Does anyone have a script that would merge all Excel files in a folder into just one Excel file?

[laurentd]

Not a script, but an app:
You can do this with Google Sheets Connect

[rhd_ole]

Are you looking to merge them into one sheet (same headers) or each doc on a new sheet within the workbook?

I have a python script that will combine them by putting each other their own sheet within the workbook.

[JimmyHartington]

And if a more low-level method could be to convert the Excel-files to CSV.
And then use Miller (https://miller.readthedocs.io/en/6.12.0/) to merge the CSVs.

I use this method in some flows.

Miller is run with the Run Command app.

Code: Select all

cd "%%InputFilePath%%" & "C:\ProgramData\chocolatey\lib\miller\tools\mlr.exe" --csv unsparsify [Job.PrivateData:Key="ListOfFiles"] > "%%OutputPath%%\[Job.NameProper].csv"

[magnussandstrom]

And if a more low-level method could be to convert the Excel-files to CSV.
And then use Miller (https://miller.readthedocs.io/en/6.12.0/) to merge the CSVs.

I use this method in some flows.

Miller is run with the Run Command app.

Code: Select all

cd "%%InputFilePath%%" & "C:\ProgramData\chocolatey\lib\miller\tools\mlr.exe" --csv unsparsify [Job.PrivateData:Key="ListOfFiles"] > "%%OutputPath%%\[Job.NameProper].csv"

Hi Jimmy, I looking for a way to merge multiple CSV-files in Switch, would you like to share an example flow?

[tdeschampsBluewest]

Hi there,

You might want to take a look at the File Fusion app: https://www0.enfocus.com/en/appstore/pr ... ile-fusion. While it's not designed exclusively for CSVs, it could potentially be used to merge them—provided the files have no headers and the columns are aligned correctly.

Alternatively, if you're working with the CSV Pickup app, you can extract rows from multiple CSVs and loop through them to append them to a master file using CSV Creator. This method can be a bit resource-intensive for a relatively simple task, but it does get the job done.

If you're already using the CSV Creator app and merging CSVs is something you frequently need, feel free to reach out! I'd be happy to explore the possibility of adding this functionality as a dedicated feature.

[JimmyHartington]

And if a more low-level method could be to convert the Excel-files to CSV.
And then use Miller (https://miller.readthedocs.io/en/6.12.0/) to merge the CSVs.

I use this method in some flows.

Miller is run with the Run Command app.

Code: Select all

cd "%%InputFilePath%%" & "C:\ProgramData\chocolatey\lib\miller\tools\mlr.exe" --csv unsparsify [Job.PrivateData:Key="ListOfFiles"] > "%%OutputPath%%\[Job.NameProper].csv"

Hi Jimmy, I looking for a way to merge multiple CSV-files in Switch, would you like to share an example flow?

I will be happy to share an example flow.
Just need to adapt one of my flows.

The strength for my use case is that Miller stacks the data.
So if one file has header A, B and C.
The other file has A, C, D and E.
Then the output will have A, B, C, D and E as headers and the cells are populated correctly.

[magnussandstrom]

I will be happy to share an example flow.
Just need to adapt one of my flows.

The strength for my use case is that Miller stacks the data.
So if one file has header A, B and C.
The other file has A, C, D and E.
Then the output will have A, B, C, D and E as headers and the cells are populated correctly.

Sounds great! I have installed Miller 6.13 now and setup Run Command as your example, just need to see the rest of the flow.

[JimmyHartington]

Here is an example flow using Miller to stack/merge csv files.
There is also some examples csv-files attached.

The input to Milles is a folder containing the csv-files. In my real flow I have an Assemble Job before to collect the files which needs to be stacked. In this example the input in the flow needs to be a folder.

With these 3 csv files as input:

Code: Select all

A,B,C
1,2,3
4,5,6
7,8,9

Code: Select all

A,B,D,E
1,2,4,5
6,7,9,10
11,12,14,15

Code: Select all

B,D,E
2,4,5
7,9,10
12,14,15

The output is:

Code: Select all

A,B,C,D,E
1,2,3,,
4,5,6,,
7,8,9,,
1,2,,4,5
6,7,,9,10
11,12,,14,15
,2,,4,5
,7,,9,10
,12,,14,15

Remember to change the path to the Miller-executable.

forum-Merge Excel files.zip

(8.11 KiB) Downloaded 103 times

[magnussandstrom]

Works great for the demo-files, thanks Jimmy!

[May]

I am kind of surprised that no one mentioned Easy Data Transform:

https://www.easydatatransform.com/

Unbelievably mighty tool. That one time fee is a joke for what it can do.

Miller is also good, yes. But EDT has the advantage that you can "click" your workflow in a GUI and you can see what the tool does to your XLSX / CSV whatever data.

One use case of EDT for example in my environment is that I export each line of a multi line XLSX as one dataset and on its way out I convert ist to JSON which makes it possible to access all variables (with their former column names) directly in Enfocus Switch.

[JimmyHartington]

I agree Easy Data Transform is an excellent program.
But I have not found a way to get it to stack/merge a folder with an arbitrary amount of files.

In other flows I use the cli method of driving it to do data transformation and conversion.