Vlookup using Ask from specific column from two files

Tags
awk, shell scripts, solved

 
Thread Tools Search this Thread
# 1  
Old 12-04-2017
Vlookup using Ask from specific column from two files

File1

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

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

Code:
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
# 2  
Old 12-04-2017
First thing that caught my eyes is there's no field 10 in any file.
# 3  
Old 12-04-2017
Hi RudiC,

I dont have much info
Code:
awk 'FNR == NR

Dont know how to Vlookup

take the third ROW from File1
And check with ROW fifth or sixth from File2
# 4  
Old 12-04-2017
When writing ROW you mean column or field, don't you? How about
Code:
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
.
.
.

# 5  
Old 12-04-2017
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
# 6  
Old 03-21-2018
Is there any number i need to change NR {T[$2]

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



Quote:
10.62.75.120, portIndex: 247 portName: slot12 port23,20:50:00:2a:6a:00:b8:00
# 7  
Old 03-21-2018
What input has changed? What should the output look like?
Give he whole picture!

|
Thread Tools Search this Thread
Search this Thread:
Advanced Search

More UNIX and Linux Forum Topics You Might Find Helpful
Vlookup using awk non similar files ranjancom2000 Shell Programming and Scripting 6 04-18-2018 10:59 AM
Vlookup using Ask from specific column shell script ranjancom2000 Shell Programming and Scripting 10 11-29-2017 06:47 AM
Overwrite specific column in xml file with the specific column from adjacent line rk4k Shell Programming and Scripting 2 10-20-2015 06:00 AM
Sum values of specific column in multiple files, considering ranges defined in another file Bastami Shell Programming and Scripting 3 12-09-2014 01:36 PM
awk script to perform an action similar to vlookup between two csv files in UNIX Prit Siv Shell Programming and Scripting 9 09-04-2014 04:25 PM
How to print multiple specific column after a specific word? marvinandco Shell Programming and Scripting 2 03-12-2014 09:14 AM
awk or sed to find specific column from different files Tzole Shell Programming and Scripting 3 01-29-2013 10:07 AM
Concatenating many files based on a specific column contents ks_reddy Shell Programming and Scripting 4 06-13-2012 05:18 AM
Divide data with specific column values into separate files chanclitas Shell Programming and Scripting 3 04-24-2012 09:14 PM
averaging specific column of multiple files ida1215 Shell Programming and Scripting 15 12-28-2011 01:20 PM
vlookup files dodasajan Shell Programming and Scripting 4 09-08-2011 05:16 AM
merge files like VLOOKUP godzilla07 Shell Programming and Scripting 3 11-30-2010 08:37 PM
Assigning a specific format to a specific column in a text file using awk and printf goodbenito Shell Programming and Scripting 2 04-30-2010 10:25 AM
Insert a text from a specific row into a specific column using SED or AWK Issemael Shell Programming and Scripting 5 07-14-2009 12:12 PM
Concatenate two files after a specific column sogi Shell Programming and Scripting 1 05-22-2009 12:10 AM