Solved

Windows Batch File - Read CSV

Posted on 2016-11-14
9
65 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 84

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 84

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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
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 84

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 84

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

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

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

The following is a collection of cases for strange behaviour when using advanced techniques in DOS batch files. You should have some basic experience in batch "programming", as I'm assuming some knowledge and not further explain the basics. For some…
When you receive another warning that your shared drive is almost full and you have asked your users to clean out old files again and again, here is a single command that may help. This command will place all the files that have not been used rec…

734 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