This is extension to another question (Loop through CSV file with batch - Space issue)
I have csv file content like this
name,sex,age,description,date
venu,m,16,test mesg,2012-05-01
test,f,22,"He is good guy
and
brilliant",2012-05-01
I am looping this file using this command.
For /F "usebackq tokens=1-3 delims=" %%x in (test.csv) Do (
But since there is line break in second row, I am getting 3 records even though there are two records in the file.
How to fix this? Thanks in advance.
The main problem seems to be to count the quotes in a line.
If the count of quotes is odd then you need to append the next line and count again the quotes.
Counting of characters in a string is a bit tricky, if you won't iterate through all charachters.
I used here the delayed reduction technic, each quote will be effectivly replaced by a +1 and all other characters are removed.
To begin and terminate the line in a proper way there is always one extra +1 at the beginning, which will be compensated by a -1 in front.
The main trick is to replace the complete text from one quote to the next with exactly one +1 by replacing each quote with !!#:#=.
This works as !#:#=...<some text>...! will always be expanded to +1, as the content of the variable # is +1 and so the search pattern # can't be found.
The other replacements are only necessary to avoid problems with exclamation marks and carets int the text.
:::::::::::::::::::::::::::::::::::::::::::
:CountQuotes <stringVar> <result>
setlocal EnableDelayedExpansion
set "line=!%~1!"
set "#=+1"
rem DelayedExpansion: double all quotes
set "line=!line:"=""!"
rem DelayedExpansion: remove all carets ^
set "line=!line:^=!"
rem PercentExpansion: Remove all !
set "line=%line:!=%"
rem PercentExpansion: Replace double quotes to !!#:#=
set "line=-1^!#:#=%line:""=^!^!#:#=%"
for /F "delims=" %%X in ("!line!") do (
set /a count=%%X!
)
(
endlocal
set %~2=%count%
exit /b
)
And the logic for appending lines and inserting linefeeds
@echo off
setlocal DisableDelayedExpansion
set "lastLine="
set LF=^
rem Two empty lines
for /F "delims=" %%A in (test.csv) do (
set "line=%%A"
setlocal EnableDelayedExpansion
set "line=!line:\=\x!"
if defined lastLine (
set "line=!lastLine!\n!line!"
)
call :CountQuotes line quoteCnt
set /a rest=quoteCnt %% 2
if !rest! == 0 (
for %%L in ("!LF!") DO set "line=!line:\n=%%~L!"
set "line=!line:\\=\!"
echo Complete Row: !Line!
echo(
set "lastLine="
) ELSE (
set "lastLine=!line!"
)
for /F "delims=" %%X in (""!lastLine!"") DO (
endlocal
set "lastLine=%%~X"
)
)
exit /b
:::::::::::::::::::::::::::::::::::::::::::
:CountQuotes <stringVar> <result>
setlocal EnableDelayedExpansion
set "line=!%~1!"
set "#=+1"
rem DelayedExpansion: double all quotes
set "line=!line:"=""!"
rem DelayedExpansion: remove all carets ^
set "line=!line:^=!"
rem PercentExpansion: Remove all !
set "line=%line:!=%"
rem PercentExpansion: Replace double quotes to !!#:#=
set "line=-1^!#:#=%line:""=^!^!#:#=%"
for /F "delims=" %%X in ("!line!") do (
set /a count=%%X!
)
(
endlocal
set %~2=%count%
exit /b
)
The Batch file below do what you want:
@echo Off
setlocal EnableDelayedExpansion
call :processFile < test.csv
goto :EOF
:processFile
set line=
set /P line=
if not defined line exit /b
set "line=!line:,,=,@,!"
for %%a in (name sex age description mydate) do set %%a=
for %%a in (!line!) do (
if not defined name (
set "name=%%a"
) else if not defined sex (
set "sex=%%a"
) else if not defined age (
set "age=%%a"
) else if not defined description (
set "description=%%a"
) else if not defined mydate (
set "mydate=%%a"
)
)
:checkDate
if defined mydate goto show
set /P line=
for /F "tokens=1* delims=," %%a in ("!line!") do (
set "description=!description! %%a"
set "mydate=%%b"
)
goto checkDate
:show
for %%a in (name sex age description mydate) do set /P "=%%a=!%%a!, " < NUL
echo/
goto processFile
I added the requirements you requested in your previous topic, that is, the sex may be empty (and is changed by @ character as I explained in my answer to that topic), and the name may include commas. I tested the program with this data file:
name,sex,age,description,date
venu,m,16,"test mesg",2012-05-01
test,,22,"He is good guy
and
brilliant",2012-05-01
"venu,gopal",m,16,"Another
multi-line
description",2012-05-02
And get these results:
name=name, sex=sex, age=age, description=description, mydate=date,
name=venu, sex=m, age=16, description="test mesg", mydate=2012-05-01,
name=test, sex=@, age=22, description="He is good guy and brilliant", mydate=2012-05-01,
name="venu,gopal", sex=m, age=16, description="Another multi-line description", mydate=2012-05-02,
Note that any field that contain commas or spaces must be enclosed in quotes.
