Solved

Windows Batch File - Read CSV

Posted on 2016-11-14
9
51 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 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
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 
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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
.csv files 5 100
search on network drive not working 4 67
Scraping specific data within an XML document 19 59
VB Script/Bat: check if a file has been modified based on prefix 10 77
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…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

856 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