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


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Match column 3 in file1 to column 1 in file 2 and replace with column 2 from file2
# 1  
Old 10-31-2010
Error Match column 3 in file1 to column 1 in file 2 and replace with column 2 from file2

Match column 3 in file1 to column 1 in file 2 and replace with column 2 from file2
file 1 sample
Code:
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

Code:
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:
Code:
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.

Last edited by radoulov; 11-01-2010 at 07:48 AM..
# 2  
Old 10-31-2010
Awk is probably the most straight forward. Assuming that the 'translation' file is short, I'd go with something along these lines:

Code:
#/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 themv 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.
This User Gave Thanks to agama For This Post:
# 3  
Old 11-01-2010
I keep getting

Unmatched '.
# 4  
Old 11-01-2010
Code:
 
awk 'NR==FNR{_[$1]=$2;next}_[$3]{print $1,$2,_[$3];next}{print}' file2 file1

# 5  
Old 11-01-2010
Code:
awk 'NR==FNR{A[$1]=$2;next}$3 in A{$3=A[$3]}1'  file2 file1

This User Gave Thanks to Scrutinizer For This Post:
# 6  
Old 11-01-2010
Quote:
Originally Posted by rydz00
I keep getting

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

Code:
#!/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  
Old 11-01-2010
Code:
awk 'NR==FNR{A[$1]=$2}A[$3]{sub($3,A[$3]);print}'  file2 file1

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Replace a column in tab delimited file with column in other tab delimited file,based on match

Hello Everyone.. I want to replace the retail col from FileI with cstp1 col from FileP if the strpno matches in both files FileP.txt ... (2 Replies)
Discussion started by: YogeshG
2 Replies

2. Shell Programming and Scripting

Match column 8 in file 1 with column 2 in file 2 and replace..

I am looking at the NR==FNR posts and trying to use them to achieve the following but I am not getting it. I have 2 files. I want to match column 8 in file 1 with column 2 in file 2. When they match I want to replace column 9 in file 1 with column 1 in file 2. This is and extract from file 1 ... (5 Replies)
Discussion started by: kieranfoley
5 Replies

3. Shell Programming and Scripting

awk Print New Column For Every Two Lines and Match On Multiple Column Values to print another column

Hi, My input files is like this axis1 0 1 10 axis2 0 1 5 axis1 1 2 -4 axis2 2 3 -3 axis1 3 4 5 axis2 3 4 -1 axis1 4 5 -6 axis2 4 5 1 Now, these are my following tasks 1. Print a first column for every two rows that has the same value followed by a string. 2. Match on the... (3 Replies)
Discussion started by: jacobs.smith
3 Replies

4. Shell Programming and Scripting

Need to select some column from file1 if condition match.

I have two files file1.txt and file2.txt. want to print some column(number,status,date1,date2,description(descrption column end before category column start with'Oracle Services') and assigned_to column from file1.txt ...If the assigned_to name in file2.txt matches with assinged_to name in... (2 Replies)
Discussion started by: vijay_rajni
2 Replies

5. Shell Programming and Scripting

Match part of string in file2 based on column in file1

I have a file containing texts and indexes. I need the text between (and including ) INDEX and number "1" alone in line. I have managed this: awk '/INDEX/,/1$/{if (!/1$/)print}' file1.txt It works for all indexes. And then I have second file with years and indexes per year, one per line... (3 Replies)
Discussion started by: phoebus
3 Replies

6. Shell Programming and Scripting

Based on column in file1, find match in file2 and print matching lines

file1: file2: I need to find matches for any lines in file1 that appear in file2. Desired output is '>' plus the file1 term, followed by the line after the match in file2 (so the title is a little misleading): This is honestly beyond what I can do without spending the whole night on it, so I'm... (2 Replies)
Discussion started by: pathunkathunk
2 Replies

7. UNIX for Dummies Questions & Answers

if matching strings in file1 and file2, add column from file1 to file2

I have very limited coding skills but I'm wondering if someone could help me with this. There are many threads about matching strings in two files, but I have no idea how to add a column from one file to another based on a matching string. I'm looking to match column1 in file1 to the number... (3 Replies)
Discussion started by: pathunkathunk
3 Replies

8. Shell Programming and Scripting

Replace column that matches specific pattern, with column data from another file

Can anyone please help with this? I have 2 files as given below. If 2nd column of file1 has pattern foo1@a, find the matching 1st column in file2 & replace 2nd column of file1 with file2's value. file1 abc_1 foo1@a .... abc_1 soo2@a ... def_2 soo2@a .... def_2 foo1@a ........ (7 Replies)
Discussion started by: prashali
7 Replies

9. Shell Programming and Scripting

Match one column of file1 with that of file2

Hi, I have file1 like this aaa ggg ddd vvv eeeand file2 aaa 2 aaa 443 xxx 76 aaa 34 ggg 33 wee 99 ggg 33 ddd 1 ddd 10 ddd 98 sds 23 (4 Replies)
Discussion started by: polsum
4 Replies

10. Shell Programming and Scripting

Changing one column of delimited file column to fixed width column

Hi, Iam new to unix. I have one input file . Input file : ID1~Name1~Place1 ID2~Name2~Place2 ID3~Name3~Place3 I need output such that only first column should change to fixed width column of 15 characters of length. Output File: ID1<<12 spaces>>Name1~Place1 ID2<<12... (5 Replies)
Discussion started by: manneni prakash
5 Replies
Login or Register to Ask a Question

Featured Tech Videos