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.

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
  • 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 Apr 9 at 13:37
  • added new solution with join and edited awk and csvsql solution that now all match your changed requirements. – pLumo Apr 9 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.