Vlookup using Ask from specific column shell script

Shell Programming and Scripting
1

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, 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

 00C1C
 00C24

Output required with Preffix Added (Snapfound)

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

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

For your example:-

awk 'NR==FNR{A[$1];next}$2 in A{$2="sanpfound," $3}1' file2 file1
3

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

Here is what I get. Note that the sequence of input files matters:-

$ 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
1 Like
5

Hi Yoda,

Thanks it working great. It it possible to add notfound device as "dummy" before the device.

6

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

Hi Yoda,

Thanks i used sed to rename the blank space

8

Please show us the code you used to solve your problem. We want everyone who reads your thread in the future to be able to learn from your experience.

9

This is step i used to resolve this issue

awk 'NR==FNR{A[$1];next}$2 in A{$2 = "sanpfound," $2}1' file2 file1 > file-2
cat file-2 |awk '$2="dummy,"$2' > file-3
cat file-3 |sed -e "s/dummy,snapfound/snapfound/" > file-4
10

Note that if you put "sanpfound," in field 2 and then add in "dummy,"; you'll never find "dummy,snapfound".

If what you want is "snapfound" (which is not what you said you wanted in post #1 in this thread), you need to use "snapfound" in your first awk command.

You might want to consider the following single awk script as a possible replacement for your two awk scripts and a sed script:

awk '
FNR == NR {
	a[$1]
	next
}
{	sub(/,[^,]*/, "", $1)
	$2 = (($2 in a) ? "snapfound" : "dummy") "," $2
}
1' file2 file1 > file-4

but, of course, this assumes that your input file does already contain a ",dummy," that you want to get rid of, and that you want "snapfound," or "dummy," added before your device.

If file2 contains:

 00C1C
 00C24

(with [as shown in post #1] or without the leading <space> on each line) and file1 contains:

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

(as shown in post #1), the above code will store:

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

as requested in post #1 in file-4 except that:

  1. "dummy," is added before the device ID on lines with device IDs that do not appear in file2 , and
  2. "snapfound," is added before the device ID instead of "sanpfound," on lines with device IDs that do appear in file2 .

As always, if someone wants to try this on a Solaris/SunOS system, change awk to /usr/xpg4/bin/awk or nawk .

I hope this helps,
Don

1 Like
11

HI Don,

Yes it was typo error. I will try your script also