Unix/Linux Go Back    


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

Shell Programming and Scripting


Reply    
 
Thread Tools Search this Thread Display Modes
    #1  
Old Unix and Linux 12-04-2017   -   Original Discussion by ranjancom2000
ranjancom2000's Unix or Linux Image
ranjancom2000 ranjancom2000 is offline
Registered User
 
Join Date: Apr 2011
Last Activity: 18 April 2018, 10:00 AM EDT
Posts: 130
Thanks: 21
Thanked 1 Time in 1 Post
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 Unix and Linux 12-04-2017   -   Original Discussion by ranjancom2000
RudiC's Unix or Linux Image
RudiC RudiC is offline Forum Staff  
Moderator
 
Join Date: Jul 2012
Last Activity: 24 April 2018, 1:13 AM EDT
Location: Aachen, Germany
Posts: 12,512
Thanks: 401
Thanked 3,879 Times in 3,566 Posts
First thing that caught my eyes is there's no field 10 in any file.
Sponsored Links
    #3  
Old Unix and Linux 12-04-2017   -   Original Discussion by ranjancom2000
ranjancom2000's Unix or Linux Image
ranjancom2000 ranjancom2000 is offline
Registered User
 
Join Date: Apr 2011
Last Activity: 18 April 2018, 10:00 AM EDT
Posts: 130
Thanks: 21
Thanked 1 Time in 1 Post
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 Unix and Linux 12-04-2017   -   Original Discussion by ranjancom2000
RudiC's Unix or Linux Image
RudiC RudiC is offline Forum Staff  
Moderator
 
Join Date: Jul 2012
Last Activity: 24 April 2018, 1:13 AM EDT
Location: Aachen, Germany
Posts: 12,512
Thanks: 401
Thanked 3,879 Times in 3,566 Posts
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 Unix and Linux 12-04-2017   -   Original Discussion by ranjancom2000
ranjancom2000's Unix or Linux Image
ranjancom2000 ranjancom2000 is offline
Registered User
 
Join Date: Apr 2011
Last Activity: 18 April 2018, 10:00 AM EDT
Posts: 130
Thanks: 21
Thanked 1 Time in 1 Post
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 Unix and Linux 03-21-2018   -   Original Discussion by ranjancom2000
ranjancom2000's Unix or Linux Image
ranjancom2000 ranjancom2000 is offline
Registered User
 
Join Date: Apr 2011
Last Activity: 18 April 2018, 10:00 AM EDT
Posts: 130
Thanks: 21
Thanked 1 Time in 1 Post
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 Unix and Linux 03-21-2018   -   Original Discussion by ranjancom2000
RudiC's Unix or Linux Image
RudiC RudiC is offline Forum Staff  
Moderator
 
Join Date: Jul 2012
Last Activity: 24 April 2018, 1:13 AM EDT
Location: Aachen, Germany
Posts: 12,512
Thanks: 401
Thanked 3,879 Times in 3,566 Posts
What input has changed? What should the output look like?
Give he whole picture!
Sponsored Links
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Linux 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 01:59 AM.