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: 1909
Joined: Tue Sep 19, 2006 3:29 pm
Contact:

Parse Name and Address

Post by Eric » Fri May 21, 2010 1:34 pm

When you have the name, address, city, state, and zip all in one field, assign the address field to that information, import the list, then pull down the Edit menu to Command.
In the Save As box, type in: Parse Name and Address
In the Command Statement Syntax area, paste the information 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