Parse Name and Address
If you have the name, address, city, st, and zip all in one single field, assign the field named Address to that field, then pull down the Edit menu to Command.
Create a new command, naming it Parse Name and Address
In the Command Statement Syntax area, paste this command from the box below:
| 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)) |
