Extract specific text from very long string

[csmith]

I'm running Access 2010 and have Outlook feeding in form data to a table.

I need the content of that table split out into columns. Instead of trying to parse it out as it's imported from Outlook (that road looked much too complex for me) I would like to create a query that pulls specific info from one column in the imported email's table and spreads it out over many fields in my query. The trick is the content of the emails that are imported into Access all feed into a single field and it is a long string of text.

So, what I want is to extract the content of the email (ex below) from a single field and move it to various fields. E.g.

"LastName:" would become its own field with "Williams" as the first record, "FirstName:" would become the second field with "Dix" as the first record, etc.

I know a little about queries and no VBA experience. I'm an administrator with no tech staff to support these types of requests so I'm trying to solve it with my sophomoric experience with Access.

All ideas are very appreciated. Thanks!

~Csmith

==== the text is all in a single field called "Content" in a table called "Emails"======

Field names from your form are listed below followed by a colon and what the
user entered for a response.


LastName:
Williams


FirstName:
Dix


MI:


State:
CA


Street:
622 E Mossy Oak Ct


City:
Broadview


Zip:


45679


County:
Mortings


Employer_Present_Information:

Etc. ...

[orange]

Can you show us the value of content from a few records?
Your post indicates separate lines per item, but I doubt that's how it is in the contents field.

[andy49]

It will be the details that reall count here so I agree with orange, we need to see precise copied data. Vba seems obvious route.


Sent from my iPhone using Tapatalk

[csmith]

I tried to show it as closely as it appears in the database. It is using a line break.
Attached is a screen shot with info redacted. My first time posting an image here so I hope it displays well.

[Micron]

I think it would be possible to create an update query and in each calculated field, pass the field name to a function that finds the field name in the long string, moves past the colon and returns what's between the colon and the first line feed and/or first line return character(s). If doable, you wouldn't have to loop through the entire string, trying to find each value after the colon. In another thread today, I proved that you can update a table with a query that calls a function, so if this would be an append query, that might be possible as well.

[csmith]

@Micron, what would that look like? As I mentioned, I'm pretty novice with Access; I use it to run basic queries and reports but that's about the extent of my knowledge. Where should I begin and what would be the steps to accomplishing this? And what types of functions would I be running?

Much Thanks!

[orange]

You showed a sample in post #1. It seems you are putting some sort of title.info line at the top of the "contents" field.
Are the field names always in a specific order?
If you know these field names you could parse the contents field looking for the field names and select the values in a loop construct to get all fields for this record.

I realize you have redacted the contents in latest post, but not in your first post. It would be helpful (to me at least) if you could copy a couple of your contents fields and post them in their entirety as text. That would give us something to work with.

Do you have a standard format for where you are eventually put these extracted values? That is, you are going to split these values into columns ---and possibly insert into a table????

[csmith]

You showed a sample in post #1. It seems you are putting some sort of title.info line at the top of the "contents" field.
Are the field names always in a specific order?

Yes. The content will always be in this order; LastName: ... FirstName: ... etc. The amount of text between these will vary of course.

If you know these field names you could parse the contents field looking for the field names and select the values in a loop construct to get all fields for this record.

I do know the field names. I started down the instr function road but got stuck due to lack of Access know how; I couldn't figure out how to isolate content between the end of "LastName:", for example, and the start of "FirstName:" If I could run a function that put the contents after the ":" following "LastName:" and the "F" at the start of "FirstName:" I could just put this function in every field of my query and it would parse out the content... that's what I was hoping would be possible.

I realize you have redacted the contents in latest post, but not in your first post. It would be helpful (to me at least) if you could copy a couple of your contents fields and post them in their entirety as text. That would give us something to work with.

OK. I'll paste in an entire field below. That excerpt is a copy/paste from the Contents field, but I changed all the info for privacy of course; it gives the gist of the contents.

Do you have a standard format for where you are eventually put these extracted values? That is, you are going to split these values into columns ---and possibly insert into a table????

Yes. I want to split it out into a table where my staff can grab it and use it for their day-to-day tasks.

[csmith]

@orange

HERE IS THE COMPLETE FIELD INFO FOR THE "CONTENTS" FIELD

========================================

Field names from your form are listed below followed by a colon and what the
user entered for a response.


