Delineating/Parsing Values- Plain Text w/simple formatting (1 Viewer)

[Cody.K]

First and foremost, thanks for taking the time to look at my thread. I've been at this for a few hours now- googling, searching threads, etc- and I'm at my wits end.

What I have is a "volunteers database", which stores info on individuals and organizations who volunteer at the local homeless shelter. In the past, the shelter has been gathering this information via forms on it's webpage. Unfortunately, this website is hosted through Weebly, which doesn't support server side scripting. Instead, Weebly emails this information to the shelter via an MHTML form:

[B]<div style='background: #ddd; text-align: center; font-family: helvetica, arial, verdana, sans-serif; font-size: 16px; line-height: 22px; color: #222;'>
    <div style='padding: 30px;'>
        
        <div style='margin: 0 auto; width: 540px; background: white; text-align: left; padding: 25px; border-radius: 8px; -moz-border-radius: 8px; -webkit-border-radius: 8px;'>
            <p>You've just received a new submission to your <a h..ref='com/volunteer.html'>VOLUNTEER FORM</a>.</p><br /><h2 style='font-size: 16px; border-bottom: 1px solid #ccc; color: black; margin: 10px 0; padding: 0 0 5px 0;'><b>Submitted Information:</b></h2><b>Name</b><br />John Doe<br /><br /><b>Phone Number</b><br />555-555-5555<br /><br /><b>Address</b><br />6037 E. East Way<br />Dirty Myrtle, SC  29588<br /><br /><b>Email</b><br />VolunteersEmail@netzero.com<br /><br /><b>Church Or Organization Affiliation</b><br />No Affiliation<br /><br /><b>How would you like to volunteer? (choose all that apply).Kitchen/Food Prep</b><br />1<br /><br /><b>How would you like to volunteer? (choose all that apply).Construction</b><br />1<br /><br /><b>How would you like to volunteer? (choose all that apply).Gardening</b><br />1<br /><br /><b>How would you like to volunteer? (choose all that apply).Prayer Warrior</b><br />1<br /><br /><b>How would you like to volunteer? (choose all that apply).Maintenance</b><br />1<br /><br /><b>How would you like to volunteer? (choose all that apply).Mentoring</b><br />1<br /><br /><b>How would you like to volunteer? (choose all that apply).Job Skills</b><br />1<br /><br /><b>How would you like to volunteer? (choose all that apply).Transportation</b><br />1<br /><br /><b>How would you like to volunteer? (choose all that apply).Front Desk Receptionist</b><br />1<br /><br /><b>How would you like to volunteer? (choose all that apply).Painting</b><br />1<br /><br />
        </div>
    </div>
</div>
[/B]

Looking at this, I realize all the information I want to import into the database is delineated by <br /> tags. I'm not interested in parsing these values, using these tags, because I'm under the impression that they would have to somehow archive these messages in order to run any sort of script against them. We're a ragtag bunch and that seems too complicated to be practical.

I'm simply mentioning this angle because I think you might know better...

So, what I've done is this:

-Setup rules in Outlook 2013 to move these emails to a folder in the Inbox.
-Linked MS Access 2013 to said folder.
-Queried pertinent data.
-Created form and set the query as it's data source:

You've just received a new submission to your VOLUNTEER FORM.



Submitted Information:

Name
John Doe

Phone Number
555 - 555 - 5555

Address
1510 Lovely LN, Apt 206
Dirty Myrtle, SC United States 29577

Email
volunteersemail@gmail.com

Church Or Organization Affiliation
Not Affiliated

How would you like to volunteer? (choose all that apply).Kitchen/Food Prep
1

How would you like to volunteer? (choose all that apply).Maintenance
1

How would you like to volunteer? (choose all that apply).Mentoring
1

How would you like to volunteer? (choose all that apply).Job Skills
1

How would you like to volunteer? (choose all that apply).Front Desk Receptionist
1

How would you like to volunteer? (choose all that apply).Painting
1

And there it sits, in a form-- teasing me...

My question is this: Am I going about this in the most efficient manner? If so, how do I go about parsing this data so that I can append it to the table? If not, what do you suggest I do?

 

[MarkK]

It might be easier to parse that data than you think. Check out the VBA Split() function, which returns an array from a string using some character or characters that you specify as a delimiter.
Here, check out this code ...

