Replace field in one file with whole record data of another

Shell Programming and Scripting
1

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

2

It think perl or GNU sed are more suited for uppercase / lowercase conversion. Give this try:

perl -pe 's/\b(.)(.*?)\b/$1\L$2/g' file2 |
awk 'NR==FNR{A[$1]=$0; next}$3 in A {$3=A[$3]}1' FS=\| OFS=\| - file1

This is assuming that the leading space in the files is not there in reality. This can be done in regular awk, but that leads to elaborate code. This can also be combined in one Perl, but I think this might do...