Vlookup using Ask from specific column from two files

Tags
awk, shell scripts, solved

Login to Reply

 
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!
Login to Reply

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

Similar Threads More UNIX and Linux Forum Topics You Might Find Helpful
Thread Thread Starter Forum Replies Last Post
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
awk or sed to find specific column from different files Tzole Shell Programming and Scripting 3 01-29-2013 10:07 AM
Count specific characters at specific column positions thienxho Shell Programming and Scripting 2 12-04-2012 01:58 PM
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
All times are GMT -4. The time now is 04:54 PM.

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





Not a Forum Member?
Forgot Password?