Create a new command, naming it Parse Name and Address
In the Command Statement Syntax area, paste this command from the code box below:
Code: Select all
ON ERROR com_Mesg=MESSAGE()
ALTER TABLE ALIAS() ADD COLUMN first C(50)
ALTER TABLE ALIAS() ADD COLUMN last C(50)
Replace first with alltrim(alltrim(first)+" "+left(address,at(" ",alltrim(address))-1)), address with ltrim(substr(address,at(" ",alltrim(address))+1)) for occur(" ",alltrim(address))>0 and !ISDIGIT(LEFT(address,1)) AND !"PO BOX" $ LEFT(UPPER(Address),6)
use in iif(used('_uszips'),select('_uszips'),0)
replace zip with allt(substr(address,rat(" ",allt(address)))),;
address with left(address,rat(" ",allt(address)));
for empty(zip) and isdigit(right(allt(address),1))
replace address with allt(address)+" "+zip,;
zip with "" for isalpha(allt(zip))
public comalias
comalias=alias()
use (addbs(xDefFldr)+"dmm\_uszips.dbf") in 0 order uszip
replace city with _uszips.prefcity,;
st with _uszips.st for seek(left(zip,5),'_uszips')
use in iif(used('_uszips'),select('_uszips'),0)
replace address with left(address,rat(left(city,4),upper(address))-1) ;
for !empty(city) and rat(left(city,4),upper(address))>0
replace city with allt(substr(address,rat(" ",allt(address),2))) for (empty(city) ;
or allt(st) $ address)
replace address with strtran(address,allt(city)) for allt(city) $ address and !empty(city)
replace address with allt(address)+" "+city for !" " $ allt(address)
replace city with "" for allt(city) $ address and !empty(city)
release comalias
Replace last with left(address,at(" ",alltrim(address))-1), address with ltrim(substr(address,at(" ",alltrim(address))+1)) FOR EMPTY(last)
Replace last with ALLT(last)+" "+left(address,at(" ",alltrim(address))-1), address with ltrim(substr(address,at(" ",alltrim(address))+1)) FOR !"PO" $ LEFT(UPPER(address),2) AND !ISDIGIT(LEFT(address,1)) AND !"RR" $ LEFT(UPPER(address),2)
Replace first with ALLT(first)+" "+ALLT(last)+" "+ left(address,at(" ",alltrim(address))-1), last with "",address with substr(ALLT(address),at(" ",alltrim(address))+1) for occur("&",alltrim(last))>0
Replace first with ALLT(first)+" "+left(address,at(" ",alltrim(address))-1), address with substr(ALLT(address),at(" ",alltrim(address))+1) for !ISDIGIT(LEFT(address,1)) AND !"BOX" $ UPPER(address) AND !"RR" $ UPPER(LEFT(address,2)) AND !"HC" $ UPPER(LEFT(address,2))