PARSE routine

[lmv]

Ok... this has probably been done but I haven't found out how to do it yet.

PROBLEM:
If I do a search on super pages for a name, address, city state zip phone
it returns the following
=====
Smith, J
1111 Hopper Hill Ct
Aspen, Co 11111-4444
(500) 222-9999
=======
if I copy it I can paste it into my notes field.
Then I must copy and paste EACH part one at a time into my fields BUT is
there a way I could have a parse button and it would move each PART into the
field it belongs in on my form?

FIELDS ARE:
FirstName
LastName
Address
StreetName
City
Zip
Phone

THANKS for any IDEAS or better yet SOLUTIONS!!

 

[Larry Linson]

Surely, you can do it, and it will not be to difficult, if the name/address
data is all well-formed and all follows the format. Name/address data,
however, is rarely ever well-formed and following the same format. Then it
gets enough more difficult that almost all "address clean-up" functions
include a manual step to allow a human to look at and make a final fix.

Googling the groups and the internet, if you are good at Googling, may bring
you to some examples. I am not so presumptuous as to post something here.

I've done a couple for customers/clients, over the years, but the data was
reasonably restricted for those clients and they were not at all general.
The first person who tried them on "odd data" would be back, giving me
"what-for" because "everybody knows" that free, posted code should never
have an "error" no matter what kind of sloppy, un-conditioned input you feed
it.

Larry Linson
Microsoft Office Access MVP

 

[lmv]

Larry,
RE:

Googling the groups and the internet, if you are good at Googling, may bring
you to some examples. I am not so presumptuous as to post something here.

I had done this before posting and have found bits and pieces but not enough
to know how to get it to a button...

I do not expect to not clean it up nor do I expect it to not have errors. My
last question got an answer that had error in the code BUT I was still able
to use the code to get started. So, even if you don't want to post the code
here... can you post the link that would direct me to it if you have posted
it before?

Thanks

 

[Larry Linson]

You wouldn't want to Google on my name to find such code... I've posted tens
of thousands of answers, and certainly don't try to keep an index of them.
Nor is such parsing code, as far as I can remember, in any of the examples
I've posted... those are, mostly, at http://accdevel.tripod.com, and at
http://appdevissues.tripod.com. My guess is that I probably do have a saved
copy on a diskette from back in the mid-1990s and if I had the job to do
again, and couldn't find something online, it'd likely be simpler to rewrite
than to locate.

Examples aren't very useful, typically... definitions are best, and may be
helpfully supplemented by examples.
Here's what you showed in your first post...

=====
Smith, J
1111 Hopper Hill Ct
Aspen, Co 11111-4444
(500) 222-9999
=======
if I copy it I can paste it into my notes field.
Then I must copy and paste EACH part one at a time into my fields BUT is
there a way I could have a parse button and it would move each PART into
the
field it belongs in on my form?

FIELDS ARE:
FirstName
LastName
Address
StreetName
City
Zip
Phone

Can you clarify the definition of your data from which you want to begin?

1. Is each address separated by before-and-after lines of equal signs, as
shown?
2. Are the separate parts (always) on separate lines, as shown? Is each
ended by a vbCrLf or are they separate records read in from a file? If not,
how are they separated?
3. Is the name line always in three parts: last name, followed by a comma,
followed by first?
4. Do you actually NOT have a field for state/state code, as shown? If you
do not, where do you plan to store the state code?
5. Do you expect all the addresses to have ZIP plus FOUR format, or do some
of them only have a 5-digit ZIP code? Are you going to use the ZIP code as a
number (not usual, but occasionally done, to determine distance from / to
some geographic point)?
6. Are you going to use the phone number (which would be unusual) as a
number or can it be saved, with any included punctuation, as a text string
(the normal case)?
7. Are any of the addresses missing one or more of the lines?
8. Might any of the addresses have two address lines?

If you can provide answers to these, perhaps I can give you some suggestions
or snippets that would be helpful.

Larry Linson
Microsoft Office Access MVP

 

[lmv]

Hi Larry,

1. Is each address separated by before-and-after lines of equal signs, as
shown?

No that was just a seperator for post here it is not included.

2. Are the separate parts (always) on separate lines, as shown? Is each
ended by a vbCrLf or are they separate records read in from a file? If not,
how are they separated?

Just to clarify my question though you may already fully understand it.

I have a form with address fields.
I do a search on any of the phone book search online.
They return a value that is formatted example:

Smith, J
1111 Hopper Hill Ct
Aspen, Co 11111-4444
(500) 222-9999

