Vlookup using awk without exact match for two colum input


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Vlookup using awk without exact match for two colum input
# 1  
Old 04-16-2018
Vlookup using awk without exact match for two colum input

Source file 1

Code:
335 R1-snapfound 0098F RDFType:R1 R2-Dev R2-snapfound ,010C0 RemoteSymmetrixID:345
335 R1-snapfound 00990 RDFType:R1 R2-Dev R2-snapfound ,010C1 RemoteSymmetrixID:345
335 R1-snapfound 009C0 RDFType:R1 R2-Dev R2-snapfound ,009C1 RemoteSymmetrixID:345
335 R1-snapfound 009C1 RDFType:R1 R2-Dev R2-snapfound ,009C2 RemoteSymmetrixID:345
335 R1-snapfound 009C2 RDFType:R1 R2-Dev R2-snapfound ,009C3 RemoteSymmetrixID:345

Source file 2

Code:
335 server1_silver 0098F
335 server1_silver 00990
335 server1_silver 009C0
335 server1_silver 00ab4
335 server1_silver 00ab5
335 server1_silver 00ab6


It need to check for third column with each file and append second and third column in output file

Output required

Code:
335 server1_silver 0098F R1-snapfound 0098F RDFType:R1 R2-Dev R2-snapfound ,010C0 RemoteSymmetrixID:345
335 server1_silver 00990 R1-snapfound 00990 RDFType:R1 R2-Dev R2-snapfound ,010C1 RemoteSymmetrixID:345
335 server1_silver 009C0 R1-snapfound 009C0 RDFType:R1 R2-Dev R2-snapfound ,009C1 RemoteSymmetrixID:345
335 R1-snapfound 009C1 RDFType:R1 R2-Dev R2-snapfound ,009C2 RemoteSymmetrixID:345
335 R1-snapfound 009C2 RDFType:R1 R2-Dev R2-snapfound ,009C3 RemoteSymmetrixID:345

# 2  
Old 04-16-2018
Code:
 awk 'FNR==NR {f2[$3]=$0;next} {print (($3 in f2)?f2[$3] OFS:"") $0}' file2 file1

This User Gave Thanks to vgersh99 For This Post:
# 3  
Old 04-16-2018
Quote:
Originally Posted by vgersh99
Code:
 awk 'FNR==NR {f2[$3]=$0;next} {print (($3 in f2)?f2[$3] OFS:"") $0}' file2 file1


this is working great. How to run if i want to use without case sensitive
# 4  
Old 04-16-2018
Try:
Code:
 awk 'FNR==NR {f2[tolower($3)]=$0;next} {print ((tolower($3) in f2)?f2[tolower($3)] OFS:"") $0}' file2 file1

This User Gave Thanks to Don Cragun For This Post:
# 5  
Old 04-17-2018
Quote:
Originally Posted by Don Cragun
Try:
Code:
 awk 'FNR==NR {f2[tolower($3)]=$0;next} {print ((tolower($3) in f2)?f2[tolower($3)] OFS:"") $0}' file2 file1


Great it was working
# 6  
Old 04-17-2018
As a non-existing element of f2 will yield an empty string if referenced, you might simplify above to
Code:
awk 'FNR==NR {f2[tolower($3)]=$0 OFS;next} {print f2[tolower($3)] $0}' file2 file1
335 server1_silver 0098F 335 R1-snapfound 0098F RDFType:R1 R2-Dev R2-snapfound ,010C0 RemoteSymmetrixID:345
335 server1_silver 00990 335 R1-snapfound 00990 RDFType:R1 R2-Dev R2-snapfound ,010C1 RemoteSymmetrixID:345
335 server1_silver 009C0 335 R1-snapfound 009C0 RDFType:R1 R2-Dev R2-snapfound ,009C1 RemoteSymmetrixID:345
335 R1-snapfound 009C1 RDFType:R1 R2-Dev R2-snapfound ,009C2 RemoteSymmetrixID:345
335 R1-snapfound 009C2 RDFType:R1 R2-Dev R2-snapfound ,009C3 RemoteSymmetrixID:345

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Vlookup using awk without exact match

