In Window Batch - how do I parse CSV file where fields include Comma and double quote

Question

I have an input CSV file, ttt.csv, which is comma delimited, each field may include double quote and comma:

Here is the contents of ttt.csv:

"CN=Bar\\,Alex,OU=Users,OU=Headquarters,DC=CORP",Bar,Alex,"Barziza,Alex",BARAAA,[email protected]

"CN=Boo\\,Ryan,OU=Users,OU=Headquarters,DC=CORP",Boo,Ryan,"Boo,Ryan",BABBBB,[email protected]

I would need to loop this file, for each line, I would need to get each of the 6 values and create my SQL insert statement to database.

In my case for Line 2 I would need to get:

Value1=       CN=Bar\\,Alex,OU=Users,OU=Headquarters,DC=CORP
Value2=       Boo
Value3=       Ryan
Value4=       Boo,Ryan
Value5=       BABBBB
Value6=       [email protected]

I used delimiter which includes double quotes and it does not seems working:

set str2="CN=Bar\\,Alex,OU=Users,OU=Headquarters,DC=CORP",Bar,Alex,"Barziza,Alex",BAR‌AAA,[email protected]
echo %str2%
for /f "tokens=1 delims=(,")" %%a in ("!str2!") do ( set newstr2=%%a )
echo !newstr2!

Please share what you have tried so far and describe precisely where you are stuck; remember that SO is not a free code writing service... — aschipfl
– aschipfl, Commented Oct 6, 2015 at 7:43
I used delimiter which includes double quotes and it does not seems working: set str2="CN=Bar\\,Alex,OU=Users,OU=Headquarters,DC=CORP",Bar,Alex,"Barziza,Alex",BARAAA,[email protected] echo %str2% for /f "tokens=1 delims=(,")" %%a in ("!str2!") do ( set newstr2=%%a ) echo !newstr2! — Paul Z
– Paul Z, Commented Oct 6, 2015 at 10:56
set str2="CN=Bar\\,Alex,OU=Users,OU=Headquarters,DC=CORP",Bar,Alex,"Barziza,Alex",BARAAA,[email protected] echo %str2% for /f "tokens=1 delims=(,")" %%a in ("!str2!") do ( set newstr2=%%a ) echo !newstr2! — Paul Z
– Paul Z, Commented Oct 6, 2015 at 10:56
@PaulZ You're on the right track, but you're making it more difficult than it needs to be. Try using a plain for loop without the /f switch and delims. — rojo
– rojo, Commented Oct 6, 2015 at 11:37
I tried to use get double quote delimiter to get the first substring, and later strip first strip & use comma to delimit the remaining but still does not work: — Paul Z
– Paul Z, Commented Oct 6, 2015 at 11:51

rojo · Accepted Answer · 2015-10-06 15:29:59Z

2

As I commented above, just use a plain for loop -- no /f, no /r, no /d, no /l, just a plain, simple for loop. It'll handle CSV delimiters while treating quoted stuff as a single token.

@echo off
setlocal enabledelayedexpansion

set str2="CN=Bar\\,Alex,OU=Users,OU=Headquarters,DC=CORP",Bar,Alex,"Barziza,Alex",BARAAA,[email protected]
echo %str2%

set idx=0

for %%a in (%str2%) do (
    set "newstr[!idx!]=%%~a"
    set /a idx += 1
)

set newstr

Output:

C:\Users\me\Desktop>test.bat "CN=Bar\\,Alex,OU=Users,OU=Headquarters,DC=CORP",Bar,Alex,"Barziza,Alex",BARAAA, [email protected]
newstr[0]=CN=Bar\\,Alex,OU=Users,OU=Headquarters,DC=CORP
newstr[1]=Bar
newstr[2]=Alex
newstr[3]=Barziza,Alex
newstr[4]=BARAAA
newstr[5][email protected]

If your csv data contains unquoted spaces that should not be treated as token delimiters, you can temporarily convert spaces to underscores before splitting, then convert back like this:

@echo off
setlocal enabledelayedexpansion

set str2="CN=Ryan\\,David Paul,OU=Users,OU=Singapore,DC=GLOBAL,DC=CORP",Ryan,David Paul,"Ryan, David Paul",RPAUL123,[email protected]
echo %str2%

set idx=0

for %%a in (%str2: =_%) do (
    set "str=%%~a"
    set "newstr[!idx!]=!str:_= !"
    set /a idx += 1
)

set newstr

You can read more on substring substitution if you wish. Output:

