awklookup

Using AWK for Lookup Table and Replace first column of 1st file with another column of 2nd file


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

Solution

  • awk 'FNR==NR{a[$1]=$2 OFS $3;next}{print a[$1],$2}' File1.txt File2.txt | column -t
    

    Brief explanation,