Match column 3 in file1 to column 1 in file 2 and replace with column 2 from file2

Shell Programming and Scripting
1

Match column 3 in file1 to column 1 in file 2 and replace with column 2 from file2
file 1 sample

SNDK    80004C101       AT
XLNX    983919101       BB
NETL    64118B100       BS
AMD     007903107       CC
KLAC    482480100       DC
TER     880770102       KATS
ATHR    04743P108       KATS
RBCN    78112T107       JT
TXN     882508104       KATS
STM     861012102       KATS

file 2 sample

AT      AU
AU      AU
AV      AT
BB      BE
BS      BR
BSE     HU
BZ      BR
CC      CL
CD      CZ
CG      CN

file1 column3 will need to match one value in file2 column1. WHen a match is found, I need to overwite file1 column3 with the corresponding match in file2 column2.
so for the above samples, the output for line1 and line2 of new output will be:

SNDK    80004C101       AU
XLNX    983919101       BE

I have tried a join command to no success and have been messing with awk for a few hrs. I am close but no cigar. Please help.

2

Awk is probably the most straight forward. Assuming that the 'translation' file is short, I'd go with something along these lines:

#/usr/bin/env ksh

 awk -v xlate_file=$1 '
        BEGIN {
                while( (getline < xlate_file) > 0 )
                        xlate[$1] = $2;                 # load in translations
        }

        {
                $NF = xlate[$NF] ? xlate[$NF] : "<UNDEFINED>";
                print;
        }
' <input-file >new-file

Reads your translation file, and then applies it to the contents of the input file. Use the mv command to move the new file onto the old file after verification that it worked.

The name of the translation file is assumed to be passed in on the command line as the first (only) parameter.

Hope this gets you started.

1 Like
3

I keep getting

Unmatched '.

4 
 
awk 'NR==FNR{_[$1]=$2;next}_[$3]{print $1,$2,_[$3];next}{print}' file2 file1
5 
awk 'NR==FNR{A[$1]=$2;next}$3 in A{$3=A[$3]}1'  file2 file1
1 Like
6

There should be an open quote on the first line and a close just before the input redirection:

#!/usr/bin/env ksh

 awk -v xlate_file=$1 '
        BEGIN {
                while( (getline < xlate_file) > 0 )
                        xlate[$1] = $2;                 # load in translations
        }

        {
                $NF = xlate[$NF] ? xlate[$NF] : "<UNDEFINED>";
                print;
        }
'<input-file >new-file

I assumed you were talking about my sample. I also note that I somehow chopped a bang off of the #! line, but that shouldn't have caused you grief.

7 
awk 'NR==FNR{A[$1]=$2}A[$3]{sub($3,A[$3]);print}'  file2 file1
8

all worked well - thanks for the helping hand