Shell Programming and Scripting

BSD, Linux, and UNIX shell scripting — Post awk, bash, csh, ksh, perl, php, python, sed, sh, shell scripts, and other shell scripting languages questions here.

Vlookup using Ask from specific column from two files


👤 Login to reply

    #1  
Old 12-04-2017
ranjancom2000 ranjancom2000 is offline
Registered User
 
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
Sponsored Links
    #2  
Old 12-04-2017
RudiC RudiC is offline Forum Staff  
Moderator
 
First thing that caught my eyes is there's no field 10 in any file.
Sponsored Links
    #3  
Old 12-04-2017
ranjancom2000 ranjancom2000 is offline
Registered User
 
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
RudiC RudiC is offline Forum Staff  
Moderator
 
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
.
.
.

Sponsored Links
    #5  
Old 12-04-2017
ranjancom2000 ranjancom2000 is offline
Registered User
 
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
Sponsored Links
    #6  
Old 03-21-2018
ranjancom2000 ranjancom2000 is offline
Registered User
 
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
Sponsored Links
    #7  
Old 03-21-2018
RudiC RudiC is offline Forum Staff  
Moderator
 
What input has changed? What should the output look like?
Give he whole picture!
Sponsored Links
👤 Login to reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

More UNIX and Linux Forum Topics You Might Find Helpful
Thread Thread Starter Forum Replies Last Post
Vlookup using Ask from specific column shell script ranjancom2000 Shell Programming and Scripting 10 11-29-2017 05:47 AM
Overwrite specific column in xml file with the specific column from adjacent line rk4k Shell Programming and Scripting 2 10-20-2015 05:00 AM
vlookup files dodasajan Shell Programming and Scripting 4 09-08-2011 04:16 AM
merge files like VLOOKUP godzilla07 Shell Programming and Scripting 3 11-30-2010 07:37 PM
Concatenate two files after a specific column sogi Shell Programming and Scripting 1 05-21-2009 11:10 PM



All times are GMT -4. The time now is 08:52 AM.

Unix & Linux Forums Content Copyright©1993-2018. All Rights Reserved.
×
UNIX.COM Login
Username:
Password:  
Show Password





Not a Forum Member?
Forgot Password?