Simple CSV Combiner
Combining CSV files is a fairly straight forward task, but the most common way of combining them using a simple copy *.csv combined.csv command line still requires some manual deduplication. Recently, I came across the need to combine a large number of files and I decided that I didn’t want to have to go through the process of deduplication, if I could avoid it. My particular approach to the problem can be found here: Simple CSV Combiner. This file contains a batch file that combines all CSVs in the same directory into a single CSV, copies the component CSVs into a backup directory, and then transforms the CSV into an Excel document with a single click. All of the CSVs must have the same fields and must have field names in the first row, but the number of records per CSV and number of CSVs can vary without impacting the script. The main magic is in this part of the script:
for %%f in (*.csv) do ( xcopy /a /y %%f JustImported ren %%f CombinedCSVOutput.tmp ) for %%f in (*.csv) do ( for /F "usebackq skip=1 delims=" %%a in ("%%f") do ( echo %%a>> CombinedCSVOutput.tmp ) ) ren CombinedCSVOutput.tmp CombinedCSVOutput.csv
In essence, it takes the first CSV file in the directory, turns it into a temporary file in order to get the field names and that file’s data, and then for the remaining CSVs, the script inserts all of the data into the temporary file except for the first line (where the field names are). Once it has gone through all of the files, it then renames the temporary file as a CSV. Renaming it to a .tmp file is important because otherwise the first file will be entered twice into the final combined file.
In the full script, it would also save the CSVs into the JustImported directory and then run an Excel document with a macro to convert the CSV to the Excel document. And that’s it. This batch file will work on Windows machines. If you have any feedback, please leave a comment.