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
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.
This still works. And thanks for the lesson.
Did want to say I had 18mb between 28 files. I’m not using a high test pc right now, and the operation happened very quickly. 7 seconds or so. If that.
Thanks again!
HI,
I have used your script. For some reason it has created trailing spaces on last column of combined csv. Is there anything can be added to code to remove this space?
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?
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
)
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
)
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?
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
)
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
)
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
)
approved
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
Hi Milano,
you can merge them on http://merge-csv.com pretty easy.
Hi Milano.
Try https://www.synkronizer.com/ Excel Add-in to Merge Multiple Excel Files.