Need to print field name if it is empty in CSV file using Windows batch or PowerShell

Question

I have a delimited file like below:

Field 0|Field 1|Field 2|Field 3|Field 4|Field 5
June 27 2017|5466|28998|52|3175507|1144121044

My requirement is if any of the above field is empty in .csv file, I need to print the name of the field that is empty and later email the output details saying this field is empty.

I tried below code but failing at last step to check if empty/NULL, pass value to another variable.

setlocal enabledelayedexpansion
@echo off
for /f "tokens=*" %%X in (TEMP.csv) do (
  set "work=%%X"
  :: fill empty fields with "#NUL#" ...
  :: but do it twice, just in case consecutive fields are empty
  for /l %%j in (1,1,2) do set "work=!work:||=|#NUL#|!"
  for /F "tokens=1,2,3,4,5,6* delims=|" %%i in ("!work!") do (
    echo first is %%i
    echo second is %%j
    echo third is %%k
    echo fourth is %%l
    echo fifth is %%m
    echo sixth is %%n
    echo -------------  
  )
)

What do you see? What do you expect to see? Applying the hints given at sscce.org, can you reproduce the problem with minimal code? — I'm with Monica
– I'm with Monica, Commented Jul 26, 2017 at 11:33
…if match line beginning or ending with | or containing ||; or …if "%%n"=="". — Compo
– Compo, Commented Jul 26, 2017 at 11:37
Myoutput is coming as below: first is June 27 2017 second is #NUL# third is 28998449388.11 fourth is 5244686087.57 fifth is 31755076225.46 sixth is I need a condition check on this output as if it is #NUL# like "second is #NUL#" I need to move print "Field 1 is empty" and later i will email the text on my email id for notification. and if more than two fields are having #NUL#, need to repeat notification on email for both field. — KAPIL
– KAPIL, Commented Jul 26, 2017 at 11:42
@KAPIL Please edit your question if you need to provide additional information. Do not bury it in comments. — Ansgar Wiechers
– Ansgar Wiechers, Commented Jul 26, 2017 at 12:22

Ansgar Wiechers · Accepted Answer · 2017-07-26 12:21:38Z

1

To identify which field is empty you need to make the output in your innermost loop conditional. Also, your routine for filling empty fields with a canary string doesn't account for an empty first or last field.

...
rem Add delimiters to beginning and end of line to allow detection of empty
rem first/last fields.
set "work=|%%X|"
rem Insert canary into empty fields (run twice to take care of consecutive empty
rem fields).
set "work=!work:||=|#NUL#|!"
set "work=!work:||=|#NUL#|!"
rem Remove additional delimiters from beginning/end of line.
set "work=!work:~1,-1!"
for /F "tokens=1,2,3,4,5,6* delims=|" %%i in ("!work!") do (
    if "%%i"=="#NUL#" echo Field 0 is empty
    if "%%j"=="#NUL#" echo Field 1 is empty
    if "%%k"=="#NUL#" echo Field 2 is empty
    if "%%l"=="#NUL#" echo Field 3 is empty
    if "%%m"=="#NUL#" echo Field 4 is empty
    if "%%n"=="#NUL#" echo Field 5 is empty
)
...

It would be easier to do this in PowerShell:

Import-Csv 'C:\path\to\input.csv' -Delimiter '|' | ForEach-Object {
    $_.PSObject.Properties |
        Where-Object { -not $_.Value } |
        Select-Object -Expand Name
}

edited Jul 26, 2017 at 12:21

answered Jul 26, 2017 at 12:01

Ansgar Wiechers

202k27 gold badges286 silver badges363 bronze badges

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

3 Comments

Stephan Over a year ago

your batch solution won't work, because for treats consecutive delimiters as one.

Ansgar Wiechers Over a year ago

@Stephan Thanks for the heads up. Fixed.

KAPIL Over a year ago

Done. Need another small help. As my output can have more than one field name as empty, I moved value of "Name" to a txt file as below: Select-Object -Expand Name | out-file D:\tmp\test\out.txt Now I need to email text present in out.txt and i tried blat out.txt -to [email protected] -subject "Field missing" -body "Field missing" I am getting email but, text present in file is not coming via email. Please note, I dont want as attachment in email. Please suggest.

Collectives™ on Stack Overflow

Need to print field name if it is empty in CSV file using Windows batch or PowerShell

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related