How to find empty values while reading csv file using batch file?

Question

I am pretty new to batch file scripting.

I have to read data from the CSV file. Luckily I find a way to read.

But now struck with one more hurdle.

While reading CSV file I need to give warning for empty values of mandatory fields.

Given below is example of CSV file.

Example:

1,'EMPTY FIELD','abc','efg','tiger'
2, '172.16.2.22','xyz','str','lion'

Mandatory field is ipaddress. If that column is empty or null I need to give alert.

Batch file:

@echo off
SETLOCAL EnableDelayedExpansion
FOR /f "usebackq tokens=1-6* delims=, " %%a in ("template.csv") do (
    set sno=%%a
    set ipaddress=%%b
    set username=%%c
    set password=%%d
    set port=%%e
    set domain=%%f

    IF !ipaddress! == [] ECHO is empty

    ECHO !sno!, !ipaddress!, !username!, !password!, !port!, !domain! 
)
pause

To check the values is null or not I tried this:

IF !ipaddress! == [] ECHO is empty

But this IF condition is not working.

how are you reading it? You've given us an example of your file but not your current script! — Compo
– Compo, Commented Oct 13, 2016 at 18:39
Is it the literal string EMPTY FIELD or does the line actually look like 1,'','abc','efg','tiger'? Or does the line look like 1,'abc','efg','tiger'? — SomethingDark
– SomethingDark, Commented Oct 13, 2016 at 19:08
Please note that stackoverflow.com is not a free script/code writing service. If you tell us what you have tried so far (include the scripts/code you are already using) and where you are stuck then we can try to help with specific problems. You should also read How do I ask a good question?. — DavidPostill
– DavidPostill, Commented Oct 13, 2016 at 19:36
@SomethingDark, it actually look like 1,'','abc','efg','tiger'. — Aashick
– Aashick, Commented Oct 13, 2016 at 19:47

Mofi · Accepted Answer · 2016-10-14 05:43:20Z

The batch command line

IF !ipaddress! == [] ECHO is empty

compares the value of environment variable ipaddress with the string []. Yes, the square brackets don't have a special meaning. The command IF on usage for string comparison always compares just what is left of == with what is right of ==.

The code below is written to work for

1,'','abc','efg','tiger'
2, '172.16.2.22','xyz','str','lion'

as well as for

1,,abc,efg,tiger
2, 172.16.2.22,xyz,str,lion

The problem with delims=, is that command FOR interprets a sequence of commas as 1 delimiter. Therefore it is necessary to make sure that the row from CSV file does not contain 1 or more ,, which is done by replacing all commas by ,#. The inserted # is later removed from each non empty value.

@echo off
setlocal EnableDelayedExpansion
for /F "usebackq delims=" %%# in ("template.csv") do (
    set "Row=%%#"
    set "Row=!Row:, =,!"
    set "Row=!Row:,=,#!"
    for /F "tokens=1-6 delims=," %%a in ("!Row!") do (
        set "sno=%%a"
        set "ipaddress=%%b"
        if "!ipaddress!" == "#" (
            echo IP address is empty in: %%#
        ) else if "!ipaddress!" == "#''" (
            echo IP address is empty in: %%#
        )
        set "ipaddress=!ipaddress:~1!"
        set "username=%%c"
        if not "!username!" == "" set "username=!username:~1!"
        set "password=%%d"
        if not "!password!" == "" set "password=!password:~1!"
        set "port=%%e"
        if not "!port!" == "" set "port=!port:~1!"
        set "domain=%%f"
        if not "!domain!" == "" set "domain=!domain:~1!"
        echo !sno!, !ipaddress!, !username!, !password!, !port!, !domain!
    )
)
endlocal
pause

For understanding the used commands and how they work, open a command prompt window, execute there the following commands, and read entirely all help pages displayed for each command very carefully.

echo /?
endlocal /?
for /?
if /?
pause /?
set /?
setlocal /?

aschipfl · Accepted Answer · 2016-10-27 22:05:37Z

Here is a way to parse CSV data using a standard for loop to split each line. The only drawback is that the characters * and ? are not allowed in the entire file. The CSV file(s) need(s) to be provided as command line argument(s):

@echo off
setlocal EnableExtensions DisableDelayedExpansion

for %%F in (%*) do (
    set "FILE=%%~F"
    set /A "LIDX=0"
    for /F usebackq^ delims^=^ eol^= %%L in ("%%~F") do (
        set "LINE=%%L"
        set /A "LIDX+=1, IIDX=0"
        setlocal EnableDelayedExpansion
        for %%I in ("!LINE:,=" "!") do (
            endlocal
            set "ITEM=%%~I"
            set /A "IIDX+=1"
            setlocal EnableDelayedExpansion
            if "!ITEM:~,1!"=="'" if "!ITEM:~-1!"=="'" set "ITEM=!ITEM:~1,-1!"
            if "!ITEM!"=="" (
                if !IIDX! EQU 2 (
                    >&2 echo Empty item found: file "!FILE!", row !LIDX!, column !IIDX!.
                )
            )
        )
        endlocal
    )
)

endlocal
exit /B

The variables LIDX and IIDX hold row and column indexes, respectively. An error message is returned here in case the second column contains an empty value.

Collectives™ on Stack Overflow

How to find empty values while reading csv file using batch file?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related