Vlookup using Ask from specific column shell script


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Vlookup using Ask from specific column shell script
# 1  
Old 11-27-2017
Vlookup using Ask from specific column shell script

Input file1

Code:
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, 00C21 N/A RDF1+TDEV RW 51789
frame1,dummy,server1, 00C22 N/A RDF1+TDEV RW 51789
frame1,dummy,server1, 00C23 N/A RDF1+TDEV RW 51789
frame1,dummy,server1, 00C24 N/A RDF1+TDEV RW 51789
frame1,dummy,server1, 00C25 N/A RDF1+TDEV RW 51789
frame1,dummy,server1, 00C26 N/A RDF1+TDEV RW 51789
frame1,dummy,server1, 00C27 N/A RDF1+TDEV RW 51789
frame1,dummy,server1, 00C28 N/A RDF1+TDEV RW 51789

File 2 to compare
Code:
 00C1C
 00C24



Output required with Preffix Added (Snapfound)



Code:

frame1,server1, sanpfound,00C1C N/A RDF1+TDEV RW 51789
frame1,server1, 00C1D N/A RDF1+TDEV RW 51789
frame1,server1, 00C1E N/A RDF1+TDEV RW 51789
frame1,server1, 00C1F N/A RDF1+TDEV RW 51789
frame1,server1, 00C20 N/A RDF1+TDEV RW 51789
frame1,server1, 00C21 N/A RDF1+TDEV RW 51789
frame1,server1, 00C22 N/A RDF1+TDEV RW 51789
frame1,server1, 00C23 N/A RDF1+TDEV RW 51789
frame1,server1, sanpfound,00C24 N/A RDF1+TDEV RW 51789
frame1,server1, 00C25 N/A RDF1+TDEV RW 51789
frame1,server1, 00C26 N/A RDF1+TDEV RW 51789
frame1,server1, 00C27 N/A RDF1+TDEV RW 51789
frame1,server1, 00C28 N/A RDF1+TDEV RW 51789


I am try to use this script but having some issue to filter the column

Code:
awk 'FNR==NR{a[$1]=$2;next} {$2=($3) in a?a($3)]:"snapfound"} 1' OFS="," FS=" "  Input_file2  FS=","   Input_file1


Last edited by ranjancom2000; 11-27-2017 at 01:35 PM..
# 2  
Old 11-27-2017
For your example:-
Code:
awk 'NR==FNR{A[$1];next}$2 in A{$2="sanpfound," $3}1' file2 file1

# 3  
Old 11-27-2017
Hi Yoda,

It was not working. It was removing all the dev and update all row with "sanpfound" I need to check the device and update only the row which has device. And i have change the input file it has one more column i have missed to add.
# 4  
Old 11-27-2017
Here is what I get. Note that the sequence of input files matters:-
Code:
$ cat 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, 00C21 N/A RDF1+TDEV RW 51789
frame1,dummy,server1, 00C22 N/A RDF1+TDEV RW 51789
frame1,dummy,server1, 00C23 N/A RDF1+TDEV RW 51789
frame1,dummy,server1, 00C24 N/A RDF1+TDEV RW 51789
frame1,dummy,server1, 00C25 N/A RDF1+TDEV RW 51789
frame1,dummy,server1, 00C26 N/A RDF1+TDEV RW 51789
frame1,dummy,server1, 00C27 N/A RDF1+TDEV RW 51789
frame1,dummy,server1, 00C28 N/A RDF1+TDEV RW 51789

$ cat file2
 00C1C
 00C24

$ awk 'NR==FNR{A[$1];next}$2 in A{$2 = "sanpfound," $2}1' file2 file1
frame1,dummy,server1, sanpfound,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, 00C21 N/A RDF1+TDEV RW 51789
frame1,dummy,server1, 00C22 N/A RDF1+TDEV RW 51789
frame1,dummy,server1, 00C23 N/A RDF1+TDEV RW 51789
frame1,dummy,server1, sanpfound,00C24 N/A RDF1+TDEV RW 51789
frame1,dummy,server1, 00C25 N/A RDF1+TDEV RW 51789
frame1,dummy,server1, 00C26 N/A RDF1+TDEV RW 51789
frame1,dummy,server1, 00C27 N/A RDF1+TDEV RW 51789
frame1,dummy,server1, 00C28 N/A RDF1+TDEV RW 51789

