Merge multiple csv files using batch file

Question

Is it possible to merge multiple CSV files [same directory] into one using a batch file? If so, how can I do this?

I think that would apply to any ASCII file?

Answer 1

Sometimes you want to know what line came from which file. If you just use

copy *.csv merged.csv

you will get the files pasted one after the other, with no way to know which line came from which file.

A solution involves use of grep.exe utility. You can get it from places such as this one: http://gnuwin32.sourceforge.net/packages/grep.htm

Once you have it (and add its location to your PATH), go to the folder where the csv files are and run this in a command shell:

grep -e ^a*$ *.csv > t.txt

The result will be that all the files are added to t.txt, with the filename appended at the beginning of each line, separated by ":" from the file's text.

Example:

file1.csv

this,line

is,from

file,1

file2.csv

this,line

is,from

file,2

after you run the grep command, t.txt will have:

file1.csv:this,line

file1.csv:is,from

file1.csv:file,1

file2.csv:this,line

file2.csv:is,from

file2.csv:file,2

Change t.txt .t.csv, and open in Excel. The next step is to take the first column and split it to two column, using ":" as a delimiter (Data -> text to columns -> delimited and select "other" and put ":" in the field. )

Careful! add a new column between column A and B, so that when column A is split into 2 columns, it does not overwrite the contents of column B.

Note that if the text in the first csv field contains ":", this will be a bit more difficult as column A will split into more than 2 columns.

Also, the comments about duplicate headers hold here as well.

Answer 2

copy *.csv merged.csv

or

copy file1+file2+… merged.csv

for a more sophisticated script that avoids duplicate headers, see Batch Combine CSV Remove Header