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

Shell Programming and Scripting
1

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

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

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.

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

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

Hello kieranfoley,

Could you please try following and let me know if this helps you.

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.

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

Hi thanks! That works great but I should have said that file 1 can have more that 9 columns. For example this

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?

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!

4

Yes, that's correct. I think you had a typo, you should add Input_file names too as follows.

 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

1 Like
5

Also, this is printing out the lines twice when matches occur

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

Wouldn't this be simpler?

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