[SIZE="1"]Private Sub Test1673491236482()
    Dim HTML As String
    HTML = _
        "<p>You've just received a new submission to your <a h..ref='com/volunteer.html'>VOLUNTEER FORM</a>.</p><br /><h2 style='font-size: 16px; border-bottom: 1px solid #ccc; color: black; margin: 10px 0; padding: 0 0 5px 0;'><b>Submitted Information:</b></h2><b>Name</b><br />John Doe<br /><br /><b>Phone Number</b><br />555-555-5555<br /><br /><b>Address</b><br />6037 E. East Way<br />Dirty Myrtle, SC  29588<br /><br /><b>Email</b><br />VolunteersEmail@netzero.com<br /><br /><b>Church Or Organization Affiliation</b><br />No Affiliation<br /><br /><b>How would you like to volunteer? (choose all that apply).Kitchen/Food Prep</b><br />1<br /><br /><b>How would you like to volunteer? (choose all that apply).Construction</b><br />1<br /><br /><b>How would you like to volunteer? (choose all that apply).Gardening</b><br />1<br /><br /><b>How would you like to volunteer? (choose all that apply).Prayer Warrior</b><br />1<br /><br /><b>How would you like to volunteer? (choose all that apply).Maintenance</b><br />"
    
    Dim pos As Integer
    Dim i As Integer
    Dim var
    
    pos = InStr(HTML, "<b>Name</b>")
    HTML = Mid(HTML, pos)
    HTML = Replace(HTML, "<b>", "")
    HTML = Replace(HTML, "</b>", "")
    HTML = Replace(HTML, "How would you like to volunteer? (choose all that apply).", "")
    
    var = Split(HTML, "<br />")
    
    For i = 0 To UBound(var)
        Debug.Print var(i)
    Next
    
End Sub
[/SIZE]

... so that clean it up a fair bit, right?

 

[Cody.K]

It might be easier to parse that data than you think. Check out the VBA Split() function, which returns an array from a string using some character or characters that you specify as a delimiter.

Thanks for the reply lagbolt. I considered the Split() function, using <br /> as the delimiter, but as I had said:

"I'm under the impression that they would have to somehow archive these messages in order to run any sort of script against them."

In other words, I think the process of working with the HTML would be too much of for the other volunteer(s). Again, you may know better...

The messages are stored in plain text/rich text on the table. Do you have any ideas on how I could apply the Split() function to plain text, when there isn't any tell tale delimiter. Line count maybe?

 

[pr2-eugin]

I am sorry but I find myself a bit.. with the description..

Could you give a simple example of how the data is in the table and what is that you want to do in simple English?

 

[MarkK]

when there isn't any tell tale delimiter. Line count maybe?

There is a delimiter. What case are you talking about? I would parse out the data in code, and write it to a table. A big part of the parsing is done by the code I posted.
Or do I not understand the problem?

 

[spikepl]

I think all HTML has been stripped from the final message. Still, if you stuff the content of the form into a string, and use Lagbolt's Split function, where you do the split on vbCrLf & vbCrLf, you'd wind up with a string array- that would still need to be stripped for vbcrlfl, that separates lines.

 

[Cody.K]

I am sorry but I find myself a bit.. with the description..

Could you give a simple example of how the data is in the table and what is that you want to do in simple English?

Sure thing, sir.

The field name is Contents.
Data Type: Long Text
Text Format: Plain Text

This is what's stored in it:

[B]You've just received a new submission to your VOLUNTEER FORM.    Submitted Information:  Name John Doe  Phone Number 555 - 555 - 5555  Address 1510 Lovely LN, Apt 206 Dirty Myrtle, SC United States 29577  Email volunteers email  Church Or Organization Affiliation Not Affiliated  How would you like to volunteer? (choose all that apply).Kitchen/Food Prep 1  How would you like to volunteer? (choose all that apply).Maintenance 1  How would you like to volunteer? (choose all that apply).Mentoring 1  How would you like to volunteer? (choose all that apply).Job Skills 1  How would you like to volunteer? (choose all that apply).Front Desk Receptionist 1  How would you like to volunteer? (choose all that apply).Painting 1[/B]

I would like to "clean this up" so that I can append the information to [another table]:

Name would be parsed to [FName] and [LName], respectively.

Phone number to [PNumber]

Address to [Address], [City], [State], and [Zip]

Email to Email

and so on and so forth.

 

[spikepl]

I think I gave you the recipe how to do it in #6. Since you haven't responded, I'm guessing you have no clue what is written there? Which bit is incomprehensible?

 

[Cody.K]

I think I gave you the recipe how to do it in #6. Since you haven't responded, I'm guessing you have no clue what is written there? Which bit is incomprehensible?

Hey, Spike, I wasn't ignoring you. I had started my reply, hoping to hit on everything everyone had said, but I was cut short. Again, I appreciate everyone's patience with me here.

You were right in your assumptions in #6-- the HTML has in fact been stripped. I tried to make the vbCrLf & vbCrLf adjustment you had suggested, hoping this would give me something to play with, but I'm not getting any output whatsoever. How do I go about printing (Debug.Print var(i) ) to either "Me.Contents" or an unbound text box?

Private Sub cmdCleanUp_Click()
    Dim Contents As String
    Contents = Me.Contents
    
    Dim i As Integer
    Dim var
    
    Contents = Replace(Contents, "You've just received a new submission to your VOLUNTEER FORM ", "")
    Contents = Replace(Contents, "<link> .", "")
    Contents = Replace(Contents, "How would you like to volunteer? (choose all that apply).", "")
    var = Split(Contents, vbCrLf & vbCrLf)
    
    For i = 0 To UBound(var)
        Debug.Print var(i)
    Next
End Sub

Yeah, I know...