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
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.
Hi,
this script is just great, and it works for what I am needing. However, I have a question and hope you can give me a hand. I need to merge only the 3 or 4 last files within that folder, so, is there any way to select the files that need to be combined?
Thanks a lot,
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.