Parse Name and Address

Build your list of AccuZIP6 commands by requesting commands and sharing commands with other users

Moderators: Chrissy, Steve, Terry, Donna, Dave, Eric, MartyH, Kristen, Geoff, Vince

Post Reply
User avatar
Eric
Moderator
Posts: 1910
Joined: Tue Sep 19, 2006 3:29 pm
Contact:

Parse Name and Address

Post by Eric » Wed Oct 30, 2013 10:17 am

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 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))
Chat with an AccuZIP6 technician weekdays from 7-4 PST with AccuZIP6 Live Online Support

Post Reply