Filtering out lines in a .csv file

Shell Programming and Scripting
1

Hi Guys,

Would need your expert help with the following situation..

I have a comma seperated .csv file, with a header row and data as follows

H1,H2,H3,H4,H5.....    (header row)
0,0,0,0,0,1,2....         (data rows follow)
0,0,0,0,0,0,1
.........
.........

i need a code that would, trigger a set of instructions(if-then condition) .. based on the situation like .. if any of the rows in H5,H4,H3 .. but not H1,H2 has a value greater than 1.

thanks,
dev

2

awk has builtin column names $1, $2, ... $n

What exactly are you trying to do? - building a pile of if-then-else constructs is possible in awk in a very simple way. Huge blocks of if-then-else are prone to errors as well

Please give us sample input -> expected output.

3

You could use sed to delete heading line and then read columns into an Array. Remember bash arrays are indexed from zero, so H[0] is column 1, H[5] is column 6:

#!/bin/bash
IFS=,
sed '1d' infile | while read -a H
do
   if [ ${H[0]} -le 1 -a ${H[1]} -le 1 ] &&
      [ ${H[4]} -gt 1 -o ${H[3]} -gt 1 -o ${H[2]} -gt 1 ]
   then
      echo "set of instructions"
   fi
done
IFS=$' \t\n'
4

Hi Jim,

the sample file looks like this .. there are 18 columns in total, out of which last 3 are amount columns(D1 D2 D3), having certain values.. nothing has to be changed in the file .. the requirement is to read the last 3 columns of the file and in case any row has a value greater than '1' .. in that case 'a set of instructions' have to be executed..

H1 H2 H3 H4 H5 H6 H7 H8 H9 H10 H11 H12 H13 H14 H15 D1 D2 D3
                                                  0   0   0
                                                  0   0   1
                                                  0   0   0
                                                  1   0   1
 
.....................
..............

thanks,
Dev

5 
awk '
{
        if ( NR == 1)
        {
                print
                next
        }
        if ( $16 > 1 || $17 > 1 || $18 > 1 )
        {
                # Put your set of instructions here
        }
} ' file

Note: Set field separator to comma if fields in your file are separated by comma:

awk -F, '
6

thanks bipin, seems to be working but can this code be made a oneliner ?

7

Just put it in one line :wink:

awk -F, 'NR==1{print;next}$16>1||$17>1||$18>1{#Put your set of instructions here}' file
8

thanks for the quick response vipin, one last question though , how to introduce an else in here .. lets suppose the amounts are less than '1' .. a different set of instructions should be executed

9 
awk -F, 'NR==1{print;next}$16>1||$17>1||$18>1{#action;next}{#else action}' file
10

its working now .. thanks bipin :slight_smile:

there's one last glitch though .. i wanted to echo this entire command into a script file but that file doesnt seem to have the correct command

code:
echo "awk -F, 'NR==1{print;next}$16>1||$17>1||$18>1{print "success";next}{print "fail"}' file " > script.sh

but in file it comes out as

awk -F, 'NR==1{print;next}6>1||7>1||8>1{print success;next}{print fail}' file

the colored text show the deviation

Thanks,
Dev

11

When you echo, then you have to escape the meta-characters like dollar sign $ and double quotes " to preserve them.

Correction:

echo "awk -F, 'NR==1{print;next}\$16>1||\$17>1||\$18>1{print \"success\";next}{print \"fail\"}' file "