Parsing Memo field

[Guest]

I have a linked database. One of the fields is a memo field containing several data elements that I can't use in its present form. Every record consists of at least three elements, but some have more. None of the elements are a uniform length (so I can't use Left, Middle or Right functions). Some of the records consist of portions I would like in a separate record. I don't mind creating a new table to use the data as long as I can set up the query so I can rerun it when I refresh the linked table

Ex
[L=ENGLISH][R=PRINCIPAL]This is the text sentence.[R=WITNESS]This is a different sentence.[R=Role1]This is the male sentence for Role1.&!$This is the female sentence for Role1

Each record ID will have one of these memo strings.

I want a query or table with these fields
- SentenceID, Language (what is between "L=" and "]"
- Role (what is between "R=" and "]"
- MaleSentence (what is between the trailing Role "]" and "&!$"
- FemaleSentence (what is between the "&!$" and the next "[R="

If there are multiple Roles, I want to create a new record with the same SentenceID and the other fields for each role

Any ideas

I know this is long, but I hope it is clear. Thanks for any help
Laurie

 

[John Vinson]

I have a linked database. One of the fields is a memo field containing several data elements that I can't use in its present form. Every record consists of at least three elements, but some have more. None of the elements are a uniform length (so I can't use Left, Middle or Right functions). Some of the records consist of portions I would like in a separate record. I don't mind creating a new table to use the data as long as I can set up the query so I can rerun it when I refresh the linked table.

Ex:
[L=ENGLISH][R=PRINCIPAL]This is the text sentence.[R=WITNESS]This is a different sentence.[R=Role1]This is the male sentence for Role1.&!$This is the female sentence for Role1.

Each record ID will have one of these memo strings.

I want a query or table with these fields:
- SentenceID, Language (what is between "L=" and "]")
- Role (what is between "R=" and "]")
- MaleSentence (what is between the trailing Role "]" and "&!$")
- FemaleSentence (what is between the "&!$" and the next "[R=".

If there are multiple Roles, I want to create a new record with the same SentenceID and the other fields for each role.

Any ideas?

I know this is long, but I hope it is clear. Thanks for any help,
Laurie

It's clear enough; it'll require some VBA code to parse it. In
essence, you'ld read the entire Memo into a string variable and use
InStr() to find the delimiters. The code would open a Recordset based
on the table (with the fields you've named) into which to write the
data.

I'd really need to sit down with some sample data to get the code so
it would work cleanly. For one thing, I worry about the fact that ANY
typo - or network noise corruption - in one of the delimiters, or any
error in the sequence, could get things really off track!

 

[Guest]

-----Original Message-----
<snip>

L=ENGLISH][R=PRINCIPAL]This is the text sentence.
[R=WITNESS]This is a different sentence.[R=Role1]This is
the male sentence for Role1.&!$This is the female
sentence for Role1.

Each record ID will have one of these memo strings.

I want a query or table with these fields:
- SentenceID, Language (what is between "L=" and "]")
- Role (what is between "R=" and "]")
- MaleSentence (what is between the trailing Role "]" and "&!$")
- FemaleSentence (what is between the "&!$" and the

next "[R=".

<snip>

It's clear enough; it'll require some VBA code to parse it. In
essence, you'ld read the entire Memo into a string variable and use
InStr() to find the delimiters. The code would open a Recordset based
on the table (with the fields you've named) into which to write the
data.

I'd really need to sit down with some sample data to get the code so
it would work cleanly. For one thing, I worry about the fact that ANY
typo - or network noise corruption - in one of the delimiters, or any
error in the sequence, could get things really off track!


.

Hi John,
Thanks for pointing me in the right direction. I'd like
to play with the VBA code if you could easily give me a
starting one with the sentence I inlcuded in the original
post (you could just copy the [Role1] portion to a
[Role2] to test the add record portion). (And tell me
where this code would go! <g>)

Or I could send a sample database.

Thanks again for your help,
Laurie

 

[John Vinson]

Hi John,
Thanks for pointing me in the right direction. I'd like
to play with the VBA code if you could easily give me a
starting one with the sentence I inlcuded in the original
post (you could just copy the [Role1] portion to a
[Role2] to test the add record portion). (And tell me
where this code would go! <g>)

[L=ENGLISH][R=PRINCIPAL]This is the text sentence.
[R=WITNESS]This is a different sentence.[R=Role1]This is
the male sentence for Role1.&!$This is the female
sentence for Role1.You'ld create a new Module, name it modParse or something like that,
and edit a Sub into it:

Public Sub ParseMemo(strMemo As String)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim iPos As Integer
Dim jPos As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("TargetTable", dbOpenDynaset)
<some looping code that I'm not sure how best to handle>
rs.AddNew
iPos = InStr(strMemo, "[L=") + 3 ' start of SentenceID
jPos = InStr(iPos, strMemo, "]") - 1 ' end of SentenceID
rs!SentenceID = Mid(strMemo, iPos, jPos) ' extract SentenceID
strMemo = Mid(strMemo, jPos + 1) ' trim off text already processed
iPos = InStr(strMemo, "[R=") + 3 ' find Role; should be equal to 4
' You may want to flag an error if not
jPos = InStr(iPos, strMemo, "]") ' end of Role
rs!Role1 = Mid(strMemo,iPos,jPos)

etc. etc.

You could make this code more clever by calling a second function to
get the next "token"... there's a lot of repetition coming in this
brute-force approach.

Or I could send a sample database.

Well... I'd need a consulting contract for that. Small one but it's
more than I'd be comfortable doing on a volunteer basis.

 

[Laurie]

Thanks John,

I'll play with the code you suggested. Not worth a
consulting fee just yet. Thanks for your help.

Laurie

-----Original Message-----

Hi John,
Thanks for pointing me in the right direction. I'd like
to play with the VBA code if you could easily give me a
starting one with the sentence I inlcuded in the original
post (you could just copy the [Role1] portion to a
[Role2] to test the add record portion). (And tell me
where this code would go! <g>)

[L=ENGLISH][R=PRINCIPAL]This is the text sentence.
[R=WITNESS]This is a different sentence.[R=Role1]This is
the male sentence for Role1.&!$This is the female
sentence for Role1.You'ld create a new Module, name it modParse or something like that,
and edit a Sub into it:

Public Sub ParseMemo(strMemo As String)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim iPos As Integer
Dim jPos As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("TargetTable", dbOpenDynaset)
<some looping code that I'm not sure how best to handle>
rs.AddNew
iPos = InStr(strMemo, "[L=") + 3 ' start of SentenceID
jPos = InStr(iPos, strMemo, "]") - 1 ' end of SentenceID
rs!SentenceID = Mid(strMemo, iPos, jPos) ' extract SentenceID
strMemo = Mid(strMemo, jPos + 1) ' trim off text already processed
iPos = InStr(strMemo, "[R=") + 3 ' find Role; should be equal to 4
' You may want to flag an error if not
jPos = InStr(iPos, strMemo, "]") ' end of Role
rs!Role1 = Mid(strMemo,iPos,jPos)

etc. etc.

You could make this code more clever by calling a second function to
get the next "token"... there's a lot of repetition coming in this
brute-force approach.

Or I could send a sample database.

Well... I'd need a consulting contract for that. Small one but it's
more than I'd be comfortable doing on a volunteer basis.


.