Shell Programming and Scripting

Hello,

I have 2 files delimited by "|".

File1:

Code:

1|New York
12| Buffalo
599| Syracuse

File2:

Code:

56 Kennedy |1
9 Burridge Pl|15
98 BELL ROCK |599

My goal: Is to replace the numerical numbers in "File 2" (second field, not street address) with the corresponding city names from "File 1" where the second field in file 2 matches the first field in file 1. Many in the run of the larger list will not match and should just be ignored. In other words for the 3 records in each file listed above only 2 records should display and should ultimately > forwarded to a new file that looks like:

Code:

56 Kennedy | New York
98 BELL ROCK | Syracuse

Delimiters should remain. I really need to do this with text utilities before data gets imported. Most likely I may need to add a 3rd and 4th field as a delimiter to file 1 for "state" and "zip code". Just wanted to mention that. I assume I can just do that after I get the finished file by doing something like:

Code:

awk -F\| '{print $1,$2"||"}' newfile

I have awk, sed, cut etc. to get things this far and in numerical incrementing fields. I am just having a tough time sealing the deal. Any help appreciated. It's been a while for me battling it out with the old text utils! I realize this is easier with something like mysql or VB but I need to take care of it before importing to anything else.


Thanks in advance for any replies!


Art

Actually, pretty straight forward in awk:

Code:

awk -F \| '
    NR == FNR { city[$1] = $2; next; }
    $2 in city { printf( "%s|%s\n", $1, city[$2] ); }
' file1 file2

It can easily be expanded to include state and zip, or just add them as space separated tokens after the city:

Code:

1|New York, New York 10092

Awesome!

Thank you so much. I have not programmed in quite a while and I was really spinning my wheels on this one. I was never as proficient with awk as I should be. I really need to practice that as it is very powerful and much of what I do for projects always seems to involve more advanced awk commands at one point or another. I had one other issue but will probably start a new thread so keep this one solved, short and to the point.

Thanks again!