Mac iTOps Tube

Sunday, May 6, 2012

Replace field in one file with whole record data of another

Hello Group,



I need to replace the city field in "File 1 (fileld 3), with the entire record line of "File 2" (including delimiters) where the "city" field (File 1, Field 3)matches city field (File 2, Field1). All of the other data in "File 1" should remain intact(Fields 1,2,4,5,6). Only field 3 gets replaced.



First I need to convert "File 2" to have only the first letter of each word capitalized. I know I can:



tr '[:upper:]' '[:lower:]' < file2 > outfile



to get everything to lower case but I am lost after that to convert back just the first alphabetic character in each word, in each field of "File 2" to capitals.



Here is the sample data I need to convert below:



File1:



10 MISC 400000|03/01/2012|Syracuse|53-55 Main St. Ave|Lisa Thomas|Tom Alexander

10 MISC 400012|03/01/2012|Buffalo|327 Main Street|Robin Lake|Anita Parker

10 MISC 460316|03/01/2012|New York|82-84 Broadway, Unit 84|John Smith|Joe Jones



File2:



NEW YORK|NY|01234|COUNTY1|978|42.562912|-71.568824

SYRACUSE|NY|05678|COUNTY2|781/617|42.298800|-71.260100

BUFFALO|NY|09112|COUNTY3|978|42.600122|-72.086316





So with that said the result of conversion of "File 2" + replacing field 3 in "File" should ultimately look like the below (Newfile).





Newfile:



10 MISC 400000|03/01/2012|Syracuse|Ny|05678|County2|781/617|42.298800|-71.260100|53-55 Main St. Ave|Lisa Thomas|Tom Alexander

10 MISC 400012|03/01/2012|Buffalo|Ny|09112|County3|978|42.600122|-72.086316|327 Main Street|Robin Lake|Anita Parker

10 MISC 460316|03/01/2012|New York|Ny|01234|County1|978|42.562912|-71.568824|82-84 Broadway, Unit 84|John Smith|Joe Jones





I know most likely this is another job for "awk" but I am way out of practice.





Thanks in advance for any replies!





Art




No comments:

Post a Comment