This User Gave Thanks to Yoda For This Post:
# 5  
Old 11-28-2017
Hi Yoda,

Thanks it working great. It it possible to add notfound device as "dummy" before the device.
# 6  
Old 11-28-2017
Why don't you try building on the code Yoda provided and see if you can get the output you want on your own?

If you get stuck, show us what you've tried and we'll help you finish it.
# 7  
Old 11-28-2017
Hi Yoda,

Thanks i used sed to rename the blank space
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Using awk to change a specific column and in a specific row

I am trying to change the number in bold to 2400 01,000300032,193631306,190619,0640,1,80,,2/ 02,193631306,000300032,1,190618,0640,CAD,2/ I'm not sure if sed or awk is the answer. I was going to use sed and do a character count up to that point, but that column directly before 0640 might... (8 Replies)
Discussion started by: juggernautjoee
8 Replies

2. UNIX for Beginners Questions & Answers

How to insert data into black column( Secound Column ) in excel (.XLSX) file using shell script?

Source Code of the original script is down below please run the script and try to solve this problem this is my data and I want it column wise 2019-03-20 13:00:00:000 2019-03-20 15:00:00:000 1 Operating System LAB 0 1 1 1 1 1 1 1 1 1 0 1 (5 Replies)
Discussion started by: Shubham1182
5 Replies

3. Shell Programming and Scripting

Need awk or Shell script to compare Column-1 of two different CSV files and print if column-1 matche

Example: I have files in below format file 1: zxc,133,joe@example.com cst,222,xyz@example1.com File 2 Contains: hxd hcd jws zxc cst File 1 has 50000 lines and file 2 has around 30000 lines : Expected Output has to be : hxd hcd jws (5 Replies)
Discussion started by: TestPractice
5 Replies

4. Shell Programming and Scripting

Vlookup using Ask from specific column from two files

File1 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... (17 Replies)
Discussion started by: ranjancom2000
17 Replies

5. 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

6. Shell Programming and Scripting

Shell Scripting Vlookup

I am developing shell script on Linux OS and I have two files.Data in each file is like : File1 : A B C E F G X Y Z File 2: A C 12 E G 22 X Z 41 I need if first and third column entries ( $1 & $3) of File1 in same row matches with first & second column... (3 Replies)
Discussion started by: suneet17
3 Replies

7. Shell Programming and Scripting

KSH Script to Execute command from specific column in file

Hi Unix Experts,Team I have a file (say comand_file.prm), The file has a command specified in column 6 (say "./execute_script.sh"). I want to execute this command in my script. I am writing a KSH script to achieve this. Could you please assist me with this. (6 Replies)
Discussion started by: Jeevanm
6 Replies

8. Shell Programming and Scripting

Assigning a specific format to a specific column in a text file using awk and printf

Hi, I have the following text file: 8 T1mapping_flip02 ok 128 108 30 1 665000-000008-000001.dcm 9 T1mapping_flip05 ok 128 108 30 1 665000-000009-000001.dcm 10 T1mapping_flip10 ok 128 108 30 1 665000-000010-000001.dcm 11 T1mapping_flip15 ok 128 108 30... (2 Replies)
Discussion started by: goodbenito
2 Replies

9. Shell Programming and Scripting

Creating Report file with 'vlookup' kind of structure in shell

Hi, I have some files in the following structure. File_a.txt Field_1 Pass Field_2 Pass Field_3 Pass File_b.txt Field_1 Pass Field_2 Fail Field_3 Pass File_c.txt Field_1 Fail Field_2 Pass Field_3 Pass (2 Replies)
Discussion started by: vikaskm
2 Replies

10. Shell Programming and Scripting

Insert a text from a specific row into a specific column using SED or AWK

Hi, I am having trouble converting a text file. I have been working for this whole day now, still i couldn't make it. Here is how the text file looks: _______________________________________________________ DEVICE STATUS INFORMATION FOR LOCATION 1: OPER STATES: Disabled E:Enabled ... (5 Replies)
Discussion started by: Issemael
5 Replies
Login or Register to Ask a Question

Featured Tech Videos