Batch script skipping blank entries in a .CSV when delim is ','

Question

I have a .CSV that I am trying to sort through to create another file from the data, but when I run it through, it skips blank entries. For example, if a line is value,value,value,,,value and I try to get the 4th column, it would spit out 6th. Presumably because it is the next valid value. I don't want it to skip the blank entry as it can mess up the tables I'm trying to make. Anyone know how to resolve this? (Any tips are welcome as I suck at batch scripts)

Here is my script:

FOR /F "tokens=1,2,3,4,5,6,7,8,9,10,11,12,13,14 delims=," %%a in (file.csv) DO (
    echo %%a %%b %%c %%d %%e %%f %%g %%h %%i %%j %%k %%l %%m %%n
)
pause

score 6 · Answer 1 · answered Jul 09 '13 at 20:26

This is the standard behaviour of the FOR/F loop, consecutive delims only used as one delimiter.
But you can use a workaround with a second FOR/F.
Prefix each column with another character, split the line at the delim and remove the prefix.

setlocal EnableDelayedExpansion
FOR /F "delims=" %%L in (test.bat) DO (
    set "line=%%L,,,,,,,,"
    set "line=#!line:,=,#!"
    FOR /F "tokens=1,2,3,4 delims=," %%a in ("!line!") DO (
        set "param1=%%a"
        set "param2=%%b"
        set "param3=%%c"
        set "param4=%%d"
        set "param1=!param1:~1!"
        set "param2=!param2:~1!"
        set "param3=!param3:~1!"
        set "param4=!param4:~1!"
        echo !param1! !param2! !param3! !param4!
    )
)

score 2 · Answer 2 · edited May 23 '17 at 11:59

As jeb already mentiones in his answer, for /F treats consecutive delimiters as one. To avoid that, you could replace each delimiter , by "," and enclose each full line by "", so each field appears as being enclosed within "", which can easily be removed finally by the ~ modifier of the for /F variable; so there is no need to do any more string manipulations (like sub-string expansion) later on:

@echo off
setlocal EnableExtensions DisableDelayedExpansion
for /F "usebackq delims=" %%# in ("file.csv") do (
    set "LINE=%%#"
    setlocal EnableDelayedExpansion
    for /F "tokens=1-4 delims=," %%A in (^""!LINE:,="^,"!"^") do (
        endlocal
        echo Field 1: %%~A
        echo Field 2: %%~B
        echo Field 3: %%~C
        echo Field 4: %%~D
        setlocal EnableDelayedExpansion
    )
    endlocal
)
endlocal

This might not work properly if the data contain , characters by themselves (remember that , is not considered as delimiter in case a field is enclosed within "" in the original CSV file).

The toggling of delayed expansion is done to not lose any exclamation marks in the data.

Batch script skipping blank entries in a .CSV when delim is ','

2 Answers2

Linked

Related