Getting data from a flat file based on condition

Shell Programming and Scripting
1

Hi,

I have a flaty file from which i am fetching few columns in tablular form as below code.
Now i want to fetch the column 6 and 7 in below code only if it either of them is non zero.However below startement

awk -F, '$6==0 && $7==0{exit 1}' ${IFILE}

is not working..Not sure where is the problem.Pls help.

IFILE=/home/home1/Report1.csv
if  awk -F, '$6==0 && $7==0{exit 1}' ${IFILE}
then
  awk -F, '
    BEGIN{
     c=split("1,6,2,3,4,5,6", col)
     print "To: abc@gmail.com"
     print "Subject: Error report"
     print "MIME-Version: 1.0"
     print "Content-Type: text/html"
     print "Content-Disposition: inline\n"
     print "<HTML><TABLE border=1>"
     print "<TH>Heading 1</TH><TH>Heading 2</TH><TH>Heading 3</TH>"
     print "<TH>Heading 4</TH><TH>Heading 5</TH><TH>Heading 6</TH>"
     print "<TH>Heading 7</TH>"
    }
    NR>4 {
     printf "<TR>"
     for(i=1;i<=c;i++) printf "<TD>%s</TD>", $col
     print "</TR>"
    }
    END{
      print "</TABLE></BODY></HTML>"
    } ' ${IFILE} | sendmail -t
fi
2

I'm guessing you want to print all lines which match your condition?

awk -F, '($6 == 0) && ($7 == 0)' inputfile
3

yes..i want to print all the lines in which either of column 6 or column 7 is non zero.
But the condion is not working..Not sure what is the problem.

4

OK, so invert that:

awk -F, '($6 != 0) || ($7 != 0)' inputfile
5

not working..
I want to work as below.
Suppose there are 3 rows in files and clumn 6 and 7 have below values.

 
00
00
00

Except above case mail should come in all the cases.

6

I don't understand... Your 7-column file is a pair of zeroes per line?

7

My mistake..its like below, column 6 and 7 and are , separated.

0,0
0,0
0,0

8 
awk -F, '!(($6 == 0) && ($7 == 0))' inputfile
9

From above code also i am still getting the mail if both the columns are 0..

10

Perhaps it needs them forced into numbers...

awk -F, '!((($6+0) == 0) && (($7+0) == 0))' inputfile
11

Still the mail is coming...It seems that the problem is not with this condion but in opening and closing of if statement and location of

sendmail -t

in html code.Please guide me.

 
IFILE=/home/home1/Report1.csv
if  awk -F, '$6==0 && $7==0{exit 1}' ${IFILE}
then
  awk -F, '
    BEGIN{
     c=split("1,6,2,3,4,5,6", col)
     print "To: abc@gmail.com"
     print "Subject: Error report"
     print "MIME-Version: 1.0"
     print "Content-Type: text/html"
     print "Content-Disposition: inline\n"
     print "<HTML><TABLE border=1>"
     print "<TH>Heading 1</TH><TH>Heading 2</TH><TH>Heading 3</TH>"
     print "<TH>Heading 4</TH><TH>Heading 5</TH><TH>Heading 6</TH>"
     print "<TH>Heading 7</TH>"
    }
    NR>4 {
     printf "<TR>"
     for(i=1;i<=c;i++) printf "<TD>%s</TD>", $col
     print "</TR>"
    }
    END{
      print "</TABLE></BODY></HTML>"
    } ' ${IFILE} | sendmail -t
fi
12

So you want a program that skips entire files when it finds a line with 0,0 in it? Not what we settled on earlier, "print all lines which match your condition"?

13

Yes..no email should be sent and entire program should be skipped if both columns have 0 in all the lines.

14

Hm. exit doesn't work quite right unless it's in an END section.

awk '($6 == 0) &&($7 == 0) { E=1 } END { exit(E) }' inputfile
15

This gives exit code 1 if 1 line of the file has a 0 in both the columns:

awk '{n=$6==0 && $7==0?1:n} END{if(n)exit 1}' file

And this gives exit code 1 if all the lines have a 0 in both the columns:

awk '{n=$6==0 && $7==0?n+1:n} END{if(n==NR)exit 1}' file

Make your choice. :smiley:

16

I think he wants this, not sure

# All are zero
$ cat <<eod | awk -F, 'BEGIN{s=1}s{f=($1==0 && $2==0)?1:0}f==0{s=0}END{print f}' 
0,0
0,0
0,0
0,0
eod
1

# Not all are zero
$ cat <<eod | awk -F, 'BEGIN{s=1}s{f=($1==0 && $2==0)?1:0}f==0{s=0}END{print f}' 
0,0
0,0
0,0
0,1
eod
0

--edit---

$ awk -F, 'BEGIN{s=1}s{f=($1==0 && $2==0)?1:0}f==0{s=0}END{exit(f)}'

exit 1 if column6 and column7 are zero in all line, exit 0 if column6 and column7 are not zero in all line

$ awk 'BEGIN{s=1}s{f=($6==0 && $7==0)?1:0}f==0{s=0}END{exit(f)}'
17

Thnaks..I tried the soln proposed by Corona and Franklin but still i am getting the mail if column 6 and cloumn 7 both are 0 in file.
I want that if in any row of the file (either or both) of cloumn 6 and cloumn 7 are non zero then only mail should come.
Mail should not come only if all the rows in both cloumn have 0 values( only 1 case..)

case 1
0,0
0,0
0,0

No mail should be sent

Case 2
0,1
0,0
0,0

Mail should be sent

Case 3
0,0
1,0
0,1
Mail should be sent and so on.

18

codetags please

$ cat <<case1 | awk -F, 'BEGIN{s=1}s{f=($1==0 && $2==0)?1:0}f==0{s=0}END{exit(f)}';echo $?
0,0
0,0
0,0
case1

1

$ cat <<case2 | awk -F, 'BEGIN{s=1}s{f=($1==0 && $2==0)?1:0}f==0{s=0}END{exit(f)}';echo $?
0,1
0,0
0,0
case2

0

$ cat <<case3 | awk -F, 'BEGIN{s=1}s{f=($1==0 && $2==0)?1:0}f==0{s=0}END{exit(f)}';echo $?
0,0
1,0
0,1
case3

0
19

So you always want mail, unless the file is entirely zeroes in those columns? How about a running count then:

awk '{ T += $6 + $7 } END { exit(!T) }' inputfile

T is the count of ones. If it's zero, it exits with error status(should stop the if-statement), if it's nonzero it exits with success(should run the if statement).

20

i tried the code suggested by Corona,case 1 is success but in others also i am not getting the mail.

 
Case 1
0,0
0,0
0,0

No mail..success

 
Case 2:
0,0
0,1
0,0

No Mail..Failed..We should get a mail in this case.