Solved

Windows Batch File - Read CSV

Posted on 2016-11-14
9
75 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 85

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 85

Accepted Solution

by:
oBdA earned 500 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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

 
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 85

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 85

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

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

If like me you are one who spends a lot of time working and scripting with cmd.exe, sometimes it is handy to be able to quickly view a calendar for a given month and year. This script will quickly do just that!  Save the code posted below to a .bat …
You may have already been in the need to update a whole folder stucture using a script. Robocopy does it well and even provides a list of non-updated files in a log (if asked to). Generally those files that were locked by a user or a process by the …
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Suggested Courses

624 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