AWK comparing two files and printing individual columns

2
votes

I need help in comparing two files using awk script. Compare second column of file1.csv to the first column of file2.csv, if matches print the row in the following expected format.

file1.csv

abc,id123,newyork
bcd,id456,seattle

file2.csv

id678,bbb,ccc
id123,hhh,ddd

expecte format:

abc,id123,hhh,newyork,{hhh,ddd},ddd

I am able to print up to the following so far

abc,id123,newyork,hhh,ddd

using the following awk,

$ awk -F, 'FNR==NR{f1[$2]=$0; next} $1 in f1 {print f1[$1] "," $2 "," $3}' file1.csv file2.csv
1
awk

1 Answers

3
votes

You were close. Try:

$ awk -F, 'FNR==NR{f1[$2]=$1; f3[$2]=$3; next} $1 in f1 {printf "%s,%s,%s,%s,{%s,%s},%s\n",f1[$1],$1,$2,f3[$1],$2,$3,$3}' file1.csv file2.csv
abc,id123,hhh,newyork,{hhh,ddd},ddd

The key additions here are the use of a second array f3 to capture the input and printf to get fully formatted output.