Vlookup using Ask from specific column from two files

ranjancom2000 · December 4, 2017, 10:15am

File1

alias:server1_00,20:f1:0a:25:b5:03:02:90
alias:server2_00,20:f1:0a:25:b5:03:02:91
alias:server3_00,50:00:09:75:50:0d:bd:da
alias:server4_00,20:f1:0a:25:b5:03:02:93
alias:server5_00,21:00:00:24:ff:8b:e1:fe
alias:server6_00,50:00:09:75:50:08:54:44
alias:server7_00,10:00:00:00:c9:54:55:e1
alias:server_00,10:00:00:00:c9:5e:88:40

File2

Switch1, 381 12 45 F-Port 50:00:09:75:50:0d:bd:da
switch2, 8 8 3c0800 21:00:00:1b:32:12:8c:20
switch2, 9 9 3c0900 21:00:00:1b:32:9e:fe:ec
switch2, 10 10 3c0a00 21:00:00:24:ff:8b:e2:04
switch2, 12 12 3c0c00 21:00:00:24:ff:05:01:20
switch2, 13 13 3c0d00 21:00:00:24:ff:8b:e1:fe
switch3, 339 10 35 F-Port 50:00:09:75:00:0c:e9:d4
switch3, 340 10 36 F-Port 50:00:09:75:00:0c:e9:2c
switch3, 341 10 37 F-Port 50:00:09:75:00:0c:e9:6c
switch3, 342 10 38 F-Port 50:00:09:75:00:0c:e9:ac

Output Required (Check if the WWN from file2 is available and append the column to that line)

Switch1, 381 12 45 F-Port 50:00:09:75:50:0d:bd:da|alias:server3_00,50:00:09:75:50:0d:bd:da
switch2, 8 8 3c0800 21:00:00:1b:32:12:8c:20|Notfound
switch2, 9 9 3c0900 21:00:00:1b:32:9e:fe:ec|Notfound
switch2, 10 10 3c0a00 21:00:00:24:ff:8b:e2:04|Notfound
switch2, 12 12 3c0c00 21:00:00:24:ff:05:01:20|Notfound
switch2, 13 13 3c0d00 21:00:00:24:ff:8b:e1:fe|alias:server5_00,21:00:00:24:ff:8b:e1:fe
switch3, 339 10 35 F-Port 50:00:09:75:00:0c:e9:d4|Notfound
switch3, 340 10 36 F-Port 50:00:09:75:00:0c:e9:2c|Notfound
switch3, 341 10 37 F-Port 50:00:09:75:00:0c:e9:6c|Notfound
switch3, 342 10 38 F-Port 50:00:09:75:00:0c:e9:ac|Notfound

I am using this awk script

awk 'FNR == NR && $10 ~ /([0-9a-f]+:){7}[0-9a-f]+/ {A[$10]=P};FNR == NR {P=$0;next};$10 ~ /([0-9a-f]+:){7}[0-9a-f]+/ {$10=$10 ";" A[$10]}1' File1 File2

I am getting compared for first line only

RudiC · December 4, 2017, 10:28am

First thing that caught my eyes is there's no field 10 in any file.

ranjancom2000 · December 4, 2017, 10:46am

Hi RudiC,

I dont have much info

awk 'FNR == NR

Dont know how to Vlookup

take the third ROW from File1
And check with ROW fifth or sixth from File2

RudiC · December 4, 2017, 10:50am

When writing ROW you mean column or field, don't you? How about

awk 'FNR == NR {T[$2] = $0; next} $0 = $0 "|" (T[$NF]?T[$NF]:"Notfound")' FS="," file1 FS=" " file2
Switch1, 381 12 45 F-Port 50:00:09:75:50:0d:bd:da|alias:server3_00,50:00:09:75:50:0d:bd:da
switch2, 8 8 3c0800 21:00:00:1b:32:12:8c:20|Notfound
switch2, 9 9 3c0900 21:00:00:1b:32:9e:fe:ec|Notfound
.
.
.

ranjancom2000 · December 4, 2017, 1:16pm

Hi RudiC,

This code is working but i have some other issue in input file. So i am getting for lot of wwn not found.

There is some issue in filter in file1. I am checking on that but your code is working

ranjancom2000 · March 21, 2018, 9:25am

