Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Problem with lookup in awk

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Problem with lookup in awk
# 1  
Old 09-22-2015
Problem with lookup in awk
I need to add new ID to the file with old ID (column 7), I collected old ID / new ID pairs in a lookup file and I am trying to use awk to do the job, but something is not clicking.

My input file
Code:
ABC| 107|1440589221| -118.117167|   33.986333|10|  497476|1
ABC| 125|1440591215| -118.181000|   34.046833|10|  495713|1

I need to get this
Code:
ABC| 107|1440589221| -118.117167|   33.986333|10|  497476|1|10636872
ABC| 125|1440591215| -118.181000|   34.046833|10|  495713|1|10640836

My lookup file
Code:
497476|10636872
495713|10640836

My awk code (with all the debug I put in it)
Code:
awk 'BEGIN{old_key=0; FS=OFS="|"}
{
        if (NR == FNR) {
                old_key = $1;
                printf "adding to array: arr[%i] = %s\n", old_key, $2;
                arr[old_key] = $2;
        }
        else {
                old_key = $7;
                printf "\t\tlooking for:%d\n", old_key
                if(old_key in arr) {
                        new_key = arr[old_key];
                        printf "\t\tfound %d\n", arr[old_key];
                }
                else {
                        printf "\t\tNOT found %d\n", old_key;
                        new_key = -1;
                }
                 print $0, new_key;
        }
}
END{
 print "END";
 for(x in arr) print x, arr[x]
}'

Run results:
Code:
adding to array: arr[497476] = 10636872
adding to array: arr[495713] = 10640836
                looking for:497476
                NOT found 497476
ABC| 107|1440589221| -118.117167|   33.986333|10|  497476|1|-1
                looking for:495713
                NOT found 495713
ABC| 125|1440591215| -118.181000|   34.046833|10|  495713|1|-1
END
497476|10636872
495713|10640836

I'd appreciate it very much if someone can point me in the right direction, thanks in advance.
Last edited by migurus; 09-22-2015 at 04:48 PM.. Reason: typo
# 2  
Old 09-22-2015
You need field specifiers of both space and pipe symbol, otherwise your search is looking for a number preceeded by a space. You want only digits in the search.

With newer awk specify the field separator characters with -F
like awk -F '[ |]'
# 3  
Old 09-22-2015
Thanks.
I changed my FS / OFS assignment to be
Code:
FS="[ |]"; OFS="|"

But it did not change anything.

Just a note: I just want to point out that I initiate old_key to 0 (zero) in BEGIN section, hoping it will be integer.

---------- Post updated at 01:11 PM ---------- Previous update was at 01:01 PM ----------

Thanks, Jim, your suspecting spaces were right (I did too, thought my old_key is integer and will deal with spaces properly)
My awk does not support "[ |]" for SEP, when I tried your suggestion my $7 field became 0, that is why it did not work
I removed blanks from 7th column in the input and it worked.
Now, question, what is the best way to tell awk to disregard those spaces?
# 4  
Old 09-22-2015
Try adding a zero to convert:-
Code:
awk -F\| '
        NR == FNR {
                A[$1] = $2
                next
        }
        $7+0 in A {
                $(NF+1) = A[$7+0]
        }
        1
' OFS=\| lookup_file input_file

This User Gave Thanks to Yoda For This Post:
migurus
# 5  
Old 09-22-2015
Thank you! That adding of 0 made the trick, now I know how to force awk to work with integers
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

awk base lookup of best match strings

Hi, I'm new to scripting and unable to find out a way to perform the below task. Request help in finding out a way to accomplish this. File one consists of some numbers/string which i need to lookup against file 2 and fetch the best match results in output. If best match is not present in... (3 Replies)
Discussion started by: suraj016
3 Replies

2. Shell Programming and Scripting

awk to lookup value in one file in another range

I am trying to update the below awk, kindly provided by @RavinderSingh13, to update each line of file1 with either Low or No Low based on matching $2 of file1 to a range in $2 and $3 of file2. If the $2 value in file1 matches the range in file2 then that line is Low, otherwise it is No Low in the... (3 Replies)
Discussion started by: cmccabe
3 Replies

3. UNIX for Dummies Questions & Answers

AWK lookup not finding match

Hello everyone, I have been struggling with the following situation, I think I am doing something wrong, can anyone help? I have 2 comma separated files, the first is a look-up table that will supply the phone number based on the customer id, the second is a file containing customers and their... (4 Replies)
Discussion started by: gio001
4 Replies

4. UNIX for Advanced & Expert Users

NIS Group Lookup Problem

I'm running a NIS on an network of Ubuntu 8.04 linux systems. I'm seeing a weird problem where the 'id' command is not returning all the groups I am a member of. For example: alex@client $ id -Gn localgroupA localgroupB nisgroup1 nisgroup2 nisgroup4 alex@client $ id -Gn alex nisgroup1... (0 Replies)
Discussion started by: vertigo23
0 Replies

5. Shell Programming and Scripting

Problem with lookup values on AWK associative array

I'm at wits end with this issue and my troubleshooting leads me to believe it is a problem with the file formatting of the array referenced by my script: awk -F, '{if (NR==FNR) {a=$4","$3","$2}\ else {print a "," $0}}' WBTSassignments1.txt RNCalarms.tmp On the WBTSassignments1.txt file... (2 Replies)
Discussion started by: JasonHamm
2 Replies

6. Shell Programming and Scripting

variable lookup problem in shell script

Hi I have one properties file containing as $INSTALL_BASEPATH/mssages/commonmessages_default.properties $INSTALL_BASEPATH/resource/configurationBundle.properties and $INSTALL_BASEPATH is set in .bash_profile but from shell script when I read this file and use in copy statement then it... (7 Replies)
Discussion started by: mnmonu
7 Replies

7. Shell Programming and Scripting

Multiple file lookup using awk

I want to lookup filea with fileb,filec and filed. If entry in filea exist in fileb and filec mark Y and then if entry in filea exist in filed mark as Y. Final output should have all the entries from filea. This prints only matching entries from file a in fileb i want all entries from... (9 Replies)
Discussion started by: pinnacle
9 Replies

8. UNIX for Advanced & Expert Users

Clueless about how to lookup and reverse lookup IP addresses under a file!!.pls help

Write a quick shell snippet to find all of the IPV4 IP addresses in any and all of the files under /var/lib/output/*, ignoring whatever else may be in those files. Perform a reverse lookup on each, and format the output neatly, like "IP=192.168.0.1, ... (0 Replies)
Discussion started by: choco4202002
0 Replies

9. Shell Programming and Scripting

file Lookup using awk

Hi All, I have two files file1 and file2(lookup file).I need to map more than one keyfields of file1 with file2.how can we achieve it using awk. file1(max 2.2 million records) -------------------------- 680720|680721|077 680720|680721|978 680721|680722|090 file2(no idea about the... (1 Reply)
Discussion started by: jerome Sukumar
1 Replies

10. IP Networking

reverse lookup file problem

I'm trying to create a reverse lookup file. Below are the error messages I get in the messages file, when I start named. Below the error messages is a copy of the reverse lookup file I'm trying to use. I'm using Bind version 8.1.2. Would someone recommend the correct values and if you see any... (2 Replies)
Discussion started by: Westy564
2 Replies
Login or Register to Ask a Question