Solved

Windows Batch File - Read CSV

Posted on 2016-11-14
9
20 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 82

Expert Comment

by:oBdA
Comment Utility
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
Comment Utility
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 82

Accepted Solution

by:
oBdA earned 500 total points
Comment Utility
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
 
LVL 10

Author Comment

by:ReneGe
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 82

Expert Comment

by:oBdA
Comment Utility
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
Comment Utility
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
Comment Utility
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 82

Expert Comment

by:oBdA
Comment Utility
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
Comment Utility
Thanks mate :)
You rock as always!!
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

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 …
Introduction: Recently, I got a requirement to zip all files individually with batch file script in Windows OS. I don't know much about scripting, but I searched Google and found a lot of examples and websites to complete my task. Finally, I was ab…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

772 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now