Merge Files with same headers but different layout

[kv-defy]

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. 

[JosephSerpis · Accepted solution]

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.

[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 · Accepted solution]

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).