Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

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

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Match column 8 in file 1 with column 2 in file 2 and replace..
# 1  
Old 11-19-2015
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

Code:
MDSA01,20:00:54:7f:ee:4a:29:80,OAKS VSAN 4,1,12,0x2a1541,4 Gbps ,10000000c964e8e4,SEIITCDB16
MDSB01,20:00:54:7f:ee:56:ab:c0,OAKS VSAN 4,1,12,0xe512c0,4 Gbps ,10000000c964ec06,SEIITCDB16
MDSA01,20:00:54:7f:ee:4a:29:80,OAKS VSAN 4,port-channel, 2,0x2a0062,N/A,20000025b550137d,HOSTNAME_INFO
MDSA01,20:00:54:7f:ee:4a:29:80,OAKS VSAN 4,port-channel, 2,0x2a0063,N/A,20000025b55011dc,HOSTNAME_INFO
MDSB01,20:00:54:7f:ee:56:ab:c0,OAKS VSAN 4,1,14,0xe51ac0,4 Gbps ,10000000c95de114,SEIMOBDB03
MDSA01,20:00:54:7f:ee:4a:29:80,OAKS VSAN 4,1,15,0x2a0d00,8 Gbps ,10000000c9ae12ec,SEIDEVDB61


This is file 2

Code:
SEIESXPD21,20000025b550101c
SEIESXPD21,20000025b550137d
SEIESXPD22,20000025b55011dc
SEIESXPD22,20000025b550135d
SEIESXPD23,20000025b55011bc
SEIESXPD23,20000025b550133d


So in this example I want to replace HOSTNAME_INFO in file 1 with SEIESXPD21 when 20000025b550137d in column 8 in file 1 matches column 2 in file 2. and the same goes for 20000025b55011dc. I want to replace HOSTNAME_INFO with SEIESXPD22 in file 1.

The output should look like this.

Code:
MDSA01,20:00:54:7f:ee:4a:29:80,OAKS VSAN 4,1,12,0x2a1541,4 Gbps ,10000000c964e8e4,SEIITCDB16
MDSB01,20:00:54:7f:ee:56:ab:c0,OAKS VSAN 4,1,12,0xe512c0,4 Gbps ,10000000c964ec06,SEIITCDB16
MDSA01,20:00:54:7f:ee:4a:29:80,OAKS VSAN 4,port-channel, 2,0x2a0062,N/A,20000025b550137d,SEIESXPD21
MDSA01,20:00:54:7f:ee:4a:29:80,OAKS VSAN 4,port-channel, 2,0x2a0063,N/A,20000025b55011dc,SEIESXPD22
MDSB01,20:00:54:7f:ee:56:ab:c0,OAKS VSAN 4,1,14,0xe51ac0,4 Gbps ,10000000c95de114,SEIMOBDB03
MDSA01,20:00:54:7f:ee:4a:29:80,OAKS VSAN 4,1,15,0x2a0d00,8 Gbps ,10000000c9ae12ec,SEIDEVDB61

I have tried the following

Code:
awk -F, 'NR==FNR{A[$2]=$2;next}$8 in A{$9=A[$2]}1' file2.out file1.out

But I am getting a blank entry for column 9 in file 1 when I do this. That is when the matching occurs. Can somebody please help me with this?
# 2  
Old 11-19-2015
Hello kieranfoley,

Could you please try following and let me know if this helps you.
Code:
awk -F, 'FNR==NR{A[$NF]=$1;next} ($8 in A){$NF=A[$8];print $0} !($8 in A){print $0}'  OFS=, file2 file1

Output will be as follows.
Code:
MDSA01,20:00:54:7f:ee:4a:29:80,OAKS VSAN 4,1,12,0x2a1541,4 Gbps ,10000000c964e8e4,SEIITCDB16
MDSB01,20:00:54:7f:ee:56:ab:c0,OAKS VSAN 4,1,12,0xe512c0,4 Gbps ,10000000c964ec06,SEIITCDB16
MDSA01,20:00:54:7f:ee:4a:29:80,OAKS VSAN 4,port-channel, 2,0x2a0062,N/A,20000025b550137d,SEIESXPD21
MDSA01,20:00:54:7f:ee:4a:29:80,OAKS VSAN 4,port-channel, 2,0x2a0063,N/A,20000025b55011dc,SEIESXPD22
MDSB01,20:00:54:7f:ee:56:ab:c0,OAKS VSAN 4,1,14,0xe51ac0,4 Gbps ,10000000c95de114,SEIMOBDB03
MDSA01,20:00:54:7f:ee:4a:29:80,OAKS VSAN 4,1,15,0x2a0d00,8 Gbps ,10000000c9ae12ec,SEIDEVDB61

As per your request considering that always you need to change last column and column 8 from file1 and column 2 in file2 always should be compared.

Thanks,
R. Singh
# 3  
Old 11-19-2015
Hi thanks! That works great but I should have said that file 1 can have more that 9 columns. For example this

