Parsing Data using EXTRACT_STRING() question

[Rich Hartnett]

I'm trying to parse out data from a DMV web page by copying the text and placing it into a memo field, then using the EXACT_STRING() function to separate out the necessary fields.

Fortunately most of the needed fields are preceeded by the field name such as "Vehicle Make:" which allows me to use that as the "starting point," (a delimiter of sorts), and then I use the field name of the next field as my "end point."

This works fine for almost every field except three, and I'm looking for some suggestions on those, but I'll just ask about the first one here now to avoid confusion.

One of the fields is the vehicle owner's address, and it appears as:

Owner Information
License Number:L-200-488-762-000 Suspended/Revoked:YES
Name:JOHN WILLIAM DOE
Address: 1234 WILLIAMSBURG ST
FT WASHINGTON PG MD 20744
Height:5-08 Weight:115 Race:1 Sex:M Date of Birth:09/29/76

As you can see the address is on two lines, with nothing but a carriage return to separate them. I am using the expression of EXTRACT_STRING(MEMO,"Address: ",CRLF()) which gets me 1234 WILLIAMSBURG ST exactly as I want, but I can't figure out how to grab the next line after the carriage return? (City, State, County, Zip) In other words, I want to capture the string after the carriage return, but end at the beginning of the next field name of "Height:"

[Stan Mathews]

Re: Parsing Data using EXTRACT_STRING() question

Is the line after the address always Height? I would extract the address in total with
EXTRACT_STRING(MEMO,"Address: ","H")
then then street address is
word(EXTRACT_STRING(MEMO,"Address: ","H") ,1,crlf())
then the rest is
word(EXTRACT_STRING(MEMO,"Address: ","H") ,2,crlf())

I think that's right. I can't get to the documentation wiki to double check.

[Tim Kiebert]

Re: Parsing Data using EXTRACT_STRING() question

word(EXTRACT_STRING(MEMO,"Address: ","Height:") ,2,crlf()) should do it.

oops, didn't see that Stan replied while I was.

Stan, yep that's right but I would use the the complete tag as the delimiter as in 'Height:'. Just using "H" runs the risk of it being in the address. In this case the return would be "FT WAS"

[Stan Mathews]

Re: Parsing Data using EXTRACT_STRING() question

Just using "H" runs the risk of it being in the address

Correct, thanks.

[Rich Hartnett]

Re: Parsing Data using EXTRACT_STRING() question

Excellent! Yes Stan, the next field name is always "Height:", so as Tim said, using it in full prevents complications from just using the letter "H., and the expression of word(EXTRACT_STRING(MEMO,"Address: ","Height:") ,2,crlf()) works like a champ!!

Now to the second issue:

The parsed field from the above expression, results in this data:
FT WASHINGTON PG MD 20744
Which is the City (Ft Washington), the County (PG), the State (MD), and the Zip (20744).

The unusual field of "County" always shows on Maryland Licenses and Registrations (and that is all I'm working with here) so it will always be present. I'm using the "Break City/State/Zip into its parts" expression to break up this field, but with the added County field, I'm getting only the City and the Zip parsed correctly, with the County showing up in the State field, and nothing showing up in the County field.

Is there a way to get all four parts of this line to break apart correctly?

[Stan Mathews]

Re: Parsing Data using EXTRACT_STRING() question

You're going to have a heck of a time designing that expression.

Suppose the second line was

Sault Ste. Marie MR MI 49783

[Rich Hartnett]

Re: Parsing Data using EXTRACT_STRING() question

I was afraid you were going to say that.

These are the "knowns:"
The Zip will always be 5 characters. (They don't use the full zip code on these reports)
The State will always be 2 characters.
The County will always be 1-3 characters.

Any way I could work with that starting from the far right?

[Rich Hartnett]

Re: Parsing Data using EXTRACT_STRING() question

If it makes any difference, I do not need the "County" field for anything, just the City, State and Zip Code.

[Stan Mathews]

Re: Parsing Data using EXTRACT_STRING() question

Taking the knowns you can split the word(EXTRACT_STRING(MEMO,"Address: ","Height:") ,2,crlf())

Where are you doing this? Do we need calculated fields for each of the pieces or a script to process the total address value into fields?

[Rich Hartnett]

Re: Parsing Data using EXTRACT_STRING() question

Thanks for the help Stan. I have a simple table that starts with the memo field, then parses out the individual fields. As for the address, I have one that is called "CityStateFull," which contains, the CITY, COUNTY, STATE & ZIP from the expression you provided above. Then I have four individual fields which would contain the actual city, county, state, and zip once they have been broken up from the City_State field. (See attached pics)

(I don't need the county field if it makes any difference.)

[Stan Mathews]

Re: Parsing Data using EXTRACT_STRING() question

So we need calculated field expressions to trigger off the data entry into the full address field. I'll work on it in the morning if no one else gets there first.

[Raymond Lyons]

Re: Parsing Data using EXTRACT_STRING() question

Something tells me there is a better way to deal with your second problem, but if nothing better shows up here you might be able use the WORD() (using a negative parameter) and W_COUNT() functions to build an expression. It's too late here for me to try for something better--need to get some sleep.

?word("Sault Ste. Marie MR MI 49783",-1)
= "49783" (zip)
?word("Sault Ste. Marie MR MI 49783",-2)
= "MI" (state)
?word("Sault Ste. Marie MR MI 49783",-3)
= "MR" (county whether 2 or 3 characters)
?w_count("Sault Ste. Marie MR MI 49783")
= 6 (total # of words)
'So words 1, 2 and 3 have to be the city

?word("FT WASHINGTON PG MD 20744",-1)
= "20744"
?word("FT WASHINGTON PG MD 20744",-2)
= "MD"
?word("FT WASHINGTON PG MD 20744",-3)
= "PG"

Raymond Lyons

[Rich Hartnett]

Re: Parsing Data using EXTRACT_STRING() question

Fantastic!! Ray that works like a champ!

Now will it make any difference if the County has more or less characters? (The county is normally listed as 1,2, or 3 characters)

[martinwcole]

Re: Parsing Data using EXTRACT_STRING() question

I think the issue with the county will be how many words the city has, but I agree working backwards is a good way to go.

one way might be to verify the number of words in the city name from a zip code fetch.find

[Mike Wilson]

Re: Parsing Data using EXTRACT_STRING() question

I have to add this so it is at least in here. I work backwards often The knowns related to this data string are at the end. The end can be the beginning, so to speak. I use this strategy often.

dim vCSZ as C="Sault Ste. Marie MR MI 49783"

dim vzip as C
vzip=*reverse(left(*reverse(vCSZ),5))
? vzip
= "49783"

dim vst as C
vst=alltrim(*reverse(substr(*reverse(vCSZ),6,4)))
? vst
= "MI"

dim vcity as C
vcity=*reverse(word(*reverse(word_subtract(vCSZ,vzip+" "+vst)),3," ",5))
? vcity
= "Sault Ste. Marie"