How to merge (combine) multiple CSV files into one CSV file using CMD

Here’s a useful tip if you ever need to combine multiple CSV files into one CSV file. This may be useful if you need to run reports (such as a crystal report) based on the data – where you need the data to be in a single file.

Option 1 – CSV files without a header row

The following single command line will combine all CSV files in the folder as a single file titled ‘combined.csv’

copy *.csv combined.csv

csv-combine-1

If you want to run this from a cmd file, copy the following contents into a text file and save as ‘run.cmd’.

This command will automatically run from the folder the file is saved in, that is – if you save it to C:\TEMP it will look for CSV files in C:\TEMP and save the new file to C:\TEMP

@echo off
copy %~dp0*.csv %~dp0combined.csv

Option 2 – CSV files with header row

So what if your source files have a header row? The following command will take the header from the first file, then exclude it from the rest. Copy the following contents into a text file and save as ‘run.cmd’.

@echo off

ECHO Set working directory
pushd %~dp0

ECHO Deleting existing combined file
del combined.csv

setlocal ENABLEDELAYEDEXPANSION

REM set count to 1
set cnt=1

REM for each file that matches *.csv
for %%i in (*.csv) do (
REM if count is 1 it's the first time running
  if !cnt!==1 (
REM push the entire file complete with header into combined.csv - this will also create combined.csv
    for /f "delims=" %%j in ('type "%%i"') do echo %%j >> combined.csv
REM otherwise, make sure we're not working with the combined file and
  ) else if %%i NEQ combined.csv (
REM push the file without the header into combined.csv
    for /f "skip=1 delims=" %%j in ('type "%%i"') do echo %%j >> combined.csv
  )
REM increment count by 1
  set /a cnt+=1
)

Having troubles?

You can download an example of this script here: www.itsupportguides.com/downloads/csvmerge.zip

 

Note: this process will not work for XLS (or similar) files – CSV files are text files, their data can be easily accessed using scripts where as XLS files are binary files and require an application such as Microsoft Excel to access the data.