Code:
Switch_10,10:00:00:05:1e:36:6b:98,OAKS_PA,7,3,614300,N8,20000025b520102e,HOSTNAME_INFO,fnic v1.4.0.98 over fnic1
Switch_10,10:00:00:05:1e:36:6b:98,OAKS_PA,7,1,614100,N8,20000025b520102e,HOSTNAME_INFO,fnic v1.4.0.98 over fnic1


In this scenario I would need to do the following. Correct?

Code:
awk -F, 'FNR==NR{A[$2]=$1;next} ($8 in A){$9=A[$8];print $0} !($8 in A){print $0}'  OFS=, file2 file1

Thanks again!
Last edited by kieranfoley; 11-19-2015 at 09:30 AM..
# 4  
Old 11-19-2015
Quote:
Originally Posted by kieranfoley
Hi thanks! That works great but I should have said that file 1 can have more that 9 columns. For example this

Code:
Switch_10,10:00:00:05:1e:36:6b:98,OAKS_PA,7,3,614300,N8,20000025b520102e,HOSTNAME_INFO,fnic v1.4.0.98 over fnic1
Switch_10,10:00:00:05:1e:36:6b:98,OAKS_PA,7,1,614100,N8,20000025b520102e,HOSTNAME_INFO,fnic v1.4.0.98 over fnic1

In this scenario I would need to do the following. Correct?

Code:
awk -F, 'FNR==NR{A[$2]=$1;next} ($8 in A){$9=A[$8];print $0} !($8 in A){print $0}'  OFS=,

Thanks again!
Yes, that's correct. I think you had a typo, you should add Input_file names too as follows.
Code:
 awk -F, 'FNR==NR{A[$2]=$1;next} ($8 in A){$9=A[$8];print $0} !($8 in A){print $0}'  OFS=,  Input_file Input_file

Thanks,
R. Singh
This User Gave Thanks to RavinderSingh13 For This Post:
kieranfoley
# 5  
Old 11-19-2015
Also, this is printing out the lines twice when matches occur

Code:
awk -F, 'FNR==NR{A[$2]=$1;next} ($8 in A){$9=A[$8];print $0} !($8 in A){print $0}'  OFS=, file2 file1

Do you know why that is?

---------- Post updated at 01:39 PM ---------- Previous update was at 01:34 PM ----------

Never mid my last reply. This works perfect. Appreciate your help. Thanks!
# 6  
Old 11-19-2015
Wouldn't this be simpler?
Code:
awk -F, 'FNR==NR{A[$2]=$1;next} ($8 in A){$9=A[$8]} 1'  OFS=, file2 file1

This User Gave Thanks to RudiC For This Post:
kieranfoley
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Compare 1st column from 2 file and if match print line from 1st file and append column 7 from 2nd

hi I have 2 file with more than 10 columns for both 1st file apple,0,0,0...... orange,1,2,3..... mango,2,4,5..... 2nd file apple,2,3,4,5,6,7... orange,2,3,4,5,6,8... watermerlon,2,3,4,5,6,abc... mango,5,6,7,4,6,def.... (1 Reply)
Discussion started by: tententen
1 Replies

2. 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

3. Shell Programming and Scripting

How to match mandatory column in file.?

(3 Replies)
Discussion started by: Rishabh Jain
3 Replies

4. Shell Programming and Scripting

Match same file column data

File A B07 U51C 4434 L662C 4412 B07 L64U 612 L651B 4434 B07 L11C 4434 R151B 4434 B05 L12Z 612 L51B 4434 B01 651Z 612 L651C 4434 B04 A51Z 612 L51A 4434 L07 B08D 4434 B1B 4434 B07 RU8D 4434 L51A 4434 B07 L58D 4434 B51C 4434 B07 LA8D 4434 L4B 4434 Now i want File B Output B07... (2 Replies)
Discussion started by: asavaliya
2 Replies

5. 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

6. Shell Programming and Scripting

print when column match with other file

Hello all, please help. There are two file like this: file1: 1197510.0 294777.7 9666973.0 21.6 1839.8 1197510.0 294777.7 9666973.0 413.2 2075.9 1197510.0 294777.7 9666973.0 689.3 2260.0 ... (1 Reply)
Discussion started by: attila
1 Replies

7. Shell Programming and Scripting

Replace column with column from another file

Hello, I am trying to replace the column in file1 with the column from file2. The two files will have the same amount of rows. Each row will correspond with the same row in the other file. File1 "Replace this column" 500 13-APR-2011... (11 Replies)
Discussion started by: doobe01
11 Replies

8. Shell Programming and Scripting

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 SNDK 80004C101 AT XLNX 983919101 BB NETL 64118B100 BS AMD 007903107 CC KLAC 482480100 DC TER 880770102 KATS ATHR 04743P108 KATS... (7 Replies)
Discussion started by: rydz00
7 Replies

9. Shell Programming and Scripting

Strings from one file which exactly match to the 1st column of other file and then print lines.

Hi, I have two files. 1st file has 1 column (huge file containing ~19200000 lines) and 2nd file has 2 columns (small file containing ~6000 lines). ################################# huge_file.txt a a ab b ################################## small_file.txt a 1.5 b 2.5 ab ... (4 Replies)
Discussion started by: AshwaniSharma09
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