?
Solved

Windows Batch File - Read CSV

Posted on 2016-11-14
9
Medium Priority
?
103 Views
Last Modified: 2016-11-14
Hi there,

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

Open in new window


Expected output:
1
2
3

Thanks for your help,
Rene
0
Comment
Question by:ReneGe
  • 5
  • 4
9 Comments
 
LVL 86

Expert Comment

by:oBdA
ID: 41886857
So you want the last element per line?
@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 %%b in (%%a) do set LastElement=%%b
	echo !LastElement!
)
	
ECHO.
PAUSE
EXIT /b

Open in new window

0
 
LVL 10

Author Comment

by:ReneGe
ID: 41886880
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

@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

Open in new window


Where the expected output would still be:
1
2
3

Thanks and cheers
0
 
LVL 86

Accepted Solution

by:
oBdA earned 2000 total points
ID: 41886917
Then try this:
@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
set Col=4
FOR /F "tokens=2 delims==" %%a in ('SET Line') DO (
	set Line=,%%a
	set Line=!Line:,,=,"",!
	set /a i = 0
	for %%b in (!Line!) do (
		set /a i += 1
		if !i!==%Col% set Element=%%~b
	)
	echo !Element!
)
	
ECHO.
PAUSE
EXIT /b

Open in new window

0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
LVL 10

Author Comment

by:ReneGe
ID: 41886921
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.

@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

Open in new window


The output here would be:
a
big
apple

Cheers :)
0
 
LVL 86

Expert Comment

by:oBdA
ID: 41886936
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

Open in new window

0
 
LVL 10

Author Comment

by:ReneGe
ID: 41886964
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?
0
 
LVL 10

Author Comment

by:ReneGe
ID: 41886971
Cool!!!!!
I just learned something new :)

@echo off
for %%a in (a,b,"c,d",e) do echo %%a
pause

Open in new window

0
 
LVL 86

Expert Comment

by:oBdA
ID: 41886977
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.
0
 
LVL 10

Author Closing Comment

by:ReneGe
ID: 41886978
Thanks mate :)
You rock as always!!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article was inspired by a question here at Experts Exchange (http://www.experts-exchange.com/Software/Photos_Graphics/Images_and_Photos/Q_28629170.html). The requirements stated in that question are (1) reduce the file size of a large number of…
This is a fine trick which I've found useful many times, when you just don't want to accidentally run a batch script or the commands needs administrator rights.
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Planning to migrate your EDB file(s) to a new or an existing Outlook PST file? This video will guide you how to convert EDB file(s) to PST. Besides this, it also describes, how one can easily search any item(s) from multiple folders or mailboxes…

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question