LastName:
W*****




FirstName:
D****




MI:






State:
CA




Street:
63**** ***Ct




City:
Bl*****




Zip:
4***1




County:
M*****




Employer_Present_Information:






DayPhone:
81*-***-**75 (could be entered differently, form doesn't have a mask)




EvePhone:






Email:
de******@gmail.com




BirthDay:
01/21/****




Gender:






EmergName:






EmergRelation:






EmerDayPhone:






EmerEvePhone:






SPNeeds:






EmrgInfo:






HowHeard:






CrseNo1:






CrseTitle1:






DayTime1:






Cost1:






CrseNo2:






CrseTitle2:






DayTime2:






Cost2:






CrseNo3:






CrseTitle3:






DayTime3:






Cost3:






TotalCost:






Comments:

[Micron]

As I envision it, the query design grid would look like Last _Name: ExtractStrings("LastName:",[ID]) for the Last_Name field. To start with, I wouldn't use a field name the same as the value we're looking for (LastName). This should pass the value LastName: and the record ID to the function. "LastName:" would be the part of your long text that identifies where to start (after the colon). It would then have to find the string in the table based on the ID that the query passes to it (say 1 for the first table row). The function would DLookup the long field string and look for "LastName:" and get the length of the value that follows. So far, so good (for me anyway). The tricky part is how to determine the length of the value "Williams". Perhaps the line feed or line return characters. I don't know yet, but I'm dabbling with it.

Your design would require that there's something to identify the table row that contains the string (such as ID) and preferably be a number (like autonumber). The string would have to be consistent (e.g. no semi-colon separators some times, colons another time, etc.). If you only want to pull 3 field values from a string, you only have 3 fields in your query, otherwise, one field for each value being pulled.

I do tend to complicate things sometimes, so if anybody has a better/easier idea, please do come forward and let's not make this much more difficult than it needs to be. I can only write about how I would tackle it. I sure wouldn't want to spend hours on this only to have someone develop something in a flash.

[aytee111]

Started...

[andy49]

Just a thought but if the lines are all the same gaps (as they appear to be), what about replacing every LineFeed with a ":" then use the split function.

The data values would then all be in the (guessing here) 4th, 8th, 12th,16th positions easily collected by a loop.

[Micron]

If you can find the line feed you can know the length of the value, so why compress the string? Just asking.

[aytee111]

I am sure there are many ways to do this - I haven't used split before, however. In the meantime, the old-fashioned way.

This reads the data as Field1 as writes to a table (just started with first 6 fields):

Public Function ExtractData()
    Dim strLabel(10) As String, strValue(10) As String, rst As Recordset, rstOut As Recordset
    Dim x As Integer, y As Integer, z As Integer
    
    strLabel(0) = "LastName"
    strLabel(1) = "FirstName"
    strLabel(2) = "MI"
    strLabel(3) = "State"
    strLabel(4) = "Street"
    strLabel(5) = "City"
        
    On Error Resume Next
    Set rst = CurrentDb.OpenRecordset("Table9", dbOpenDynaset)
    Set rstOut = CurrentDb.OpenRecordset("Table10", dbOpenDynaset)
    
    rst.MoveFirst
    Do Until rst.EOF
        rstOut.AddNew
        Do Until y > 5
            x = InStr(rst!Field1, strLabel(y)) + 3 + Len(strLabel(y))
            If y < 5 Then
                strValue(y) = Trim(Mid(rst!Field1, x, InStr(x, rst!Field1, strLabel(y + 1)) - x))
            Else
                strValue(y) = Trim(Mid(rst!Field1, x))
            End If
            strValue(y) = Replace(strValue(y), Chr(10), "")
            strValue(y) = Replace(strValue(y), Chr(13), "")
            If strValue(y) <> "" Then rstOut(strLabel(y)) = strValue(y)
            y = y + 1
        Loop
        rstOut.Update
        rst.MoveNext
    Loop
    
End Function

[aytee111]

Got the split working. However, the spaces in front of each field mess things up (e.g. space Lastname, space Williams) and these are additional array values. After this split a loop will be needed to put the values somewhere.

        strTest = Replace(rst!Field1, Chr(10), ":")
        strTest = Replace(rst!Field1, Chr(13), ":")
        testA = Split(strTest, ":")