Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Vlookup using Ask from specific column from two files

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Vlookup using Ask from specific column from two files
# 8  
Old 03-21-2018
THis is original input file i need check the WWN from file1 and append the line if the WWn found. If not mark has notfound

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


New Input file was having like this

Code:
10.62.75.120, portIndex: 247 portName: slot12 port23,20:f1:0a:25:b5:03:02:90
10.62.75.120, portIndex: 247 portName: slot12 port23,20:50:00:2a:6a:00:b8:00 
10.62.75.120, portIndex: 247 portName: slot12 port23,20:50:00:2a:6a:00:b8:00 
10.62.75.120, portIndex: 247 portName: slot12 port23,21:00:00:24:ff:8b:e1:fe
10.62.75.120, portIndex: 247 portName: slot12 port23,20:50:00:2a:6a:00:b8:00

# 9  
Old 03-21-2018
No surprise if you change the structure of your files. Get rid of the FS=" " and report back.
# 10  
Old 03-22-2018
Quote:
Originally Posted by RudiC
No surprise if you change the structure of your files. Get rid of the FS=" " and report back.
I ran the script by removing FS=" " same output for all record it was updating not found.
# 11  
Old 03-22-2018
Code:
awk 'FNR == NR {T[$2] = $0; next} $0 = $0 "|" (T[$NF]?T[$NF]:"Notfound")' FS="," file1  file2
10.62.75.120, portIndex: 247 portName: slot12 port23,20:f1:0a:25:b5:03:02:90|alias:server1_00,20:f1:0a:25:b5:03:02:90
10.62.75.120, portIndex: 247 portName: slot12 port23,20:50:00:2a:6a:00:b8:00 |Notfound
10.62.75.120, portIndex: 247 portName: slot12 port23,20:50:00:2a:6a:00:b8:00 |Notfound
10.62.75.120, portIndex: 247 portName: slot12 port23,21:00:00:24:ff:8b:e1:fe|alias:server5_00,21:00:00:24:ff:8b:e1:fe
10.62.75.120, portIndex: 247 portName: slot12 port23,20:50:00:2a:6a:00:b8:00|Notfound

on Ubuntu 16.04.4 LTS with mawk 1.3.3 Nov 1996.
# 12  
Old 03-22-2018
thanks it resolved now.
# 13  
Old 04-06-2018
I used same code but it was not comparing

Code:
awk 'FNR == NR {T[$2] = $0; next} $0 = $0 "|" (T[$NF]?T[$NF]:"Notfound")' FS="," file1  file2

Code:
533,000A3 Server1_Parent                  000A3 00666   R1:13  RW RW RW  A..1.        0        0 RW  WD   Consistent
533,000A4 Server2_Silver                  000A4 007B6   R1:12  RW RW RW  A..1.        0        0 RW  WD   Consistent
533,000A5 Server2_Silver                  000A5 007B7   R1:12  RW RW RW  A..1.        0        0 RW  WD   Consistent
533,000A6 Server3_Parent                  000A6 007B8   R1:12  RW RW RW  A..1.        0        0 RW  WD   Consistent
533,000A7 Server3_Parent                  000A7 007B9   R1:12  RW RW RW  A..1.        0        0 RW  WD   Consistent

Device to compare File 1 with colume2
Quote:
000A3
000A2
000A3
000A4
Device to compare File2 with colume5
Code:
00666
000A2
007B7
000A4
007B9

Last edited by ranjancom2000; 04-06-2018 at 03:32 PM..
# 14  
Old 04-09-2018
sorry to bump any help on this
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