I am trying to use AWK Command in shell for lookup table to replace column with another column value matching the first file.
awk 'FNR==NR{a[$1]=$1,a[$2]=$2;next}{print a{$1],a[$2]}' file1.txt file2.txt
I am not having any success. Please help me with AWK Command Syntax - that would return the desired output results?
File1.txt
VALUE_TCP21:Z9E1AG.PNT ABC1 SILVER
VALUE_TCP21:Z9E1CU.PNT ABC1 COPPER
VALUE_TCP21:Z9E1RH.PNT ABC1 HUMIDITY
VALUE_TCP21:Z9E1TT001.PNT ABC1 TEMP
File2.txt
VALUE_TCP21:Z9E1AG.PNT 3716.09
VALUE_TCP21:Z9E1CU.PNT 4015.55
VALUE_TCP21:Z9E1RH.PNT 54.4023
VALUE_TCP21:Z9E1TT001.PNT 24.9414
Desired output:
ABC1 SILVER 3716.09
ABC1 COPPER 4015.55
ABC1 HUMIDITY 54.4023
ABC1 TEMP 24.9414
awk 'FNR==NR{a[$1]=$2 OFS $3;next}{print a[$1],$2}' File1.txt File2.txt | column -t
Brief explanation,
FNR==NR{a[$1]=$2 OFS $3;next}
: in file1.txt, assign $1
as the key, and $2 OFS $3
as the corresponding value in a. print a[$1],$2