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

Answer 2

I know this is an old question, but this type of question is my favorite one so here it is my answer:

@echo off
setlocal EnableDelayedExpansion

rem Create heading array:
set /P headingRow=< %1
set i=0
for %%h in (%headingRow%) do (
    set /A i+=1
    set heading[!i!]=%%~h
)

rem Process the file:
call :ProcessFile < %1
exit /B

:ProcessFile
set /P line=
:nextLine
    set line=:EOF
    set /P line=
    if "!line!" == ":EOF" goto :EOF
    set i=0
    for %%e in (%line%) do (
        set /A i+=1
        for %%i in (!i!) do echo !heading[%%i]!%%~e
    )
goto nextLine
exit /B

This program have not any limit in the number of fields. This version requires to enclose in quotes the elements that may have spaces or other Batch delimiters, but this restriction may be easily fixed.

Answer 3

Python makes this so easy it should be regulated by the government.

from csv import DictReader

with open('file', 'rb') as file:
    reader = DictReader(file)

    for line in reader:
        for field in reader.fieldnames:
            print '{0}{1}'.format(field.strip(), line[field].strip())

         print '\n'

Edit: I guess you need something native to the Windows command shell. Oh well.