[ Batch: convert pipe delimited text file to comma delimited csv file ]
I have a .txt
file like this:
Customer Number||Customer Name||Partner Number||Partner Name||Customer Country 1ABC||Jame||1234||Anny||USA 2DEF||Susan||5678||Prissy||UK
My output should be a .csv
file with no empty columns.
This is what I tried:
@echo off
setlocal disableDelayedExpansion
set input="C:\a.txt"
set output="C:\Customer_Pipe.csv"
>%output% (
for /f "tokens=*" %%a in ('input"') do (
set line=%%a
setlocal enableDelayedExpansion
echo "!line:|=","!">>"%~2"
)
)
Answer 1
If you want to read the file stored in variable INPUT
, you need to read it as %INPUT%
to do that (you stated INPUT
literally, with odd quotation). When you specify ''
within the set of for /F
, the part within ()
is interpreted as a command rather than a file, so for /F
tries to execute command input
which cannot be found and so the script fails. I stringly recommend to put ""
around the specified file, together with the usebackq
option, in order to avoid trouble with white-spaces in paths/file names.
If you want to write into the file stored in variable OUTPUT
, you must not redirect the echo
to somewhere else (you placed >>"%~2"
in the code).
Here is the code as I would write it:
@echo off
setlocal EnableExtensions DisableDelayedExpansion
rem Define constants here:
set "INPUT=%~1"
set "OUTPUT=%~2"
if not defined INPUT exit /B 1
if not defined OUTPUT set "OUTPUT=con"
> "%OUTPUT%" (
for /F usebackq^ delims^=^ eol^= %%L in ("%INPUT%") do (
set "LINE=%%L"
setlocal EnableDelayedExpansion
echo(!LINE:^|^|=,!
endlocal
)
)
endlocal
exit /B
The input file for the batch program must be provided as the first command line argument. The second argument (optional) is the output file.
Note that this script does not check whether the input data contains ,
characters, which impact the way the converted file is treated. If you want to put quotation marks around all fields of the returned CSV data to avoid data interpretation troubles, replace the above echo
command line by:
echo("!LINE:||=","!"
The output data (with the original echo
command line) looks like this:
Customer Number,Customer Name,Partner Number,Partner Name,Customer Country 1ABC,Jame,1234,Anny,USA 2DEF,Susan,5678,Prissy,UK
The output data with the modified echo
command line looks like this:
"Customer Number","Customer Name","Partner Number","Partner Name","Customer Country" "1ABC","Jame","1234","Anny","USA" "2DEF","Susan","5678","Prissy","UK"
Answer 2
There are two problems in your code.
1)
You use setlocal EnableDelayedExpansion
, that's a good idea, but you have to close it with endlocal
else you get an error after ~32 setlocals.
2)
Your replace functions looks a bit odd.
You add some quotes, they will be part of the output later.
You could try this.
echo(!line:^|^|=,!
At all
>%output% (
for /f "tokens=*" %%a in (%input%) do (
set "line=%%a"
setlocal EnableDelayedExpansion
set "line=!line:||=,!"
(echo(!line!)
endlocal
)
)