awk- Combine 2 .csv files based on a matching column

Question

I have two .csv files (comma separated) with many rows. Below are the sample entries of these two files: n.csv:

X,I180,10.5.5.180,,833-006366,00,N-D,,,5.2,A52.604,5.2.02,,,,,,,8471 FX,,,,,

f.csv:

X,3FE,1,I180,,,,I180:LT13,,,,,,,,,,,0,,,,,,NLS-A

What I want to do is:

• 1) fetch the fields: $2,$4,$8,$12,$25 of the f.csv and print them to the first columns of the new output .csv file ;
• 2) Fetch the field: $7 of n.csv file if the $2 of n.csv matches the $4 of f.csv and print it to the last column of the new output .csv file.

So the desired output should be:

3FE,I180,I180:LT13,,NLS-A,N-D

I tried following command but it does print the $7 of n.csv file in the last column:

awk 'BEGIN { FS = OFS = "," ; } FNR==NR{ne_model[$2]=$7;x[$2]=$2;next} { if ($4!="" && $4 in x)  {print $2,$4,$8,$12,$25,ne_model[$2]} }' n.csv f.csv
3FE,I180,I180:LT13,,NLS-A,

Could you please help to figure out what I am missing?

Answer 1

you mismatch $2 from n.csv to f.csv

try

awk 'BEGIN { FS = OFS = "," ; } 
  FNR==NR{ne_model[$2]=$7;x[$2]=$2;next} 
  { if ($4!="" && $4 in x)  {print $2,$4,$8,$12,$25,ne_model[x[$4]]} }' n.csv f.csv

I replace ne_model[$2] by ne_model[x[$4]] (and spilt in multiple line for readability)

which give

3FE,I180,I180:LT13,,NLS-A,N-D