awk for filtering the contents on two files

Question

$ file1.txt    
12345,865221,APPLE,ZZ,QQ,DD,GG,APPLE-FRUIT
12346,865222,MANGO,ZZ,QQ,DD,GG,MANGO-FRUIT
12347,865222,GRAPE,ZZ,QQ,DD,GG,GRAPE-FRUIT

$file2.txt
APPLE-FRUIT,10KG
MANGO-FRUIT,12KG

I have two files as mentioned above. I need to create a new file, as given below.

$Output
12345,865221,APPLE,ZZ,QQ,DD,GG,APPLE-FRUIT,10KG
12346,865222,MANGO,ZZ,QQ,DD,GG,MANGO-FRUIT,12KG
12347,865222,GRAPE,ZZ,QQ,DD,GG,GRAPE-FRUIT

One method I worked out was using a while loop. I read each line of file2 and compare first column with 8th column of file1. This way I am able to get desired output. I am looking for a simple awk command to achieve the same.

Answer 1

Using join:

join -t, -a 1 -1 8 -2 1 file1.txt file2.txt

---

Using awk:

Save the contents of first file in an array with the respective index as the key, and call that when processing second file:

awk -F "," '
    FILENAME=="file1.txt"{row[$8]=$0}
    FILENAME=="file2.txt"{OFS=","; print row[$1],$2}
' file*.txt

For your new requirements:

awk -F "," '
    FILENAME=="file1.txt"{row[$8]=$0}
    FILENAME=="file2.txt"{row[$1]=row[$1]","$2}
    END{ for (i in row) print row[i] }
' file*.txt

Note, this will change the sorting.

---

Using csvsql:

This is also a good use case for csvsql, especially as the above solutions will fail when you have a text field with quotes and the delimiter inside quotes:

csvsql -H \
  --query  "select file1.*, file2.b from file1 left join file2 on file1.h == file2.a" \
  file*.txt

Answer 2

I'd loop over the smaller file first:

awk -F, -v OFS=, '
    NR == FNR    { weight[$1] = $2; next }
    $8 in weight { $(NF+1) = weight[$8] }
    1
' file2.txt file1.txt