I want to COPY FROM THE INTERNET and PASTE that data into a field on my form
and then clk a button that will dispurse that field into my forms fields
where I will be checking it to see it is accurate before saving.
(Technically I understand this is NOT putting it into the form it is putting
it into a NEW RECORD in the [Names List] Table and displaying it in my form.)

I have been using a [notes] which is a text field on my form to paste the
data from the internet. Then I cut copy and paste it section by section into
my forms fields that apply.

Regarding the rest of your questions:

3. Is the name line always in three parts: last name, followed by a comma,
followed by first?

Yes that is the format the site returns.

4. Do you actually NOT have a field for state/state code, as shown? If you
do not, where do you plan to store the state code?

I do have a field for state in my table [Names List] table it is controlled
by the City field because I only use 3 states so the data from the state is a
number and will not need to fill.

5. Do you expect all the addresses to have ZIP plus FOUR format, or do some
of them only have a 5-digit ZIP code?

I am only concerned with the 5 digits even though the intenet returns the 9
+ dash
my field is text - format: 00000\-9999;;_ I have only been pasting the 5

Are you going to use the ZIP code as a number (not usual, but occasionally done, to determine distance from / to some geographic point)?

No it is a text field.

6. Are you going to use the phone number (which would be unusual) as a
number or can it be saved, with any included punctuation, as a text string
(the normal case)?

Phone is text field - format: !\(999") "000\-0000;;_)

7. Are any of the addresses missing one or more of the lines?

could be they could have NO ADDRESS LINE because it could just be a name and
phone but I wouldn't NEED to use the parse button for those if it is
difficult to impliment.

8. Might any of the addresses have two address lines?

No they would not have 2 address lines

Where I put "address" on my list it is actually [StreetNumber] and it is a
number field
It is complicated because sometimes street number could be 2-5 numbers
before you get to the street name.

[City] is a Number field based on CityID table

I certainly appreciated your input. I did find some code just for a name but
I don't know how to implement it regarding a button. I will keep looking to
see what I can find.

Thanks!

 

[lmv]

I found a link
http://www.candace-tripp.com/download/parsenpopulate.zip

I am started... it worked for the name but I have to get rid of the comma
and then figure out how to add additional code to split up the rest:

'-------------start code
Private Sub cmdParse_Click()
On Error GoTo Err_cmdParse_Click

Dim strName As String
Dim strFName As String
Dim strLName As String
Dim intLoc As Integer

Me.Notes.SetFocus
If Me.Notes.Text <> "" Then
strName = Me.Notes.Text
intLoc = InStr(1, strName, " ")
strLName = Left(strName, intLoc)
strFName = Right(strName, Len(strName) - intLoc)
Me.FirstName.SetFocus
Me.FirstName.Text = strFName
Me.LastName.SetFocus
Me.LastName.Text = strLName
End If

Exit_cmdParse_Click:
Exit Sub

Err_cmdParse_Click:
MsgBox Err.Description
Resume Exit_cmdParse_Click

End Sub
'----------------end code

Hi Larry,

1. Is each address separated by before-and-after lines of equal signs, as
shown?

No that was just a seperator for post here it is not included.

2. Are the separate parts (always) on separate lines, as shown? Is each
ended by a vbCrLf or are they separate records read in from a file? If not,
how are they separated?

Just to clarify my question though you may already fully understand it.

I have a form with address fields.
I do a search on any of the phone book search online.
They return a value that is formatted example:

Smith, J
1111 Hopper Hill Ct
Aspen, Co 11111-4444
(500) 222-9999

I want to COPY FROM THE INTERNET and PASTE that data into a field on my form
and then clk a button that will dispurse that field into my forms fields
where I will be checking it to see it is accurate before saving.
(Technically I understand this is NOT putting it into the form it is putting
it into a NEW RECORD in the [Names List] Table and displaying it in my form.)

I have been using a [notes] which is a text field on my form to paste the
data from the internet. Then I cut copy and paste it section by section into
my forms fields that apply.

Regarding the rest of your questions:

3. Is the name line always in three parts: last name, followed by a comma,
followed by first?

Yes that is the format the site returns.

4. Do you actually NOT have a field for state/state code, as shown? If you
do not, where do you plan to store the state code?

I do have a field for state in my table [Names List] table it is controlled
by the City field because I only use 3 states so the data from the state is a
number and will not need to fill.

5. Do you expect all the addresses to have ZIP plus FOUR format, or do some
of them only have a 5-digit ZIP code?

I am only concerned with the 5 digits even though the intenet returns the 9
+ dash
my field is text - format: 00000\-9999;;_ I have only been pasting the 5

