Merge Files with same headers but different layout

Hi, I have more than 50 CSV files that have the same number of columns but the columns are scattered around differently in each file. I would like to merge them together with the correct data in the correct column.

Here is an example of my CSV file:

CSV File 1:

Category	A1	A2	A3

CSV File 2:

Category	A2	A3	A1

I would like to union and merge the data based on the data in the column A1, A2 and A3 respectively despite the column being scattered around.

Input

Preparation

Accepted answers

JosephSerpis

Hi @kv-defy try the third option in this article. The batch macro option has the option to Auto configure by name which would union the columns based on names irrspective on where they are scattered in the files.

JosephSerpis

Hi @kv-defy as your file is a CSV you won't need to include Sheet name in the filepath that only needed for excel files. Then inside the batch macro I would change the Input file to one of your CSV file. Then lastly I would check the action tool is updating the filepath (It should have the filepath highlighted in the action tool which matches the filepath of your Input Data Tool).

All comments

JosephSerpis

KrishnaChithrathil

@kv-defy

INPUT

OUTPUT

Is this what you're looking for?

If yes, in the UNION tool, there's an option 'Auto Config by Name'. It'll merge the data irrespective of the column order, with respect to the Header name.

If it's not what ya looking for, kindly provide us a sample output. 🙂

kv-defy

Hi @KrishnaChithrathil, Union works well but I am working with more than 50 huge files. Was thinking if there is any other better way to do it.

kv-defy

@JosephSerpis Thanks for the sharing! I have looked at the forum post and replicate it. I am not able to run it. Was wondering if there is anything that I did wrongly.

This is the workflow. I am working with csv files. The path for csv and the sheet is based of the file name.

Here is how the files look like in the directory input:

In the batch correct file provided in the forum post:

For the placeholder file, what files should I put? Initially I put in 1 file that is existing in the directory. But the output for the workflow comes up as duplicated entries for the number of files I have.

Kindly advice and my files are very huge so I want to look for a quicker way to merge since all the columns are scattered.

JosephSerpis

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703