Help with AWK - Compare a field in a file to lookup file and substitute if only a match | Unix Linux Forums | UNIX for Dummies Questions & Answers

  Go Back    


UNIX for Dummies Questions & Answers If you're not sure where to post a UNIX or Linux question, post it here. All UNIX and Linux newbies welcome !!

Help with AWK - Compare a field in a file to lookup file and substitute if only a match

UNIX for Dummies Questions & Answers


Closed Thread    
 
Thread Tools Search this Thread Display Modes
    #1  
Old 10-08-2012
venalla_shine venalla_shine is offline
Registered User
 
Join Date: Oct 2012
Last Activity: 8 October 2012, 3:34 PM EDT
Posts: 2
Thanks: 1
Thanked 0 Times in 0 Posts
Help with AWK - Compare a field in a file to lookup file and substitute if only a match

I have the below 2 files:

1) Third field from file1.txt should be compared to the first field of lookup.txt.
2) If match found then third field, file1.txt should be substituted with the second field from lookup.txt.
3)Else just print the line from file1.txt.

File1.txt:

Code:
|ABCD|1000|Jill|Smith|MD|
|ABCD|1001|Jack|Smith|MD|
|ABCD|1002|Mary|Smith|MD|
|ABCD|1003|Jill|Smith|MD|
|ABCD|AK10GFM|George|Foreman|MD|
|ABCD|2000|Akil|Roman|MD|

lookup.txt :

Code:
1000|NJ12JSM
1001|NJ34JSM
1002|NJ45MSM
1003|NJ12JSM
2000|PA10ARM

Output file:

Code:
|ABCD|NJ12JSM|Jill|Smith|MD|
|ABCD|NJ45MSM|Mary|Smith|MD|
|ABCD|NJ12JSM|Jill|Smith|MD|
|ABCD|AK10GFM|George|Foreman|MD|
|ABCD|PA10ARM|Akil|Roman|MD|

I tried giving this command, it returns only the matching values from File1.txt. Can someone help?

Code:
awk 'BEGIN {FS=OFS="|"} NR==FNR{a[$1]=$2;next} $3 in a{print $0}' lookup.txt file1.txt

---------- Post updated at 01:21 PM ---------- Previous update was at 01:06 PM ----------


Code:
awk -F"|" 'NR==FNR{a[$1]=$2;next}{for(i=3;i<=NF;i++){if($i in a)sub($i,a[$i],$i)}print}' OFS="|" lookupfile contentfile

The above post,from user ahamed101 provides a solution

Last edited by Scrutinizer; 10-08-2012 at 03:47 PM.. Reason: code tags
Sponsored Links
    #2  
Old 10-08-2012
rdrtx1 rdrtx1 is offline
Registered User
 
Join Date: Sep 2012
Last Activity: 15 July 2014, 11:14 AM EDT
Location: Houston, Texas, USA
Posts: 675
Thanks: 0
Thanked 203 Times in 195 Posts

Code:
 
awk 'BEGIN {FS=OFS="|"} NR==FNR{a[$1]=$2;next} $3 in a{$3=a[$3];print $0}' lookup.txt file1.txt

Sponsored Links
    #3  
Old 10-08-2012
venalla_shine venalla_shine is offline
Registered User
 
Join Date: Oct 2012
Last Activity: 8 October 2012, 3:34 PM EDT
Posts: 2
Thanks: 1
Thanked 0 Times in 0 Posts
Thanks for posting, but this code does only partial work.It should also print the line from file1.txt in case it's a mismatch.
    #4  
Old 10-08-2012
rdrtx1 rdrtx1 is offline
Registered User
 
Join Date: Sep 2012
Last Activity: 15 July 2014, 11:14 AM EDT
Location: Houston, Texas, USA
Posts: 675
Thanks: 0
Thanked 203 Times in 195 Posts

Code:
 
awk 'BEGIN {FS=OFS="|"} NR==FNR{a[$1]=$2;next} $3 in a{$3=a[$3]};{print $0}' lookup.txt file1.txt

Sponsored Links
    #5  
Old 10-08-2012
migurus migurus is offline
Registered User
 
Join Date: Sep 2008
Last Activity: 1 August 2014, 9:48 PM EDT
Location: US
Posts: 207
Thanks: 25
Thanked 20 Times in 19 Posts
you would need to add else clause to situation when $3 in a is not found, such as below

Let me reformat the one-liner into a structured form:

Code:
gawk 'BEGIN {FS=OFS="|"}
{
        if(NR==FNR){
                a[$1]=$2;
                next
        }
        if($3 in a){
                $3=a[$3];
                print $0
        }
         else {
                print $0
        }
}'


Last edited by migurus; 10-08-2012 at 03:47 PM.. Reason: typo
Sponsored Links
Closed Thread

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

More UNIX and Linux Forum Topics You Might Find Helpful
Thread Thread Starter Forum Replies Last Post
Plz Help. Compare 2 files field by field and get the output in another file. i150371485 Shell Programming and Scripting 5 07-26-2012 12:32 AM
Compare two files Field by field and output the result in another file i150371485 Shell Programming and Scripting 7 07-20-2012 07:01 AM
[Solved] Lookup a file and match the contents forums123456 Shell Programming and Scripting 2 02-08-2012 03:57 PM
AWK: Pattern match between 2 files, then compare a field in file1 as > or < field in file2 right_coaster Shell Programming and Scripting 4 10-06-2011 06:07 PM
simplify the script, check field match to value in a file jimmy_y Shell Programming and Scripting 6 05-25-2010 03:40 AM



All times are GMT -4. The time now is 04:36 AM.