TAGS :Viewed: 9 - Published at: a few seconds ago

[ 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
    )
)