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.
@echo off & setLocal enableDELAYedeXpansioN pushd myfiles
if exist new.csv del new.csvfor /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
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
:: ===== script starts here ===============
:: Rubal February 26, 2013 at 14:59:16
:: mergecsv.bat 2013-02-26 15:06:35.15
@echo off & setLocal enableDELAYedeXpansioNpushd myfiles
if exist new.csv del new.csvfor /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
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
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,
,,
"
====
I want to delete lines matching followings ,
,,
"
also the one matching string "View Sale
:: ===== 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
So you use nested FOR loops or call it as a sub; which is easier than nesting. =====================
M2 Golden-Triangle