Code used to find the server from cloum 3 and update needtotakesnap Output came from above command awk 'NR==FNR{A;next}$3 in A{$3 = "needtotakesnap " $3}1' /home/Others/active-server.txt /home/Others/all-server |grep server1 879 dummy server1_217_silver dummy 00870 TDEV 2071575 831 Tier1... (3 Replies)
Discussion started by: ranjancom2000
3 Replies

2. Shell Programming and Scripting

awk to update file based on partial match in field1 and exact match in field2

I am trying to create a cronjob that will run on startup that will look at a list.txt file to see if there is a later version of a database using database.txt as the source. The matching lines are written to output. $1 in database.txt will be in list.txt as a partial match. $2 of database.txt... (2 Replies)
Discussion started by: cmccabe
2 Replies

3. Shell Programming and Scripting

Match exact Variable in Awk

I want to match exact string stored in Variable INO and print next two lines. I have written below code which partially matches: awk '/\'$VAR'/{getline; getline; print}' Will be thankful , if somebody can help in to resolve this problem. Please view this code tag video for how to use... (9 Replies)
Discussion started by: nitj
9 Replies

4. Shell Programming and Scripting

Exact match and #

Hi friends, i am using the following grep command for exact word match: >echo "sachin#tendulkar" | grep -iw "sachin" output: sachin#tendulkar as we can see in the above example that its throwinng the exact match(which is not the case as the keyword is sachin and string is... (6 Replies)
Discussion started by: neelmani
6 Replies

5. Shell Programming and Scripting

How to find lines that match exact input and count?

I am writing a package manager in BASH and I would like a small snippet of code that finds lines that match exact input and count them. For example, my file contains: xyz xyz-lib2.0+ xyz-lib2.0 xyz-lib1.5 and "grep -c xyz" returns 4. The current function is: # $1 is the package name.... (3 Replies)
Discussion started by: cooprocks123e
3 Replies

6. Shell Programming and Scripting

exact string match ; search and print match

I am trying to match a pattern exactly in a shell script. I have tried two methods awk '/\<mpath${CURR_MP}\>/{print $1 $2}' multipath perl -ne '/\bmpath${CURR_MP}\b/ and print' /var/tmp/multipath Both these methods require that I use the escape character. I am guessing that is why... (8 Replies)
Discussion started by: bash_in_my_head
8 Replies

7. Shell Programming and Scripting

exact match in Perl

Hi By using select clause I'm trying to pull out the rows to a variable. If the variable has 0 row(s) selected then i'm printing some text message else printing some other text message if($xyz =~ m/0 row/) { print "0 rows "; } else { print " There are rows"; } By my problem... (4 Replies)
Discussion started by: pdreddy34
4 Replies

8. Shell Programming and Scripting

Awk+Grep Input file needs to match a column and print the entire line

I'm having problems since few days ago, and i'm not able to make it works with a simple awk+grep script (or other way to do this). For example, i have a input file1.txt: cat inputfile1.txt 218299910417 1172051195 1172070231 1172073514 1183135117 1183135118 1183135119 1281440202 ... (3 Replies)
Discussion started by: poliver
3 Replies

9. UNIX for Advanced & Expert Users

can awk built-in "match" be exact??

hello everybody, as explained in the title, here is what I want: str1="name1 name2 name3" str2="name1" str3="name" I know that match(str1,str2) will return 1, but I want that match(str1,str3) returns 0 (when it also returns 1...) Is there a way to get that exact matching process done... (6 Replies)
Discussion started by: farphe
6 Replies

10. Shell Programming and Scripting

add multiple colum using awk

hi i need help in my script i have a file a.txt 3,4,5,13 6,7,8,45 9,0,1,67 i want to add 2nd and 3rd colum like o/p will be by adding all values of colum2(4+7+0) and colum 3(5+8+11) o/p: colum 2: 11 colum 3: 14 (6 Replies)
Discussion started by: anish19
6 Replies
Login or Register to Ask a Question