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. Thanks a lot, it worked now with my test files.
    Now I was trying it with bigger files but it doesn’t do anything. Is there a capacity Limit for using it? Or do I Need to wait much longer until it’s finished?

  2. Hello again,

    i have tried it but the deleting order doesn’t work…it is combing it with the combined file 🙂

    @echo on

    ECHO Set working directory
    pushd %~dp0

    ECHO Deleting existing combined file
    set combined=C:SamCSV TESTKombinationcombined.csv
    del !combined!

    setlocal ENABLEDELAYEDEXPANSION
    set cnt=1

    for %%i in (*.csv) do (
    if !cnt!==1 (
    for /f “delims=” %%j in (‘type “%%i”‘) do echo %%j >> !combined!
    ) else if %%i NEQ !combined! (
    for /f “skip=1 delims=” %%j in (‘type “%%i”‘) do echo %%j >> !combined!
    )
    set /a cnt+=1
    )

    1. Must be having issues with the space in folder/file path.

      Try

      @echo on

      ECHO Set working directory
      pushd %~dp0

      ECHO Deleting existing combined file
      set “combined=C:SamCSV TESTKombinationcombined.csv”
      del “%combined%”

      setlocal ENABLEDELAYEDEXPANSION
      set cnt=1

      for %%i in (*.csv) do (
      if !cnt!==1 (
      for /f “delims=” %%j in (‘type “%%i”‘) do echo %%j >> “%combined%”
      ) else if %%i NEQ “%combined%” (
      for /f “skip=1 delims=” %%j in (‘type “%%i”‘) do echo %%j >> “%combined%”
      )
      set /a cnt+=1
      )

  3. Many thanks, I was looking for exactly something like that. This is really awesome. What must be changed, if I want to create the combined file in a different directory?

    1. Hey,

      This would save to a ‘C:TEMP’ folder instead – just make sure the folder already exists and account/user running the script has write access. I’m pretty sure you can’t do UNC paths – so if you’re wanting to target a location over the network you’ll have to map a drive first and use the mapped path.

      @echo on
      ECHO Set working directory
      pushd %~dp0
      ECHO Deleting existing combined file
      set combined=C:TEMPcombined.csv
      del !combined!
      setlocal ENABLEDELAYEDEXPANSION
      set cnt=1
      for %%i in (*.csv) do (
      if !cnt!==1 (
      for /f “delims=” %%j in (‘type “%%i”‘) do echo %%j >> !combined!
      ) else if %%i NEQ !combined! (
      for /f “skip=1 delims=” %%j in (‘type “%%i”‘) do echo %%j >> !combined!
      )
      set /a cnt+=1
      )

    2. Hello,
      i was trying it but i am losing the header. Did i do something wrong?

      @echo on

      ECHO Set working directory
      pushd %~dp0

      ECHO Deleting existing combined file
      set combined=C:SamCSV TESTKombinationcombined.csv
      del !combined!

      setlocal ENABLEDELAYEDEXPANSION
      set cnt=1

      for %%i in (*.csv) do (
      if !cnt!==1 (
      for /f “delims=” %%j in (‘type “%%i”‘) do echo %%j >> !combined!
      ) else if %%i NEQ !combined! (
      for /f “skip=1 delims=” %%j in (‘type “%%i”‘) do echo %%j >> !combined!
      )
      set /a cnt+=1
      )

    3. hello,

      sorry didn’t see the changes before…but now the existing file won’t be deleted…
      @echo on

      ECHO Set working directory
      pushd %~dp0

      ECHO Deleting existing combined file
      set combined=C:SamKombinationcombined.csv
      del !combined!

      setlocal ENABLEDELAYEDEXPANSION
      set cnt=1

      for %%i in (*.csv) do (
      if !cnt!==1 (
      for /f “delims=” %%j in (‘type “%%i”‘) do echo %%j >> !combined!
      ) else if %%i NEQ !combined! (
      for /f “skip=1 delims=” %%j in (‘type “%%i”‘) do echo %%j >> !combined!
      )
      set /a cnt+=1
      )

  4. Hi, Thanks for posting this awesome script. I’ve got a quick question, hope you could help me with it. I have multiple folders with csv files that I’d like to merge into one file. Could you please outline what needs to be changed to recursively check all the folders and merge all csv files in all the folders into one CSV. The folders are dynamically created.

    Thank you

Leave a Reply to Milano Cancel reply

Your email address will not be published.