1
$ 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.

2 Answers 2

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
2
  • Sorry for editting the question, I even want to print a record in file1, if that does not exist in file2. Have edited the question for the expected output.
    – Programmer
    Commented Apr 9, 2019 at 13:37
  • added new solution with join and edited awk and csvsql solution that now all match your changed requirements.
    – pLumo
    Commented Apr 9, 2019 at 13:52
1

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .