Merge CSV files

Topic

New Reply

Hi, I have a number of CSV (comma delimited) files containing various test data. I would like to combine them into one CSV file, but the difficulty that I am facing is that I don’t want to append the files in rows, but rather append to the next available column. All files have a common column named Time in the first column. Each file contains different number of columns with test data, but all have the same number of rows. For example:

File 1:

Time, Temp1, Temp2

File 2:

Time, Temp3, Temp4, Temp5, Temp6

File 3:

Time, Temp7

The combined file should have all data organized in the following manner:

Time, Temp1,Temp2,Temp3,Temp4,Temp5,Temp6,Temp7

______

I have tried using Power Query but failed to reach my objective. Can anyone guide me on the process to achieve this using either Power Query or any other method?

Thanks and kind regards,

Hanan

 

Reply | Quote

Replies

One way to do it would be to open the CSV files Excel or in LibreOffice. Then you can copy the columns of each subsequent CSV file to the desired columns of the master CSV file. It would be a good idea to save your work as you process each CSV file.

Reply | Quote

Up to now I have used this method of manually opening the CSV files and copying it to a master file. However, I usually receive 30+ CSV files every month and I have repeat this task and then check that no error has been made, which can become quite tedious. Also, each file contains thousands of rows and I don’t want to make the mistake of missing or skipping any data. I therefore prefer to automate the process.

Did anyone come across this in their past or current work? Any macro or program out there that I can use?

Thanks again.

Reply | Quote

Hi Hanan

The irresistible beauty of Excel is you can pretty much automate such tasks with a little bit of vba code.

Your initial posting was a reasonable start at explaining what you want, but we need a little clarification before offering a solution.

For example, regarding your ‘common’ Time column – is it just the heading that is ‘common’ or do ALL of your csv files have the same number and identical time stamps in this column. If the csv files contain different timings in this first column, I presume you want all all the imported columns from all files to be ‘properly aligned’ with the time entry in the first column.

It might be easier if you posted a few sample csv files just as examples.

I presume you want an Excel tool that could process all the csv files in a specified folder?

zeddy

 

Reply | Quote

Hi Hanan

And does it matter in which specific order the csv files are processed?

This will affect how the columns will finally appear after processing has finished i.e. the actual columns sequence.

For example, are the csv files processed alphabetically by filename? by filedate?

And when do you want to process all the csv files? monthly? daily? weekly?

zeddy

 

Reply | Quote

You might be best served by using a true database program, especially if you keep the data long-term. You can load each CSV file into its own table and link the data based on the time/date. Export the data to a file, or use your favorite report writer software to present the data however you need.

Reply | Quote