Determining and parsing address info from a string

[TallGuyinIT]

I purchased a mailing list from a marketing company. I need to append the data to an existing mailing list. The database that I received from the marketing company contains 5 character string fields - addr1, addr2, addr3, addr4, addr5.

Browsing the data, the name is in addr1 and the first line of the address is in addr2. My problem is that the city, state and zipcode is either in addr3, addr4, or addr5 depending upon how many address lines were used.

I need a way to determine which field - addr3, addr4, or addr5 contains the zipcode, unless some one else knows of a better way to determine which field contains the city, state and zipcode. Once the field is determined, I need to parse the data into three fields - city, state, and zipcode.

As additional info, I have already tried working backwards from addr5 and looking for the first none blank field. It did not work. Many of the records contain general comments in the field following the city, state, and zipcode information.

Any guidance is appreciated.

 

[mspratt]

City, State and Zip in the same field? Test for comma or test that VAL(RIGHT(addr2,3)) > 0.

Regards,

Mike

 

[audiopro]

Many of the records contain general comments
I would suggest that your main problem is having general comments in the address list in the first place.
Are the general comments in the next available field after the zip-code or do they pop up anywhere?


Keith

http://www.studiosoft.co.uk

 

[KoenPiller]

Hi,
I believe valid Zip-Codes in the USA are 00600 till 99999. Based on that you could load the mailinglist file into a table with 8 records name,field2,field3,field4,field5,fieldZipCode,fieldAdress,fieldCity and than scan field3, field4, field5 to check for valid ZipCode, if found polulate your fieldZipcode with that, fieldAdress with iif(field3=valid(zip),field4,field5)
Hope this is workable.
Regards,
Koen

 

[wgcs]

I'd like to second Mike's suggestion... and add in Koen's suggestion:

lnZipIdx = 0
for lnI = 5 to 1 step -1
  lcVal = eval('addr'+tran(lnI))
  lnZIP = VAL( GETWORDNUM(lcVal,GETWORDCOUNT(lcVal)) )
  if between( lnZip, 00600, 99999 )
    lnZipIdx = lnI
    lcZipFld = 'addr'+tran(lnI)
  endif
endfor
* lnZipIdx = the line number of the zipcode (or 0 if not found)
* lcZipFld = the field name of the zipcode
* lnZip    = the zip code itself (as a numeric value)

- Bill

Get the best answers to your questions -- See FAQ481-4875.

 

[alvechurchdata]

State should be one of a known set of two-letter abbreviations, Might it be easier to loop through the records looking for " AL," etc? The space, the comma and the capital letters ought to prevent any false matches. Then you can get zip and city as the characters before and after the state.

Geoff Franklin

http://www.alvechurchdata.co.uk