replace nth column of a csv file by the help of lookup table

Question

I have a csv file (a.csv) as below (with thousands of lines):

NMS_NE,,,,,,,,,,,,,,,,,,,,,,,
D,NE Name,IP Address,Site Name,Product UPEI,Product Name,Product Model,NE Status,Serial Number,SW Major Release,SW Minor Release,SW Patch Release,Manufacturing Date,Installation Date ,Into Service Date ,Licensed System ID,License Key,Decommissioning Date,Description,,,,,
X,a155,1.1.1.155,,,,audi,,,4.8,abc48.677,,,,,,,,,,,,,
X,b145,1.1.1.145,,,,bmw,,,4.3,abc43.489,,,,,,,,,,,,,
X,c198,1.1.1.198,,,,vw,,,4.4,abc44.779,,,,,,,,,,,,,
...

And I have a lookup csv table (b.csv) as below (with hundreds of lines):

model,product name
bmw,1.16
audi,a3
vw,golf
...

What I want to do is: take the values of 'Product Model' (7th column) from a.csv and check this value in b.csv file. If the 'Product Model' value (from a.csv) is equal to 'model' value (from b.csv), then find the 'product name' against the matching 'model' value in b.csv and replace the 'Product Model' of a.csv with the relevant 'product name' of b.csv. So the output file should be:

NMS_NE,,,,,,,,,,,,,,,,,,,,,,,
D,NE Name,IP Address,Site Name,Product UPEI,Product Name,Product Model,NE Status,Serial Number,SW Major Release,SW Minor Release,SW Patch Release,Manufacturing Date,Installation Date ,Into Service Date ,Licensed System ID,License Key,Decommissioning Date,Description,,,,,
X,a155,1.1.1.155,,,,a3,,,4.8,abc48.677,,,,,,,,,,,,,
X,b145,1.1.1.145,,,,1.16,,,4.3,abc43.489,,,,,,,,,,,,,
X,c198,1.1.1.198,,,,golf,,,4.4,abc44.779,,,,,,,,,,,,,
...

How can I do this by simple awk command?

Answer 1

You can try following awk:

awk 'BEGIN { FS = OFS = ","; } FNR == NR { x[$1] = $2; next; } { if ($7 in x) { $7 = x[$7]; } } 1' b.csv a.csv

In BEGIN section we set field separator FS and output field separator OFS to ,. FNR == NR is a pattern which is true while reading first file (b.csv), and we create array x with keys form column $1 and values from column $2. In the last section we read second file and check if colum $7 exists as a key from array and if yes we change the value.