Are you going to use the ZIP code as a number (not usual, but occasionally done, to determine distance from / to some geographic point)?

No it is a text field.

6. Are you going to use the phone number (which would be unusual) as a
number or can it be saved, with any included punctuation, as a text string
(the normal case)?

Phone is text field - format: !\(999") "000\-0000;;_)

7. Are any of the addresses missing one or more of the lines?

could be they could have NO ADDRESS LINE because it could just be a name and
phone but I wouldn't NEED to use the parse button for those if it is
difficult to impliment.

8. Might any of the addresses have two address lines?

No they would not have 2 address lines

Where I put "address" on my list it is actually [StreetNumber] and it is a
number field
It is complicated because sometimes street number could be 2-5 numbers
before you get to the street name.

[City] is a Number field based on CityID table

I certainly appreciated your input. I did find some code just for a name but
I don't know how to implement it regarding a button. I will keep looking to
see what I can find.

Thanks!

 

[Larry Linson]

I don't see a need to have a bound control for the initially-pasted
address... that can be an unbound field; you can either paste the parsed
fields into a bound field for a new record, so Access will do the saving, or
you can use VBA-DAO code to write the new record to the table.

If you will always have LastName, comma, space, FirstName

As for eliminating the comma, try replacing the statement "strLName =
Left(strName, intLoc)" with "strLName = Left(strName, intLoc-2)".

You are going to have to determine what control characters make the new
line... that's why I asked the question about "vbCrLf" constant. You should
be able to execute VBA... check help on instring and, if it is in VBA...

Dim intCrLFLoc as Integer
intCrLfLoc = InStr(strWholeAddress, vbCrLF)
If (intCrLfLoc > 0) Then
MsgBox "vbCrLF found at location " & intCrLFLoc"
End If

will display that your lines are separated by the Carriage Return, Chr$(13),
and LineFeed, Chr$(10). Although Access' (and Microsoft Office') standard is
both, the display may work OK with just one, so if you do not find the
vbCrLF, you may need to test for just vbCR or vbLf.

A hint: if you don't understand about a question, or how to get the answer,
it is much more productive to say so, and ask, rather than "clarifying" the
original question without answering it.

Larry Linson
Microsoft Office Access MVP

I found a link
http://www.candace-tripp.com/download/parsenpopulate.zip

I am started... it worked for the name but I have to get rid of the comma
and then figure out how to add additional code to split up the rest:

'-------------start code
Private Sub cmdParse_Click()
On Error GoTo Err_cmdParse_Click

Dim strName As String
Dim strFName As String
Dim strLName As String
Dim intLoc As Integer

Me.Notes.SetFocus
If Me.Notes.Text <> "" Then
strName = Me.Notes.Text
intLoc = InStr(1, strName, " ")
strLName = Left(strName, intLoc)

Me.FirstName.SetFocus

Me.FirstName.Text = strFName
Me.LastName.SetFocus
Me.LastName.Text = strLName
End If

Exit_cmdParse_Click:
Exit Sub

Err_cmdParse_Click:
MsgBox Err.Description
Resume Exit_cmdParse_Click

End Sub
'----------------end code

Hi Larry,

1. Is each address separated by before-and-after lines of equal signs,
as
shown?

No that was just a seperator for post here it is not included.

2. Are the separate parts (always) on separate lines, as shown? Is each
ended by a vbCrLf or are they separate records read in from a file? If
not,
how are they separated?

Just to clarify my question though you may already fully understand it.

I have a form with address fields.
I do a search on any of the phone book search online.
They return a value that is formatted example:

Smith, J
1111 Hopper Hill Ct
Aspen, Co 11111-4444
(500) 222-9999

I want to COPY FROM THE INTERNET and PASTE that data into a field on my
form
and then clk a button that will dispurse that field into my forms fields
where I will be checking it to see it is accurate before saving.
(Technically I understand this is NOT putting it into the form it is
putting
it into a NEW RECORD in the [Names List] Table and displaying it in my
form.)

I have been using a [notes] which is a text field on my form to paste the
data from the internet. Then I cut copy and paste it section by section
into
my forms fields that apply.

Regarding the rest of your questions:

3. Is the name line always in three parts: last name, followed by a
comma,
followed by first?

Yes that is the format the site returns.

4. Do you actually NOT have a field for state/state code, as shown? If
you
do not, where do you plan to store the state code?

I do have a field for state in my table [Names List] table it is
controlled
by the City field because I only use 3 states so the data from the state
is a
number and will not need to fill.

