Problem with lookup in awk

Shell Programming and Scripting
1

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

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

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

497476|10636872
495713|10640836

My awk code (with all the debug I put in it)

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:

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.

2

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

Thanks.
I changed my FS / OFS assignment to be

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

Try adding a zero to convert:-

awk -F\| '
        NR == FNR {
                A[$1] = $2
                next
        }
        $7+0 in A {
                $(NF+1) = A[$7+0]
        }
        1
' OFS=\| lookup_file input_file
1 Like
5

Thank you! That adding of 0 made the trick, now I know how to force awk to work with integers