Replace one field of CSV file based on entries in a second CSV fileI'm trying to merge 2 files together using awk. I want to print fields 1,2 from file1 as they are and replace field 3 according to a mapping stored in file 2.
File1:
1,top,yel
2,dress,bl
3,jeans,bl
File2:
bl,blue
yel,yellow
Desired output:
1,top,yellow
2,dress,blue
3,jeans,blue
What I tried so far
Here's the awk command I tried so far:
gawk 'BEGIN {FS=OFS=","} NR==FNR {seen[$3]=$1 "," $2;next} ($1) in seen {print seen[$1],$2}' file1.csv file2.csv
However when "bl" appears twice in file 1, the output only includes one of the occurences. In the above example it's missing line "2" from file1.
3,jeans,blue
1,top,yellow
Based on your desired output, it sounds like what you actually want to do is read file2.csv first, creating a mapping between the fields, and then apply that to file1.csv
awk 'BEGIN{OFS=FS=","} NR==FNR{clr[$1]=$2; next} {$3=clr[$3]; print}' file2.csv file1.csv
$3as the key and returning the corresponding value from the (arbitrarily named) arrayclr. - steeldriverseen[$3]=$1 "," $2;nextexcept the index is the 1st column (instead of the 3rd) and the assigned value is the 2nd column (instead of a concatenation of the 1st and 2nd) - steeldriver