Is there any number i need to change NR {T[$2]

awk 'FNR == NR {T[$2] = $0; next} $0 = $0 "|" (T[$NF]?T[$NF]:"Notfound")' FS="," file1 FS=" " file2

Now i have input like this. For all record it was showing Notfound

RudiC · March 21, 2018, 9:51am

What input has changed? What should the output look like?
Give he whole picture!

ranjancom2000 · March 21, 2018, 9:57am

THis is original input file i need check the WWN from file1 and append the line if the WWn found. If not mark has notfound

Switch1, 381 12 45 F-Port 50:00:09:75:50:0d:bd:da
switch2, 8 8 3c0800 21:00:00:1b:32:12:8c:20
switch2, 9 9 3c0900 21:00:00:1b:32:9e:fe:ec
switch2, 10 10 3c0a00 21:00:00:24:ff:8b:e2:04
switch2, 12 12 3c0c00 21:00:00:24:ff:05:01:20
switch2, 13 13 3c0d00 21:00:00:24:ff:8b:e1:fe
switch3, 339 10 35 F-Port 50:00:09:75:00:0c:e9:d4
switch3, 340 10 36 F-Port 50:00:09:75:00:0c:e9:2c
switch3, 341 10 37 F-Port 50:00:09:75:00:0c:e9:6c
switch3, 342 10 38 F-Port 50:00:09:75:00:0c:e9:ac

New Input file was having like this

10.62.75.120, portIndex: 247 portName: slot12 port23,20:f1:0a:25:b5:03:02:90
10.62.75.120, portIndex: 247 portName: slot12 port23,20:50:00:2a:6a:00:b8:00 
10.62.75.120, portIndex: 247 portName: slot12 port23,20:50:00:2a:6a:00:b8:00 
10.62.75.120, portIndex: 247 portName: slot12 port23,21:00:00:24:ff:8b:e1:fe
10.62.75.120, portIndex: 247 portName: slot12 port23,20:50:00:2a:6a:00:b8:00

RudiC · March 21, 2018, 5:10pm

No surprise if you change the structure of your files. Get rid of the FS=" " and report back.

ranjancom2000 · March 22, 2018, 7:35am

I ran the script by removing FS=" " same output for all record it was updating not found.

RudiC · March 22, 2018, 9:26am

awk 'FNR == NR {T[$2] = $0; next} $0 = $0 "|" (T[$NF]?T[$NF]:"Notfound")' FS="," file1  file2
10.62.75.120, portIndex: 247 portName: slot12 port23,20:f1:0a:25:b5:03:02:90|alias:server1_00,20:f1:0a:25:b5:03:02:90
10.62.75.120, portIndex: 247 portName: slot12 port23,20:50:00:2a:6a:00:b8:00 |Notfound
10.62.75.120, portIndex: 247 portName: slot12 port23,20:50:00:2a:6a:00:b8:00 |Notfound
10.62.75.120, portIndex: 247 portName: slot12 port23,21:00:00:24:ff:8b:e1:fe|alias:server5_00,21:00:00:24:ff:8b:e1:fe
10.62.75.120, portIndex: 247 portName: slot12 port23,20:50:00:2a:6a:00:b8:00|Notfound

on Ubuntu 16.04.4 LTS with mawk 1.3.3 Nov 1996.

ranjancom2000 · March 22, 2018, 9:44am

thanks it resolved now.

ranjancom2000 · April 6, 2018, 11:22am

I used same code but it was not comparing

awk 'FNR == NR {T[$2] = $0; next} $0 = $0 "|" (T[$NF]?T[$NF]:"Notfound")' FS="," file1  file2

533,000A3 Server1_Parent                  000A3 00666   R1:13  RW RW RW  A..1.        0        0 RW  WD   Consistent
533,000A4 Server2_Silver                  000A4 007B6   R1:12  RW RW RW  A..1.        0        0 RW  WD   Consistent
533,000A5 Server2_Silver                  000A5 007B7   R1:12  RW RW RW  A..1.        0        0 RW  WD   Consistent
533,000A6 Server3_Parent                  000A6 007B8   R1:12  RW RW RW  A..1.        0        0 RW  WD   Consistent
533,000A7 Server3_Parent                  000A7 007B9   R1:12  RW RW RW  A..1.        0        0 RW  WD   Consistent

Device to compare File 1 with colume2

Device to compare File2 with colume5

ranjancom2000 · April 9, 2018, 6:10am

sorry to bump any help on this

Don_Cragun · April 9, 2018, 7:16am

Have you read the various posts in this thread?

The code you are using says that comma is your field separator. With your new input file format, there are only two fields (or columns) in your input and the 2nd field on the first line of the file is:

000A3 Server1_Parent                  000A3 00666   R1:13  RW RW RW  A..1.        0        0 RW  WD   Consistent

which is clearly never going to be interpreted as a 5 digit hexadecimal number string.

When you change your input file format, you have to adjust field separators and field numbers to match that file format!

ranjancom2000 · May 8, 2018, 1:27pm

I need to check the column 3 from file1 and compare it with Colume1 of file 1 and append and update the record if not found. But dont know how to update this if the file was not having any comma

awk 'FNR == NR {T[$2] = $0; next} $0 = $0 "|" (T[$NF]?T[$NF]:"Notfound")' FS="," file1  file2

File1

533 Server1 002D9 34526
533 Server1 002DA 34526
533 Server1 002DB 34526
533 Server1 002DC 34526
533 Server1 002DD 34526
533 Server1 002DE 34526
533 Server1 002DF 34526
533 Server1 002E1 34526
533 Server1 002E2 34526
533 Server1 002E3 34526
533 Server1 002E4 34526
533 Server1 002E5 34526
533 Server1 002E6 34526
533 Server1 002E7 34526

File2

002D9 RDFType:R1 RemoteDeviceSymmetrixName:001DD RemoteSymmetrixID:000296700555
002DA RDFType:R1 RemoteDeviceSymmetrixName:001DE RemoteSymmetrixID:000296700555
002DB RDFType:R1 RemoteDeviceSymmetrixName:001DF RemoteSymmetrixID:000296700555
002DC RDFType:R1 RemoteDeviceSymmetrixName:001E0 RemoteSymmetrixID:000296700555
002DD RDFType:R1 RemoteDeviceSymmetrixName:001E1 RemoteSymmetrixID:000296700555
002DE RDFType:R1 RemoteDeviceSymmetrixName:001E2 RemoteSymmetrixID:000296700555
002DF RDFType:R1 RemoteDeviceSymmetrixName:001E3 RemoteSymmetrixID:000296700555
002E0 RDFType:R1 RemoteDeviceSymmetrixName:001E4 RemoteSymmetrixID:000296700555
002E1 RDFType:R1 RemoteDeviceSymmetrixName:001E5 RemoteSymmetrixID:000296700555
002E2 RDFType:R1 RemoteDeviceSymmetrixName:001E6 RemoteSymmetrixID:000296700555
002E3 RDFType:R1 RemoteDeviceSymmetrixName:001E7 RemoteSymmetrixID:000296700555
002E4 RDFType:R1 RemoteDeviceSymmetrixName:001E8 RemoteSymmetrixID:000296700555
002E5 RDFType:R1 RemoteDeviceSymmetrixName:001E9 RemoteSymmetrixID:000296700555
002E6 RDFType:R1 RemoteDeviceSymmetrixName:001EA RemoteSymmetrixID:000296700555
002E7 RDFType:R1 RemoteDeviceSymmetrixName:001EB RemoteSymmetrixID:000296700555

Output required

533 Server1 002D9 34526 002D9 RDFType:R1 RemoteDeviceSymmetrixName:001DD RemoteSymmetrixID:000296700555
533 Server1 002DA 34526 002DA RDFType:R1 RemoteDeviceSymmetrixName:001DE RemoteSymmetrixID:000296700555
533 Server1 002DB 34526 002DB RDFType:R1 RemoteDeviceSymmetrixName:001DF RemoteSymmetrixID:000296700555
533 Server1 002DC 34526 002DC RDFType:R1 RemoteDeviceSymmetrixName:001E0 RemoteSymmetrixID:000296700555
533 Server1 002DD 34526 002DD RDFType:R1 RemoteDeviceSymmetrixName:001E1 RemoteSymmetrixID:000296700555
533 Server1 002DE 34526 002DE RDFType:R1 RemoteDeviceSymmetrixName:001E2 RemoteSymmetrixID:000296700555
533 Server1 002DF 34526 002DF RDFType:R1 RemoteDeviceSymmetrixName:001E3 RemoteSymmetrixID:000296700555
Notfound 002E0 RDFType:R1 RemoteDeviceSymmetrixName:001E4 RemoteSymmetrixID:000296700555
533 Server1 002E1 34526 002E1 RDFType:R1 RemoteDeviceSymmetrixName:001E5 RemoteSymmetrixID:000296700555
533 Server1 002E2 34526 002E2 RDFType:R1 RemoteDeviceSymmetrixName:001E6 RemoteSymmetrixID:000296700555
533 Server1 002E3 34526 002E3 RDFType:R1 RemoteDeviceSymmetrixName:001E7 RemoteSymmetrixID:000296700555
533 Server1 002E4 34526 002E4 RDFType:R1 RemoteDeviceSymmetrixName:001E8 RemoteSymmetrixID:000296700555
533 Server1 002E5 34526 002E5 RDFType:R1 RemoteDeviceSymmetrixName:001E9 RemoteSymmetrixID:000296700555
533 Server1 002E6 34526 002E6 RDFType:R1 RemoteDeviceSymmetrixName:001EA RemoteSymmetrixID:000296700555
533 Server1 002E7 34526 002E7 RDFType:R1 RemoteDeviceSymmetrixName:001EB RemoteSymmetrixID:000296700555

rdrtx1 · May 8, 2018, 1:52pm

awk 'FNR == NR {T[$3] = $0; next} {print (length(T[$1]) ? T[$1] : "Notfound ") $0}' file1 file2

ranjancom2000 · May 8, 2018, 2:07pm

This one is working dont know how it compare the file. I tried to put

between append not able to do. How i can add that

---------- Post updated at 01:07 PM ---------- Previous update was at 01:04 PM ----------

I Can able to solve that

awk 'FNR == NR {T[$3] = $0; next} {print (length(T[$1]) ? T[$1] " | ": "Notfound |") $0}' 1.txt 2.txt