C:\Users\me\Desktop>test.bat
"CN=Ryan\\,David Paul,OU=Users,OU=Singapore,DC=GLOBAL,DC=CORP",Ryan,David Paul,"Ryan, David Paul",RPAUL123,[email protected]
newstr[0]=CN=Ryan\\,David Paul,OU=Users,OU=Singapore,DC=GLOBAL,DC=CORP
newstr[1]=Ryan
newstr[2]=David Paul
newstr[3]=Ryan, David Paul
newstr[4]=RPAUL123
newstr[5][email protected]

Of course, if your data already contains underscores, then use a character it doesn't contain -- a backtick, a tilde, a dollar sign, or something else.

edited Oct 6, 2015 at 15:29

answered Oct 6, 2015 at 11:58

rojo

24.5k5 gold badges61 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Paul Z Over a year ago

When I added this block into my codes to read file, I got error: FOR /F "eol=; tokens=* delims=" %%i in (FinalUserList.csv) do ( setlocal enabledelayedexpansion set str=%%i set idx=0 for %%a in (%str%) do ( set "newstr[!idx!]=%%~a" set /a idx += 1 ) echo %newstr[0]% echo %newstr[1]% ) Error message says: Maximum setlocal recursion level reached.

Stephan Over a year ago

@PaulZ how about a) placing setlocal enabledelayedexpansion (only) before any loops) b) using endlocal if you wish / have to use setlocal within the loop?

aschipfl Over a year ago

I'm afraid this will fail as soon as str2 contains wildcards like * or ?; furthermore, whitespaces and ; and = are also problematic (unless within "")...

Paul Z Over a year ago

I looked the data file, the issue actually comes from the data. There is one guy that his first name is 2 names with a space: "CN=Ryan\\, David Paul,OU=Users,OU=Singapore,DC=GLOBAL,DC=CORP",Ryan,David Paul,"Ryan, David Paul",RPAUL123,[email protected] The codes interpret the string as: newstr[0]=CN=Ryan\\, David Paul,OU=Users,OU=Singapore,DC=GLOBAL,DC=CORP newstr[1]=Ryan newstr[2]=David newstr[3]=Paul newstr[4]=Ryan, David Paul newstr[5]=RPAUL123 newstr[6][email protected] What really the case should be: newstr[2]=David Paul newstr[3]=Ryan, David Paul newstr[4]=RPAUL123 newstr[5][email protected]

Paul Z Over a year ago

How do i modify set "newstr[!idx!]=%%~a" - it seems the space between the first name 'David Paul' has caused the problem.

|

Paul · Accepted Answer · 2015-10-06 17:22:59Z

-1

@echo off
(
echo "CN=Bar\\,Alex,OU=Users,OU=Headquarters,DC=CORP",Bar,Alex,"Barziza,Alex",BARAAA,[email protected]
echo "CN=Boo\,Ryan,OU=Users,OU=Headquarters,DC=CORP",Boo,Ryan,"Boo,Ryan",BABBBB,bbb@em
)>%tmp%\tmp.csv

for /f tokens^=^1*^ delims^=^" %%i in (%tmp%\tmp.csv) do (
  echo value0=       "%%i"
  for /f tokens^=^1-6^ delims^=^=^,^" %%a in ("%%j") do (
    echo value1=       %%a&echo value2=       %%b&echo value3=       %%c,%%d
    echo value4=       %%e&echo value5=       %%f&echo:
  )
)

output:

value0=       "CN=Bar\\,Alex,OU=Users,OU=Headquarters,DC=CORP"
value1=       Bar
value2=       Alex
value3=       Barziza,Alex
value4=       BARAAA
value5=       [email protected]

value0=       "CN=Boo\,Ryan,OU=Users,OU=Headquarters,DC=CORP"
value1=       Boo
value2=       Ryan
value3=       Boo,Ryan
value4=       BABBBB
value5=       bbb@em

edited Oct 6, 2015 at 17:22

answered Oct 6, 2015 at 16:52

Paul

2,6982 gold badges20 silver badges28 bronze badges

1 Comment

bryc Over a year ago

This does not work. I gave it: 1,2,3,4,5,6,7,"abc (efg, xyz)", and it gave me this: 0= 1,2,3,4,5,6,7, and 1= abc (efg and 2= xyz. All it's doing is splitting between a quote it found, and that is wrong. It fails to ignore the comma within the string, which is the point of this question.

Collectives™ on Stack Overflow

In Window Batch - how do I parse CSV file where fields include Comma and double quote

2 Answers 2

8 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related