Loop through CSV file with batch - Space issue

Question

I have a csv file like this

name,sex,age
venu,m,16
test,,22

[EDIT]
name could have comma also

"venu,gopal",m,16

I want to handle if sex is nothing and save it to another file.

I have a script like this

@Echo Off
For /F "usebackq tokens=1-3  delims=," %%a in (test.csv) Do (
   echo %%a, %%b, %%c >> test-new.csv
)

But for the third record, I am getting %%b as 22 which should be space. How to fix this?

[EDIT2]

I have tried as per that link. I am not sure what I am doing wrong. I am getting same issue. Please check it once.

@echo off
setlocal DisableDelayedExpansion

For /F "usebackq tokens=1-3 delims=" %%x in (C:\somefile.csv) Do (

    setlocal EnableDelayedExpansion
    set "var=%%x"   

    set "var=!var:"=""!"
    set "var=!var:^=^^!"
    set "var=!var:&=^&!"
    set "var=!var:|=^|!"
    set "var=!var:<=^<!"
    set "var=!var:>=^>!"
    set "var=!var:,=^,^,!"
    set var=!var:""="!
    set "var=!var:"=""Q!"
    set "var=!var:,,="S"S!"
    set "var=!var:^,^,=,!"
    set "var=!var:""="!"
    set "var=!var:"Q=!"


    For /F "tokens=1-3  delims=," %%a in ("!var:"S"S=","!") Do (
      endlocal
      echo %%~a, %%~b, %%~c
      setlocal EnableDelayedExpansion
      pause
    )

    endlocal

)

Community · Accepted Answer · 2017-05-23 11:44:40Z

2

This is a bit tricky, as multiple delims will be condensed to a single delim.

So you need to replace them before to a unique delim sequence.

@Echo Off
setlocal DisableDelayedExpansion
For /F "usebackq tokens=1-3  delims=" %%a in (test.csv) Do (
    set "line=%%a"
    setlocal EnableDelayedExpansion
    set "line="!line:,=","!""
    For /F "tokens=1-3  delims=," %%a in ("!line!") Do (
        echo %%~a, %%~b, %%~c 
    )
)

This enclose each column into quotes, and with the %%~a the quotes will be removed later

EDIT: The solution for embedded commas

In this case it's only a bit different than the solution for how to split on ';' in CMD shell

@echo off
setlocal DisableDelayedExpansion

For /F "usebackq tokens=1-3 delims=" %%x in (test.csv) Do (
    set "var=%%x"
    setlocal EnableDelayedExpansion
    set "var=!var:^=^^!"
    set "var=!var:&=^&!"
    set "var=!var:|=^|!"
    set "var=!var:<=^<!"
    set "var=!var:>=^>!"
    set "var=!var:,=^,^,!"
    rem ** This is the key line, the missing quote is intention
    call set var=%%var:""="%%
    set "var=!var:"="Q!"
    set "var=!var:^,^,="C!"
    set "var=!var:,,=,!"
    set "var=!var:""="!"

    set "var="!var:,=","!""
    for /F "tokens=1-3 delims=," %%a in ("!var!") do (      
        endlocal
        set "col1=%%~a"
        set "col2=%%~b"
        set "col3=%%~c"
        setlocal EnableDelayedExpansion
        if defined col1 (
            set "col1=!col1:"C=,!"
            set "col1=!col1:"Q="!"
        )

        if defined col2 (
            set "col2=!col2:"C=,!"
            set "col2=!col2:"Q="!"
        )

        if defined col3 (
            set "col3=!col3:"C=,!"
            set "col3=!col3:"Q="!"
        )
        echo a=!col1!, b=!col2!, c=!col3!
        endlocal
    )
)

edited May 23, 2017 at 11:44

CommunityBot

11 silver badge

answered May 21, 2012 at 13:29

jeb

83.2k20 gold badges181 silver badges237 bronze badges

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

7 Comments

Venu Over a year ago

But its not working when there is a comma in the name.. how to fix it.. Thanks for the help.

jeb Over a year ago

Then you should show how your csv is formatted with names including commas (perhaps "Smith, John",m,25?)

Venu Over a year ago

yeah apologies for that.. could you please tell me how to fix this? I will modify my question

jeb Over a year ago

It's a bit ;-) more complicated, something similar is solved at how to split on ';' in CMD shell

Venu Over a year ago

Please check my updated code. I have been struggling to find the solution. Please shed some light on this. Thanks in advance.

|

Chris · Accepted Answer · 2013-07-08 17:00:42Z

0

using a string editor like SSED, I have overcome this issue by creating a temp file where I have replaced ",," with ",-," twice, then regarding a "-" as having been an empty field in the original file...

 ssed "s/,,/,-,/ig;s/,,/,-,/ig" file1.csv > file1.tmp

Then the tokens are allocated correctly. If you need to edit the temp file and then return to a CSV, then use...

 ssed "s/,-,/,,/ig;s/,-,/,,/ig" file1.tmp > file1.csv

This seems much simpler than doing in flight string replacements by token and having subroutines/etc.

answered Jul 8, 2013 at 17:00

Chris

1

Collectives™ on Stack Overflow

Loop through CSV file with batch - Space issue

2 Answers 2

7 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related