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.

For example, if you have multiple CSV files but need to run a single report (such as a crystal report) based on the data.

Option 1 – Combine 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 – Combine 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

REM Set working directory
pushd %~dp0

REM Delete existing combined file
del combined.csv

setlocal ENABLEDELAYEDEXPANSION
set total=0
set count=0

REM Set total
for %%i in (*.csv) DO set /a total+=1

for %%i in (*.csv) DO (
cls
echo:Combining CSV files [!count!/%total%]
if !count!==0 (
for /f "delims=" %%j in ('type "%%i"') do echo %%j >> combined.csv
) else if %%i NEQ combined.csv (
for /f "skip=1 delims=" %%j in ('type "%%i"') do echo %%j >> combined.csv
)
set /a count+=1
)

ECHO Finished
PAUSE >NUL

Having troubles?

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

Note: this process will only work for CSV files – not XLS (or similar) files.

Article Downloads

TIP: You may need to right-click and select 'save link as'.

Tagged in

31 comments on “How to merge (combine) multiple CSV files into one CSV file using CMD

  1. Hi, and thanks for your script! It’s been hard to find, but I’m very close to what I need 🙂

    So, I think it’s an easy one for you

    I’m using your script with a source csv whose data starts at line 3, so I guessed and changed the set cnt=3 and it’s starting to read from 3rd line, ok.
    then I have 2 of these small csv files on the directory, that I want to merge with the all.csv (it’s the the previously concatenated csv files in one single file)
    it adds to my all.csv file correctly all the contents of the first new.csv;
    but it adds the contents of second csv in news column at the end of the last line of my all.csv.
    is it clear?
    So, what i need is a way to merge the 2nd csv file to the next line in the all.csv file, instead of putting its contents on the final column of last line…

    I think I could explained what is happenning, but I would happilly share with you my csvs, if required!

    Thanks

    1. Hey,

      Could you send me a sample of your CSV files and the cmd script to [email protected] (you’ll probably have to ZIP and upload it somewhere, or rename the ZIP to something else).

      Cheers,

      Adrian

  2. When I use the above script it gets stuck on the “deleting existed combined file” and does not really finish. I have been trying every which way to get this to work but I am soo stuck!

    1. Hi burnie,

      I’ve uploaded an example of this script here: http://itsupportguides.com/downloads/csvmerge.zip

      I suggest you extract the contents of the zip file, and see if the example runs for you. Then try to use the __run.cmd file in your CSV file.

      I’m suspecting where your CSV files are stored there may be some special file or folder permissions, such as being able to create files but not delete them.

      If it still doesnt work you should try copying the CSV files to a new folder on your
      desktop, along with the ___run.cmd file then run it.

    2. You are a champion! Going to try it out now, spent several hours bashing my head against this! Will postback once i get it working on the network drive of thats something you would like?

    3. Tried the mentioned fixes and it still gets stuck. This is the message:

      “Set working directory
      Deleting existing combined file”

      When I did get it to work and then opened the file all the comma seperated vales were completly out of order. I am going to try the combined comand and just delete the the header out of each seperate file except for the first one. That way I can work around the @echo cmd.

    4. Hi again burnie –

      It just occurred to me that you might be running it from a shared folder or network drive.

      I’ve changed the working directory to use pushd – that should work on UNC paths.

      ECHO Set working directory
      pushd %~dp0

  3. Thanks for the second option, you saved my time alot 🙂 Cheers !!
    If possible can you add code which files got merged. (in my case just wanted to verify the oder of files)

Leave a Reply to Paul David Cancel reply

Your email address will not be published. Required fields are marked *