Search for values in 2 columns from CSV

Question

I have a csv files with say 20 odd columns.

I need to get the 14th and 15th column values where value in 14th column is say "VALUE1" and value in 15th column is "VALUE2".

My condition gets satisfied only when 14th column has VALUE1 and 15th column has VALUE2. I need to get the aggregate count.

I think wc -l could give me the count list and cut -d "," -f14,15 gives me the 14th and 15th column values. But how to check whether 14th has VALUE1 and 15th has VALUE2?

UPD: I think the one below works

grep -r "" * | cut -d " " -f14,15 | grep "Value1" | grep "Value2"

but still I dont think this is the perfect way to do do it.

Answer 1

awk could be more useful here.

For example:

$> echo "a b c d e" | awk '$2=="b" && $3=="c" {print}'
a b c d e

$> echo "a b c d e" | awk '$2=="a" && $3=="c" {print}'

$> echo "a b c d e" | awk '$2=="b" && $3=="d" {print}'

So answer to your question could be:

awk '$14=="VALUE1" && $15=="VALUE2" {print}'

Answer 2

If awk is not available you can do it with cut, grep and wc:

$ echo -e 'a,b, c,d\na,val1 ,val2,c' \
   | cut -d ',' -f2,3 | grep '^ *val1 *, *val2 *$' | wc -l

Assuming , as delimiter (and no somehow escaped , is included) in the input. For testing purposes, the columns 2 and 3 are used instead of 14 and 15.

Note that the grep pattern allows trailing whitespace after/before the values (you can remove the * sub-patterns if you don't want that). The meta-characters ^ and $ match the beginning and the end of a line.

The pipe grep "Value1" | grep "Value2" from you question does not do what you specify - it would match too much, e.g.:

    ..., Value1Value2, , ...
    ..., Value1, Value2, ...
    ..., OtherValue1, Value2, ...
    ...

If awk is available (it is pretty standard) you can do it like this:

$ echo -e 'a,b, c,d\na,val1,val2,c' \
    | awk -F, '$2 == "val1" && $3 == "val2" {++sum} END {print sum}'

awk automatically trims whitespace from the values. END is a specially pattern that matches after all lines are processed.

Answer 3

The following function will use only Bash to do what you want:

foo () 
{ 
    local filename="$1";
    while IFS=, read -ra arr; do
        if [[ "${arr[13]}" = "VALUE1" && "${arr[14]}" = "VALUE2" ]]; then
            printf '%s\n' "${arr[13]}" "${arr[14]}";
        fi;
    done < "$filename"
}

Usage: foo [/path/to/file.txt]

Sample output:

rany$ cat > source.txt 
a,a,a,a,a,a,a,a,a,a,a,a,a,VALUE1,VALUE2
a,a,a,a,a,a,a,a,a,a,a,a,a,NOMATCH1,NOMATCH2
a,a,a,a,a,a,a,a,a,a,a,a,a,VALUE1,VALUE2

rany$ foo source.txt
VALUE1
VALUE2
VALUE1
VALUE2