5. Do you expect all the addresses to have ZIP plus FOUR format, or do
some
of them only have a 5-digit ZIP code?

I am only concerned with the 5 digits even though the intenet returns the
9
+ dash
my field is text - format: 00000\-9999;;_ I have only been pasting the
5

Are you going to use the ZIP code as a number (not usual, but
occasionally done, to determine distance from / to some geographic
point)?

No it is a text field.

6. Are you going to use the phone number (which would be unusual) as a
number or can it be saved, with any included punctuation, as a text
string
(the normal case)?

Phone is text field - format: !\(999") "000\-0000;;_)

7. Are any of the addresses missing one or more of the lines?

could be they could have NO ADDRESS LINE because it could just be a name
and
phone but I wouldn't NEED to use the parse button for those if it is
difficult to impliment.

8. Might any of the addresses have two address lines?

No they would not have 2 address lines

Where I put "address" on my list it is actually [StreetNumber] and it is
a
number field
It is complicated because sometimes street number could be 2-5 numbers
before you get to the street name.

[City] is a Number field based on CityID table

I certainly appreciated your input. I did find some code just for a name
but
I don't know how to implement it regarding a button. I will keep looking
to
see what I can find.

Thanks!

 

[lmv]

I have fixed the comma problem according to your code.
Now I will work on the how to move to the next line
At the moment I have 2 parse buttons one for the NAME another for the
ADDRESS but I am just copying each line in seperately from superpages.
Sorry if my restating the problem was an annoyance, I had no idea what the
coding used by the phonebook search would be as to what form it returned the
address and didn't know how anyone would know that offhand (if you didn't
work for them).
I restated the problem thinking I wasn't clear. But, I quess the answer
should be whatever ACCESS uses since when you copy from anywhere and paste
into ACCESS what does it use to make it go to the next line?

In any case I really appreciated the input.
lmv

I don't see a need to have a bound control for the initially-pasted
address... that can be an unbound field; you can either paste the parsed
fields into a bound field for a new record, so Access will do the saving, or
you can use VBA-DAO code to write the new record to the table.

If you will always have LastName, comma, space, FirstName

As for eliminating the comma, try replacing the statement "strLName =
Left(strName, intLoc)" with "strLName = Left(strName, intLoc-2)".

You are going to have to determine what control characters make the new
line... that's why I asked the question about "vbCrLf" constant. You should
be able to execute VBA... check help on instring and, if it is in VBA...

Dim intCrLFLoc as Integer
intCrLfLoc = InStr(strWholeAddress, vbCrLF)
If (intCrLfLoc > 0) Then
MsgBox "vbCrLF found at location " & intCrLFLoc"
End If

will display that your lines are separated by the Carriage Return, Chr$(13),
and LineFeed, Chr$(10). Although Access' (and Microsoft Office') standard is
both, the display may work OK with just one, so if you do not find the
vbCrLF, you may need to test for just vbCR or vbLf.


Larry Linson
Microsoft Office Access MVP

I found a link
http://www.candace-tripp.com/download/parsenpopulate.zip

I am started... it worked for the name but I have to get rid of the comma
and then figure out how to add additional code to split up the rest:

'-------------start code
Private Sub cmdParse_Click()
On Error GoTo Err_cmdParse_Click

Dim strName As String
Dim strFName As String
Dim strLName As String
Dim intLoc As Integer

Me.Notes.SetFocus
If Me.Notes.Text <> "" Then
strName = Me.Notes.Text
intLoc = InStr(1, strName, " ")
strLName = Left(strName, intLoc)

Me.FirstName.SetFocus

Me.FirstName.Text = strFName
Me.LastName.SetFocus
Me.LastName.Text = strLName
End If

Exit_cmdParse_Click:
Exit Sub

Err_cmdParse_Click:
MsgBox Err.Description
Resume Exit_cmdParse_Click

End Sub
'----------------end code

Hi Larry,
1. Is each address separated by before-and-after lines of equal signs,
as
shown?

No that was just a seperator for post here it is not included.

2. Are the separate parts (always) on separate lines, as shown? Is each
ended by a vbCrLf or are they separate records read in from a file? If
not,
how are they separated?

Just to clarify my question though you may already fully understand it.

I have a form with address fields.
I do a search on any of the phone book search online.
They return a value that is formatted example:

Smith, J
1111 Hopper Hill Ct
Aspen, Co 11111-4444
(500) 222-9999

I want to COPY FROM THE INTERNET and PASTE that data into a field on my
form
and then clk a button that will dispurse that field into my forms fields
where I will be checking it to see it is accurate before saving.
(Technically I understand this is NOT putting it into the form it is
putting
it into a NEW RECORD in the [Names List] Table and displaying it in my
form.)

