Vlookup using Ask from specific column from two files


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Vlookup using Ask from specific column from two files
# 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 or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

How do I extract specific column in multiple csv files?

file1: Name,Threshold,Curr Samples,Curr Error%,Curr ART GETHome,100,21601,0.00%,47 GETregistry,100,21592,0.00%,13 GEThomeLayout,100,30466,0.00%,17 file2: Name,Threshold,Curr Samples,Curr Error%,Curr ART GETHome,100,21601,0.00%,33 GETregistry,100,21592,0.00%,22... (6 Replies)
Discussion started by: Raghuram717
6 Replies

2. UNIX for Beginners Questions & Answers

Vlookup on 2 files - inserting vlookup command on another command

Hello, i am trying to print group name column(etc/group) on script (etc/passwd) since group name is not listed on etc/passwd columns. Im trying to do a vlookup. but i cant figure out how i can insert the vlookup command FNR==NR inside the print out command or the output. I also tried exporting... (2 Replies)
Discussion started by: joonisio
2 Replies

3. Shell Programming and Scripting

Vlookup using Ask from specific column shell script

Input file1 frame1,dummy,server1, 00C1C N/A RDF1+TDEV RW 51789 frame1,dummy,server1, 00C1D N/A RDF1+TDEV RW 51789 frame1,dummy,server1, 00C1E N/A RDF1+TDEV RW 51789 frame1,dummy,server1, 00C1F N/A RDF1+TDEV RW 51789 frame1,dummy,server1, 00C20 N/A RDF1+TDEV RW 51789 frame1,dummy,server1,... (10 Replies)
Discussion started by: ranjancom2000
10 Replies

4. Shell Programming and Scripting

Overwrite specific column in xml file with the specific column from adjacent line

I have an xml file dumped from rrd file, that I want to "patch" so the xml file doesn't contain any blank hole in the resulting graph of the rrd file. Here is the file. <!-- 2015-10-12 14:00:00 WIB / 1444633200 --> <row><v> 4.0419731265e+07 </v><v> 4.5045912770e+06... (2 Replies)
Discussion started by: rk4k
2 Replies

5. Shell Programming and Scripting

awk or sed to find specific column from different files

Hi everybody, I have a folder with many files: Files with 8 columns: X 123 A B C D E F And files with 7 columns: X1234 A B C D E F I am trying to find a way to extract the 5th column when the files have eight columns, or the 4th column when the files have... (3 Replies)
Discussion started by: Tzole
3 Replies

6. Shell Programming and Scripting

Concatenating many files based on a specific column contents

Dear all, I have many files(.csv) in a directory. I want to concatenate the files which have similar entries in a particular column and save into a new file like result_datetime.csv etc. One example file is like below. Sno,Step,Data1,Data2,Data3 etc. 1,0,2,3,4 2,1,3,4,5 3,2,0,1,1 ... (4 Replies)
Discussion started by: ks_reddy
4 Replies

7. Shell Programming and Scripting

Divide data with specific column values into separate files

hello! i need a little help from you :) ... i need to split a file into separate files depending on two conditions using scripting. The file has no delimiters. The conditions are col 17 = "P" and col 81 = "*", this will go to one output file; col 17 = "R" and col 81 = " ". Here is an example. ... (3 Replies)
Discussion started by: chanclitas
3 Replies

8. Shell Programming and Scripting

averaging specific column of multiple files

Hi all, I'm needing help again on scripting. I have weekly files with 3 columns, and I need to do monthly averaging on the values on column 3, the file naming convention is as follows: 20000105.u- 2000:year 01:month 05:day 20000112.u 20000119.u 20000126.u 20000202.u 20020209.u I need to... (15 Replies)
Discussion started by: ida1215
15 Replies

9. Shell Programming and Scripting

vlookup files

hi frnds i have 2 files. 1st is dddd and 2nd is ssss ==> dddd <==: 1,charit 2,gilhotra ==> ssss <==: 1,sajan 2,doda 3,hello and i want o/p ...mean join and vlookup both files sajan,charit (4 Replies)
Discussion started by: dodasajan
4 Replies

10. Shell Programming and Scripting

Concatenate two files after a specific column

Hi I have two files, one is 1.6 GB. I would like to add one extra column of information to the large file at a specific location. After its 2nd column. For example: File 1 has two columns more than 1000 rows like this MM009987 1 File 2 looks like this MM00098 MM00076 3 4 2 4 2 1... (1 Reply)
Discussion started by: sogi
1 Replies
Login or Register to Ask a Question