Solved Merge multiple CSV files using Batch Script

February 25, 2013 at 23:59:16
Specs: Windows 7


I want batch script which would merge/join multiple CSV files from a folder into one.

Each CSV file has 2 columns delimited by comma (,) but number of rows varies. Also each of the CSV file name is unique so when we merge the CSV files I want the file name of the CSV to be the first column for each rows in the file.

So eventually when the script it run it’ll join multiple CSV files under a folder to one. From 2 columns the output file will have 3 columns where the first column would be the file name.

Could anyone please help me with the batch script which can do the above task.


See More: Merge multiple CSV files using Batch Script

Report •


#1
February 26, 2013 at 01:47:39

@echo off & setLocal enableDELAYedeXpansioN

pushd myfiles
if exist new.csv del new.csv

for /f "tokens=* delims= " %%a in ('dir/b *.csv') do (
call :sub1 %%a
)
goto :eof

:sub1
for /f "tokens=* delims= " %%i in (%1) do (
echo.%1,%%i
)>> new.csv
goto :eof
::====== script ends here =================

=====================
M2 Golden-Triangle


Report •

#2
February 26, 2013 at 01:59:45

Hi,

When I run the script it gives system cannot find the specified files yet join the files.

I think error is from line pushd myfiles . What does this means?

Also the first field is the entire file name. I want the .csv from the first column removed/stripped while combining files.

Thanks


Report •

#3
February 26, 2013 at 02:34:00
✔ Best Answer

:: ===== script starts here ===============
:: Rubal February 26, 2013 at 14:59:16
:: mergecsv.bat 2013-02-26 15:06:35.15
@echo off & setLocal enableDELAYedeXpansioN

pushd myfiles
if exist new.csv del new.csv

for /f "tokens=* delims= " %%a in ('dir/b *.csv') do (
set N=%%~Na
call :sub1 %%a
)
goto :eof

:sub1
for /f "tokens=* delims= " %%i in (%1) do (
echo.!N!,%%i
)>> new.csv
goto :eof
::====== script ends here =================

"I think error is from line pushd myfiles . What does this means?"

You need to change myfiles to the drive and directory where your files ARE.

=====================
M2 Golden-Triangle


Report •

Related Solutions

#4
February 26, 2013 at 03:04:37

Hi,

Thanks it works the way I wanted.

But now I have a situation with is not related to the above processing. It appears that all my CSVs have a , (comma) at end of the files and some rows have either 1 or 2 or 3 commas due to which the files doesnt get parsed to excel properly. How can I fix this?

I want all blank lines to be deleted and the number of colums to be 3.

Thanks


Report •

#5
February 26, 2013 at 03:16:38

post a few lines of your CSVs.

=====================
M2 Golden-Triangle


Report •

#6
February 26, 2013 at 04:01:23

Here it is

====

200094,"View Sale ,
,
Sale No.,200094,
Dealer Code,00135,
Group Order,,
,
Customer Identification Summary,
Customer Name:,Mr CHAMPAK BHOMIA,
DOB,21/05/1991,
Driving License,DLC/NDL/2012/100000,
Driving License State,Delhi,
Third Party Authority Name, ,
Third Party Authority DOB,,
,
Order Transaction Summary,
Sales Type,Provide Order,
,
SerStatee Number:,0091999999 ,

Delivery Status,,
,

,
Customer Contact Summary,
Area Code,100001,
Primary Contact No,099999999,
Contact Type,Handphone ,
Other Area Code,,
Other Contact No,,
,
DeliveryAddress,
Unit No,101,
Street No,1 ,
Street Addr,JAN PATH,
State,New Delhi State,
Postcode,100001,
,
Primary Identification,Valid Indian Drivers Licence (inc Learner),
Secondary Identification,""Passport,PAN Card"",
,
Order Notes,
Sales Notes,,
Telephony Notes,,
Admin Notes,Approved by Jaisingh,
,,
"
====


Report •

#7
February 26, 2013 at 04:02:52

I want to delete lines matching followings

,

,,

"
also the one matching string "View Sale


Report •

#8
February 27, 2013 at 00:42:45

:: =====  script starts here  ===============
:: this gets out , ,, " View Sale
:: lesson learned: if '%%a' neq '^"'   (
:: csvclean.bat  2013-02-26 19:19:13.92
@echo off > new.csv & setLocal enableDELAYedeXpansioN

for /f "tokens=* delims= " %%a in ('find /v "View Sale" ^< my.csv') do (
if '%%a' neq '^"'   (
if '%%a' neq '^,'   (
if '%%a' neq '^,^,' (
echo %%a
)))
)>> new.csv
goto :eof
::======  script ends here  =================

=====================
M2 Golden-Triangle


Report •

#9
February 27, 2013 at 01:58:16

hi

The above is not looping thru all the csvs in that folder.


Report •

#10
February 27, 2013 at 02:20:00

So you use nested FOR loops or call it as a sub; which is easier than nesting.

=====================
M2 Golden-Triangle


Report •


Ask Question