ReneGe
asked on
Windows Batch File - Read CSV
Hi there,
I need to read a csv file where the data fields may sometimes contain the delimiter character.
Expected output:
1
2
3
Thanks for your help,
Rene
I need to read a csv file where the data fields may sometimes contain the delimiter character.
@ECHO OFF
SETLOCAL EnableDelayedExpansion
SET Line1=first,a,"black, white",1
SET Line2=second,b,green,2
SET Line3=third,,red,3
FOR /F "tokens=2 delims==" %%a in ('SET Line') DO (
FOR /F "Delims=" %%b in ("%%~a") DO (
SET Line=%%b
SET Line=^"!Line:,=^",^"!^"
FOR /F "tokens=1-4 delims=," %%A in ("!Line!") DO ECHO %%~D
)
)
ECHO.
PAUSE
EXIT /b
Expected output:
1
2
3
Thanks for your help,
Rene
ASKER
Hi oBdA,
Thanks for your prompt reply.
My real challange here is that I have strings in my csv data files that are the same as the delimiter.
So my data sample should have been the following
Where the expected output would still be:
1
2
3
Thanks and cheers
Thanks for your prompt reply.
My real challange here is that I have strings in my csv data files that are the same as the delimiter.
So my data sample should have been the following
@ECHO OFF
SETLOCAL EnableDelayedExpansion
SET Line1=first,a,"black, white",1,one
SET Line2=second,b,green,2,two
SET Line3=third,,red,3,tree
FOR /F "tokens=2 delims==" %%a in ('SET Line') DO (
FOR /F "Delims=" %%b in ("%%~a") DO (
SET Line=%%b
SET Line=^"!Line:,=^",^"!^"
FOR /F "tokens=1-5 delims=," %%A in ("!Line!") DO ECHO %%~D
)
)
ECHO.
PAUSE
EXIT /b
Where the expected output would still be:
1
2
3
Thanks and cheers
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
More it goes, more I realize that my example script is probably to simplistic for my real application.
My real data is not a consecutive number, so here is another example.
The output here would be:
a
big
apple
Cheers :)
My real data is not a consecutive number, so here is another example.
@ECHO OFF
SETLOCAL EnableDelayedExpansion
SET Line1=first,a,"black, white",a,one
SET Line2=second,b,green,big,two
SET Line3=third,,red,apple,tree
FOR /F "tokens=2 delims==" %%a in ('SET Line') DO (
FOR /F "Delims=" %%b in ("%%~a") DO (
SET Line=%%b
SET Line=^"!Line:,=^",^"!^"
FOR /F "tokens=1-5 delims=," %%A in ("!Line!") DO ECHO %%~D
)
)
ECHO.
PAUSE
EXIT /b
The output here would be:
a
big
apple
Cheers :)
Yes? That's what the script above will create.
D:\Temp>type ReadCsvWithCommaData.cmd | findstr /r /c:"SET Line[0-9]"&echo.&ReadCsvWithCommaData.cmd
SET Line1=first,a,"black, white",a,one
SET Line2=,b,green,big,two
SET Line3=third,,red,apple,tree
a
big
apple
ASKER
Interresting
for %%b in (!Line!) do echo %%b
So it will consider whatever is between "" as an item. So if it contains a comma, the comma will be considered as being part of that data, rather than a new data field. Correct?
for %%b in (!Line!) do echo %%b
So it will consider whatever is between "" as an item. So if it contains a comma, the comma will be considered as being part of that data, rather than a new data field. Correct?
ASKER
Cool!!!!!
I just learned something new :)
I just learned something new :)
@echo off
for %%a in (a,b,"c,d",e) do echo %%a
pause
Yes; the normal "for" loop will use space or comma as delims if a list is inside the brackets, and respect delims enclosed in quotes.
ASKER
Thanks mate :)
You rock as always!!
You rock as always!!
Open in new window