How can I search a CSV file for a value, then get another value associated with it?

Question

myfile.csv

445,aLabel
446,anotherLabel
447,aThirdLabel

In a bash script, I want to search for the presence of "anotherLabel" (in a case-insensitive way) within myfile.csv and get the value 446.  Additionally, if "anotherLabel" isn't present, I want to add it to the end, incrementing the token of the previous line by 1.

I started with the if statement:

if grep -Fiq anotherLabel myfile.csv; then
    #get the value of field 1 (446 in this example) and store it in a variable
else
    #increment the last value of field 1 present in the file and store it in a variable
    #and append "448,anotherLabel" to myfile.csv
fi

I'm not sure that using grep to verify whether the label is even in the file is the best way to approach this or if there is an easier method using sed or awk.

Answer 1

Try this out:

 awk -F',' '{ if ($2 == "anotherLabel") { print $1 } }' myfile.csv

Answer 2

Using grep and tail:

search="anotherLabel"
file=myfile.csv

if value=$(grep -Pio -m1 "^[0-9]+(?=,$search$)" "$file"); then
    echo "do something with $value"
elif lastvalue=$(tail -n1 "$file" | grep -o '^[0-9]\+'); then
    # append lastvalue + 1 and search string
    echo "$((++lastvalue)),$search" >> "$file"
else
    # handle error
    echo "error. no integer value in last line of \"$file\" found." >&2
fi

In the first grep the following options are used:

• -P enable Perl-compatible regular expression (PCRE) to use a positive lookahead (see below).
• -i ignore case in pattern
• -o only print the matching part of the line
• -m1 stop after the first match

The first regex ^[0-9]+(?=,$search$) is using a positive lookahead (?=pattern) to match a number followed by , and the search string without comma and search string being part of the match itself. In combination with option -o only the matching part (the number) is printed.

Answer 3

If you wanted to do this in pure bash:

file="myfile.csv"
seek="anotherLabel"
while IFS=, read id label; do
    if [[ $label == "$seek" ]]; then
        myid=$id
        break
    fi
    lastid=$id
done < "$file"
if [[ -z $myid ]]; then
    myid=$((lastid + 1))
    echo "$myid,$seek" >> "$file"
fi
echo "$seek id is: $myid"

Answer 4

This is what you need:

$ awk -v tgt='the string you want to find' '
    BEGIN { FS=OFS="," }
    tolower($2) == tolower(tgt) { print $1 | "cat>&2"; f=1 }
    { print; p=$1 }
    END { if (!f) { print ++p, tgt; print p | "cat>&2"} exit !f }
' file

For example:

$ var=$( { awk -v tgt='anotherLabel' 'BEGIN{FS=OFS=","} tolower($2) == tolower(tgt){print $1 | "cat>&2"; f=1} {print; p=$1} END {if (!f) { print ++p, tgt; print p | "cat>&2"} exit !f}' file > out1; } 2>&1 )
$ echo "exit status: $?, value found: $var"
exit status: 0, value found: 446
$ cat out1
445,aLabel
446,anotherLabel
447,aThirdLabel

$ var=$( { awk -v tgt='missingLabel' 'BEGIN{FS=OFS=","} tolower($2) == tolower(tgt){print $1 | "cat>&2"; f=1} {print; p=$1} END {if (!f) { print ++p, tgt; print p | "cat>&2"} exit !f}' file > out1; } 2>&1 )
$ echo "exit status: $?, value found: $var"
exit status: 1, value found: 448
$ cat out1
445,aLabel
446,anotherLabel
447,aThirdLabel
448,missingLabel

The above will print any $1 that matches a $2 or is newly added to the end of the file to stderr (which is then captured in a variable, "var") set the exit status to success if the target string was found, failure otherwise, and print the whole file to stdout with the missing value added if appropriate.

Answer 5

The following awk code does what you ask for:

#!/bin/bash

filetosearch=myfile.csv
searchString=${1:-anotherLabel}

awk -F',' -v pat="$searchString" '
BEGIN{patl=tolower(pat);flag=0};
{prev=$1}(tolower($0)==patl){flag=1;exit}
END{
     if(flag){
          print prev
             }else{
          printf("%s%s%s\n", prev+1,FS,pat) >> ARGV[1]    # use ARGIND in gawk.
          print prev+1
             }
   }' "${filetosearch}"

Search for an string "${searchString}" that exactly match a full line (change tolower($0)==patl to tolower($0)~patl to match more loosely) and reports at which index it was found. If the string is not matched, it is added (appended) to the file used with an index that is one more than the last index of the file.

Example:

$ ./script aLabel
445

$ ./script anotherLabel
446

$ ./script MissingLabel
450

$ cat myfile.csv
445,aLabel
446,anotherLabel
447,aThirdLabel
448,dhdhdhdhdhd
449,anotherLabel4646
450,MissingLabel

Answer 6

You could make this a little shorter if you took out the diagnostics and error-handling:

file="myfile.csv"
label="$1"
if [ "$label" != "" ]
then
        IFS="," read val1 val2 <<< "$(awk -F, -v look4="$label" \
                'tolower($2) == tolower(look4) { found=1; print $1 "," $2; }
                        END { if (!found) print $1; }' "$file")"
        if [ "$val2" != "" ]
        then
                echo "Found value $val1 with label $val2."
        else
                val1=$((val1+1))
                if printf "%s,%s\n" "$val1" "$label" >> "$file"
                then
                        echo "Added value $val1 to file for label $label."
                else
                        echo "Failed to add value $val1 to file."
                fi
        fi
else
        echo "Missing label."
        exit
fi

This builds on Bart’s answer, using awk to compare the second field ($2) in each line to the desired label, but makes the test case-insensitive.  After the above code runs, $var1 will contain the value that corresponds to $label, one way or the other.  If $var2 is not blank, then it contains the exact label that was found.  If $var2 is blank, the label was not found (and an attempt was made to add it to the file).

Answer 7

I have done with below script

Let me know if there is any input

count=`awk '{print NR}' o.txt| awk '{print NR}'|sed -n '$p'`

for ((i=1;i<=$count;i++)); do sed -n ''$i'p' o.txt| grep "anotherLabel">/dev/null; if [[ $? == 0 ]]; then echo "anotherlabel exists"; awk -F "," -v i="$i"  'NR==i && $2 == "anotherLabel" {print $1}' o.txt; uni_anotherlabel=`sed -n ''$i'p' o.txt| grep "anotherLabel"| awk -F "," '{print $1}'`;else echo "anotherlabel doesnt exsists"; echo "$uni_anotherlabel"; fi; done