windows - Help in writing a batch script to parse CSV file and output a text file

Question

I am struggling to write a batch script which can read a CSV file such as below

Name:, City:, Country:
Mark, London, UK
Ben, Paris, France
Tom, Athens, Greece

There will be a heading row in the CSV file. It should output to a text file as below:

Name:Mark
City:London
Country:UK

Name:Ben
City:Paris
Country:France

Name:Tom
City:Athens
Country:Greece

The field separator (:) in the above output is expected to be provided in the header row itself. So all that I need to do is concatenate the field heading and its value.

The number of columns in this CSV file is not fixed, so the script should not limit to 3 tokens. Kindly help!

Answer 1

@ECHO OFF
IF "%~1"=="" GOTO :EOF
SET "filename=%~1"
SET fcount=0
SET linenum=0
FOR /F "usebackq tokens=1-10 delims=," %%a IN ("%filename%") DO ^
CALL :process "%%a" "%%b" "%%c" "%%d" "%%e" "%%f" "%%g" "%%h" "%%i" "%%j"
GOTO :EOF

:trim
SET "tmp=%~1"
:trimlead
IF NOT "%tmp:~0,1%"==" " GOTO :EOF
SET "tmp=%tmp:~1%"
GOTO trimlead

:process
SET /A linenum+=1
IF "%linenum%"=="1" GOTO picknames

SET ind=0
:display
IF "%fcount%"=="%ind%" (ECHO.&GOTO :EOF)
SET /A ind+=1
CALL :trim %1
SETLOCAL ENABLEDELAYEDEXPANSION
ECHO !f%ind%!!tmp!
ENDLOCAL
SHIFT
GOTO display

:picknames
IF %1=="" GOTO :EOF
CALL :trim %1
SET /a fcount+=1
SET "f%fcount%=%tmp%"
SHIFT
GOTO picknames

This batch scipt:

• accepts one parameter, the name of the file to process;

• does not verify the presence of : at the end of a header token, and when the values are displayed they are placed immediately after the corresponding header tokens;

• trims all the leading spaces (but not the trailing ones);

• considers the first row to be the header row, which also defines the number of tokens to process in subsequent rows;

• supports up to 10 tokens, and the two areas highlighted in bold italics are responsible for that (so when you need to change the maximum number, modify both areas: if you increase the number, you must expand the "%%a"?"%%b"?"%%c"?… list, and, likewise, if you decrease the number, then shrink the list).