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.

Related Articles

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

    1. No, not that I can think of – the script doesnt ‘look’ at the values – just copies line by line into a new file. You’ll need to look at what creates your data or how you’re handling it (reporting tool?) to see if you can do what you want.

  1. Hi, thank you very much for this helpful script. I have the problem, that if i use the file and merge all my csv file, a got a file with more than 5GB. To be able to work with it, i would need files with not more than 2 GB. Is there a way to merge all files in several files (that are not bigger than 2GB)? Thank you very much in advance!

  2. hi guys, the option2 does not work for me. it says the file is not recognised as an internal or external command, operable program of batch file

  3. 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

  4. 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

  5. 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

Your email address will not be published.