Extract address from character fields

[Jeff Carroza]

I'm in the process of importing data from excel spreadsheets into A5V10.5. The data was sent to me with all the address information contained within a single field. I have set up an update using the word() function to extract the street address, town and zip. I seem to recall Alpha having a built-in function to do this, but I can't recall what it was called. Just curious if anyone is familiar with such a function.

Thank You
Jeff

[madtowng]

as long as you can tell where they start and stop, a5_extract_advance works great.

[Jeff Carroza]

Thank you for the quick reply. I've tried writing an expression using the A5_EXTRACT_ADVANCE() function but it seems to be designed for a fixed character string not a variable one. Looks like I'll have to use the WORD(). It just requires a second step for addresses with more than 3 words in the street address, running it for 4 words instead of 3.

[madtowng]

provide something to work with.
I can help, but it really helps to have a sample of what you're working with.

[Jeff Carroza]

I imported multiple spreadsheets into a table. The spreadsheet was setup for the entire address to go into 1 column. I need to breakout the address into 3 fields. For example, here are 2 addresses:

2 Loockerman Ave, Poughkeepsie, NY 12601
4 St. John's Pkwy., Poughkeepsie, NY 12601

I wrote an update to extract the address into 3 fields; address, town and zip. Here are the expressions I used:

Field Expression
Address = word(Fulladdress,1," " ,3)
Town = word(Fulladdress,4," ",1)
Zip = val(word(Fulladdress,6," ",1))

This will return the data I need for the first record. For the second record it will return "Pkwy" as the town and 0 for the zip. I then do a query for zip=0 and run this update:

Field Expression
Address = word(Fulladdress,1," " ,4)
Town = word(Fulladdress,5," ",1)
Zip = val(word(Fulladdress,7," ",1))

This update will return the correct data for the second record. I don't have any experience with the A5_extract_advance function, when I read the description in the help file it showed:

? a5_extract_advance("this is a string with several words", "string", "several", .f., .f.) = " with "

Is there a way to use a wildcard to set the first string and the ", " to set the second string? Not sure if this would work?

[madtowng]

oh wow....if all the data is like that, it should be fairly simple to parse.
Give me a chance to free up.

[madtowng]

Sorry about the delay .
It's pretty much straight forward.
Feel free to ask if you have any questions.

dim listOfLocations as C
dim location as C
dim locationsArray[0] as P

listOfLocations = <<%txt%
2 Loockerman Ave, Poughkeepsie, NY 12601
4 St. John's Pkwy., Poughkeepsie, NY 12601
%txt%
debug(1)
for each location in listOfLocations
locationsArray[].address = left(location.value,at(", ",location.value)-1)
locationsArray[..].city = a5_extract_advance(location.value,", ", ", ")
locationsArray[..].statepostal = right(location.value,rat(", ",location.value)-2)
next ' location

debug(1)

Capture.png

[Jeff Carroza]

Thank so much. Your code worked exactly as needed. For the zip field I only needed the 5 digit zip as a numeric so I added the val() function at the front and a "-5" at the end. I forgot about the left() and right() functions. I never used the at() and rat() or a5_extract_advance() before. Thank you for the tutorial in their use.

Jeff

[madtowng]

Glad to help Jeff. just a heads up, some zipcodes start with zero, and if you do anything with Canada, they have funky postal codes that contain digits and letters (I'm not sure if the space is actually part of their postal code).
You right want to do just right( data,5) to make sure you don't lose expected or needed data.

[Jeff Carroza]

Thank you for the tips. The end result of this db is a text file which requires the zip to be in a numeric format. When I set up a db I normally make the postal code a character field.