Check for null values in a column

Hi All,

I have a file with 10 columns and get the required data for nine columns properly except 8th. In 8th column i have both NULL and NON NULL values...i.e
certain records have values for all the columns including 8th column and certain records have 8th column as NULL.My requisite is,without opening or reading the file line by line i need to check if the file contains atleast one NULL value for 8th column .If NULL value exists then i need to reject the file else process the file.

for eg if my file contains records like this,

1,2,3,4,5,6,7,8,9,10
a,s,d,f,f,g,h,,k,l
aa,ss,bb,ee,ff,gg,bb,,kk,ii

how do i get my job done??
Thanks in Advance

Does something like this work for you?

 nawk 'FS="," {if ($8 == "") print "Line", NR, "REJECTED" }' file

HTH,
n.

Have you tried this solution?

Uhm, yes, works here with the input data from the OP.

nawk -F, '{if ($8 == "") print "Line", NR, "REJECTED" }' file

Which does the exact same thing unless I'm missing something.
(I do admit that

 nawk 'BEGIN { FS="," } {foo}' 

would have been better.)

Anyway it's a matter of taste, I prefer to set FS inside the script because it's a bit more flexible wrt more complex separators. But it really does not matter for the CSV above.

EDIT: I stand corrected. Behaviour of my original version is highly dependant on the awk implementation used. I works in /usr/bin/nawk from Solaris 10.
Just checked with The One True awk, and my original version does not work there. vgersh99s version and the BEGIN block variation do.

You are missing something...
The FS is changed after the line is read, so it doesn't affect the FS in the current line in the buffer. Your solution gives all the lines of the data from the OP. To get the proper output you have to rearrange the buffer with $0=$0:

nawk '{FS=",";$0=$0;if ($8 == "") print "Line", NR, "REJECTED" }' file

Regards

Is there an awk implementation/version that supports that syntax?
I mean:

awk 'FS="," { ... }'

/usr/bin/nawk from Solaris 10.

the Solaris' '/usr/xpg4/bin/awk' takes this as well:

/usr/xpg4/bin/awk 'FS="," {if ($8 == "") print "Line", NR, "REJECTED" }' file

but it does not return anything. Reevaluating the whole record [$0=$0] does not help either.
I don't understand why though: the [FS=","] is the condition which after the assignment should evaluate to 'true' and then the following action [{if ...}] should be executed.
But it looks like the the assignment [FS=","] returns 'false' and the 'action' never executes and no 'print' get executed:

/usr/xpg4/bin/awk 'FS="," {print $0;if ($8 == "") print "Line", NR, "REJECTED" }'

Weird.......

Thank you!
When I read your post I did a quick test with the bell-labs awk compiled on Linux
(I thought it was quite similar to the New awk shipped with Solaris) and, as Franklin52 pointed out,
you need to force the recompilation of the current record to get the desired result.

GNU awk and mawk behave the same.

And I confirm that the New awk (/usr/bin/nawk) shipped with Solaris and awk (New awk) on HP-UX recognize it (no recompilation of the current record is needed).

The X-Open POSIX Group awk (/usr/xpg4/bin/awk on Solaris) ignores it silently. The plain old awk (/bin/awk on Solaris) complains with an error.

Solaris:

% uname -r
5.8
% awk 'FS="," { print $2 }'<<<'a,b 
c,d'
awk: syntax error near line 1
awk: bailing out near line 1
% nawk 'FS="," { print $2 }'<<<'a,b 
c,d'
b
d
% /usr/xpg4/bin/awk 'FS="," { print $2 }'<<<'a,b 
c,d'
%

Linux:

% newawk 'FS="," { print $2 }'<<<'a,b
c,d'

d
% gawk 'FS="," { print $2 }'<<<'a,b
c,d'

d
% mawk 'FS="," { print $2 }'<<<'a,b
c,d'

d

Guys Thanks for the clarifications on awk n nawk.Guys you all are opening the file and reading it line by line.But how do i check whether a particular column contains atleast one NULL column without reading the file line by line..Thats my requisite.

all test unix commands like grep ,sed awk etc etc.. work on line you will get very very few commands in unix which act on full file at once so if you find any let me know:)

Vidyadar,i can read line by line and check whether the particular column has NULL or NOT.But i receive Huge files and they come in batches of 100's.so opening each and every file and reading it line by line consumes lot of time.I am trying out if there is any other possibility to create a shell script that is efficient and less time consuming for this problem.
Please do let me know if u find any command bro' .

radoulov,

Interesting, thanks for the unraveling!

Best regards

Guys the facts were very useful and interesting.. Never knew all these befpre.Thanks all for the efforts you guys put in.
Is there any way out to check whether a column contains NULL or Not without opening the file or reading it line by line.Any command or any possible way that can help me??!!!
Thanks in advance!

Without knowing your OS and shell there are too many possible ways.

You may try something like this (assuming POSIX shell and no embedded newlines in the filenames):

ifs=$IFS IFS='
'
set -- $(egrep -l '([^,]*,){7} *,' *)
for f in *; do
  for _f; do
    [ "$f" == "$_f" ] && continue 2
  done
  printf '%s\n' "$f" # do whatever you want with the file
done  

IFS=$ifs

On Solaris this script will work with /usr/xpg4/bin/egrep and possibly /usr/xpg4/bin/sh.

I am working on Kshell Radoulov.

... and did you try the script?

What OS?

i gave my best try for this.....
you can try any of the following three solution..
with grep

grep -l "^[^,]*,[^,]*,[^,]*,[^,]*,[^,]*,[^,]*,[^,]*,," filenames..

this will display the filenames where 8th column is NULL
with sed

sed -n '/^[^,]*,[^,]*,[^,]*,[^,]*,[^,]*,[^,]*,[^,]*,,/p' filenames..

with awk

 
awk '/^[^,]*,[^,]*,[^,]*,[^,]*,[^,]*,[^,]*,[^,]*,,/{exit}' filename..

here once a NULL value found in 8th column it won't search further so it should take little less time