Core iTOps Tube

Sunday, 6 May 2012

Replacing data in one file with data in another


I have 2 files delimited by "|".


1|New York

12| Buffalo

599| Syracuse


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:

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:

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!


No comments:

Post a Comment