I have been using a [notes] which is a text field on my form to paste the
data from the internet. Then I cut copy and paste it section by section
into
my forms fields that apply.

Regarding the rest of your questions:

3. Is the name line always in three parts: last name, followed by a
comma,
followed by first?

Yes that is the format the site returns.

4. Do you actually NOT have a field for state/state code, as shown? If
you
do not, where do you plan to store the state code?

I do have a field for state in my table [Names List] table it is
controlled
by the City field because I only use 3 states so the data from the state
is a
number and will not need to fill.

5. Do you expect all the addresses to have ZIP plus FOUR format, or do
some
of them only have a 5-digit ZIP code?

I am only concerned with the 5 digits even though the intenet returns the
9
+ dash
my field is text - format: 00000\-9999;;_ I have only been pasting the
5

Are you going to use the ZIP code as a number (not usual, but
occasionally done, to determine distance from / to some geographic
point)?

No it is a text field.

6. Are you going to use the phone number (which would be unusual) as a
number or can it be saved, with any included punctuation, as a text
string
(the normal case)?

Phone is text field - format: !\(999") "000\-0000;;_)

7. Are any of the addresses missing one or more of the lines?

could be they could have NO ADDRESS LINE because it could just be a name
and
phone but I wouldn't NEED to use the parse button for those if it is
difficult to impliment.

8. Might any of the addresses have two address lines?

No they would not have 2 address lines

Where I put "address" on my list it is actually [StreetNumber] and it is
a
number field
It is complicated because sometimes street number could be 2-5 numbers
before you get to the street name.

[City] is a Number field based on CityID table

I certainly appreciated your input. I did find some code just for a name
but
I don't know how to implement it regarding a button. I will keep looking
to
see what I can find.

Thanks!

 

[Larry Linson]

I have fixed the comma problem according to your code.
Now I will work on the how to move to the next line
At the moment I have 2 parse buttons one for the NAME another for the
ADDRESS but I am just copying each line in seperately from superpages.

Obviously, in the "real world" you won't want to spend the extra time of
copying and parsing each line separately -- read the entire name/address
information and paste it into an unbound text box, from which you will work,
the parse out all the information from that name/address information. If
you should have one that is only name plus telephone number, you are going
to have to handle it manually, or perhaps that might be common enough that
you would just create a separate sequence for name and phone alone.

Sorry if my restating the problem was an annoyance, I had
no idea what the coding used by the phonebook search would
be as to what form it returned the address and didn't know how
anyone would know that offhand (if you didn't work for them).

It wasn't so much an annoyance as a delay... but now I explained, I think,
how you can check it. That will determine how you find the next line.

Assume that you determine that the end-of-line or new-line used is the
intrinsic constant vbCrLf which is the combination of Chr$(13) & Chr$(10)...
to extract the address line from the entire string, you might use... if you
have pasted the entire name/address into a text box called txtWholeShebang,
use something like this:

Dim intAddrStartLoc as Integer
Dim intAddrEndLoc as Integer
Dim strTheAddrPart as String

intAddrStartLoc = InStr(Me.txtWholeShebang, vbCrLf)+2
intAddrStartLoc = InStr(intAddrStartLoc,Me.txtWholeShebang, vbCrLF)-1
strTheAddrPart = Mid(strTheWholeShebang, intAddrStartLoc,
intAddrEndLoc - intAddrStartLoc + 1)

and then follow this for each line... the previous location of the vbCrLf
for the previous line, plus two, will give you the start of the next line.
The last line may or may not be followed by a vbCrLf, so you'll just have to
explore whether it does, does not, or is consitent.

on the City, ST zip line you will have to do additional searching on comma
and space to extract the parts. As you are not going to try to do any
calculations on the zip and phone, I'd leave them exactly as is... it will
be pretty easy for a human to mentally process them, perhaps not so easy to
write code to try to shape-up the formatting.

I restated the problem thinking I wasn't clear. But, I quess the
answer should be whatever ACCESS uses since when you
copy from anywhere and paste into ACCESS what does it
use to make it go to the next line?

No, that would seem to make sense, but it doesn't necessarily follow -- as I
stated... there's not an "industry-standard" convention for that... and
Office, including Access, tries to be "considerate" and will properly
display some other formats.

In any case I really appreciated the input.

Thanks for your kind words. If you have additional questions, feel free to
follow up, making the question as specific as you can.

Larry Linson
Microsoft Office Access MVP