Parse and view data in memo field

[cat]

In Access2003, I have a db with a table where one of the fields is field type
memo. This field is populated with data that appears to be a string of text.
That text string is actually multiple lines. I need to parse that data
(separated by the next line or end of line indicators which appear as a
square in the string of text) and then put each line into its own record.
When I copy and paste to excel, or export to a text file and import back in,
or even paste in this message, it pastes the way I need it.

For example:
In memo field, appears as:

1.10 Agent <> 2.52 FT: 10 Listings <>7. California - Northern and Hawaii #21

Where <> is actually the next line indicator, which appears as a square.

I want to convert it to this:

1.10 Agent
2.52 FT: 10 Listings
7. California – Northern and Hawaii #21

I was able to write code in VB to do this by manipulating the sytem; I open
a recordset in VB (the table), pull in the field, one line at a time, and for
each line, I export the data to a text file, import back in to a temporary
table, and then append to a separate table with the original client Id. This
works, howevever, when I try to feed several thousand records through, I get
a run time error.

My other thought was I could feed each line through and read each char until
I get to the next line indicator, and slplit that way, but I do not know what
to set the variable equal to to find that char (I.e., how does VB read this
"next line" indicator -the square?)

Is there a better way - there has to be!

Bottom line: How can I take each record and parse and transpose to split
each line into its own field and record?

 

[KARL DEWEY]

How can I take each record and parse and transpose to split each line into
its own field and record?
First you can not do both field and record. Field is easier. Record takes
more work after the field separation.
You need to find out what the character is that is your next line or end of
line indicators.
Split one out like this --
EndOfLine: Right(Left([YourMemo], X),1) where X is the known position
of the character in record 1.
Then determine what the ASCII of it is like this --
EndOfLine: Asc(Right(Left([YourMemo], X),1))

Then standard parsing using the ASCII character number as below for 'Return'

 

[cat]

I did not know about the Asc and Chr functions. That is exactly what I
needed to parse the string. Thank you!

its own field and record?
First you can not do both field and record. Field is easier. Record takes
more work after the field separation.
You need to find out what the character is that is your next line or end of
line indicators.
Split one out like this --
EndOfLine: Right(Left([YourMemo], X),1) where X is the known position
of the character in record 1.
Then determine what the ASCII of it is like this --
EndOfLine: Asc(Right(Left([YourMemo], X),1))

Then standard parsing using the ASCII character number as below for 'Return'
--
First_Line: Left([YourMemo],InStr([YourMemo],Chr(13))-1)

--
KARL DEWEY
Build a little - Test a little

In Access2003, I have a db with a table where one of the fields is field type
memo. This field is populated with data that appears to be a string of text.
That text string is actually multiple lines. I need to parse that data
(separated by the next line or end of line indicators which appear as a
square in the string of text) and then put each line into its own record.
When I copy and paste to excel, or export to a text file and import back in,
or even paste in this message, it pastes the way I need it.

For example:
In memo field, appears as:

1.10 Agent <> 2.52 FT: 10 Listings <>7. California - Northern and Hawaii #21

Where <> is actually the next line indicator, which appears as a square.

I want to convert it to this:

1.10 Agent
2.52 FT: 10 Listings
7. California – Northern and Hawaii #21

I was able to write code in VB to do this by manipulating the sytem; I open
a recordset in VB (the table), pull in the field, one line at a time, and for
each line, I export the data to a text file, import back in to a temporary
table, and then append to a separate table with the original client Id. This
works, howevever, when I try to feed several thousand records through, I get
a run time error.

My other thought was I could feed each line through and read each char until
I get to the next line indicator, and slplit that way, but I do not know what
to set the variable equal to to find that char (I.e., how does VB read this
"next line" indicator -the square?)

Is there a better way - there has to be!

Bottom line: How can I take each record and parse and transpose to split
each line into its own field and record?

 

[Ken Sheridan]

In Access2003, I have a db with a table where one of the fields is field type
memo. This field is populated with data that appears to be a string of text.
That text string is actually multiple lines. I need to parse that data
(separated by the next line or end of line indicators which appear as a
square in the string of text) and then put each line into its own record.
When I copy and paste to excel, or export to a text file and import back in,
or even paste in this message, it pastes the way I need it.

For example:
In memo field, appears as:

1.10 Agent <> 2.52 FT: 10 Listings <>7. California - Northern and Hawaii #21

Where <> is actually the next line indicator, which appears as a square.

I want to convert it to this:

1.10 Agent
2.52 FT: 10 Listings
7. California – Northern and Hawaii #21

I was able to write code in VB to do this by manipulating the sytem; I open
a recordset in VB (the table), pull in the field, one line at a time, and for
each line, I export the data to a text file, import back in to a temporary
table, and then append to a separate table with the original client Id. This
works, howevever, when I try to feed several thousand records through, I get
a run time error.

My other thought was I could feed each line through and read each char until
I get to the next line indicator, and slplit that way, but I do not know what
to set the variable equal to to find that char (I.e., how does VB read this
"next line" indicator -the square?)

Is there a better way - there has to be!

Bottom line: How can I take each record and parse and transpose to split
each line into its own field and record?

 

[Ken Sheridan]

Ooops! Sorry for the empty reply!

Firstly back up the data!

You can use the Replace function to split the text over several lines, each
terminate with a carriage return/line feed. As you also need to strip out
any leading or trailing spaces around the end of line character you'll need
to call it three times, so wrap it in a function like so:

Function SplitString(varString As Variant, strEndLine As String)

Dim strTemp As String

If Not IsNull(varString) Then
strTemp = varString
' first remove leading spaces before end of line character
Do While InStr(strTemp, " " & strEndLine) > 0
strTemp = Replace(strTemp, " " & strEndLine, strEndLine)
Loop

' next remove trailing spaces after end of line character
Do While InStr(strTemp, strEndLine & " ") > 0
strTemp = Replace(strTemp, strEndLine & " ", strEndLine)
Loop

' finally replace end of line character with
' carriage return/line feed
Do While InStr(strTemp, strEndLine) > 0
strTemp = Replace(strTemp, strEndLine, vbNewLine)
Loop

SplitString = strTemp

End If

End Function

Then update the table with:

UPDATE [YourTable]
SET [YourMemoField] =SplitString([YourMemoField]," <>");

In place of the <> copy and paste the actual 'square' character from one of
the original the records.

Then create a new table with a foreign key column which equates to the
primary key of the original table, so don't use an autonumber and index it
non-uniquely (accepts duplicates). The create a procedure to insert separate
rows into the new table for each line in the memo field in the original:

Sub FillNewTable()

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strTemp As String

Set dbs = CurrentDb

' first make sure new table is empty
strSQL = "DELETE * FROM [YourNewtable]"
dbs.Execute strSQL

strSQL = "SELECT [YourID],[YourMemoField] " & _
"FROM [YourOriginalTable] " & _
"WHERE [YourMemoField] IS NOT NULL"
Set rst = dbs.OpenRecordset(strSQL)

With rst
.MoveFirst
' loop through original table
Do While Not .EOF()
strTemp = .Fields("[YourMemoField]")
' if no line breaks then insert complete
' memo field into row in new table
If InStr(strTemp, vbNewLine) = 0 Then
strSQL = "INSERT INTO [YourNewtable]" & _
"([YourID],[YourMemoField]) " & _
"VALUES(" & .Fields("[YourID]") & ",""" & _
.Fields("[YourMemoField]") & """)"

dbs.Execute strSQL
.MoveNext
Else
' loop through memo field until
' contains no more line breaks
Do While True
' insert text up to line break into
' new row in new table
If InStr(strTemp, vbNewLine) > 0 Then
strSQL = "INSERT INTO [YourNewtable]" & _
"([YourID],[YourMemoField]) " & _
"VALUES(" & .Fields("[YourID]") & ",""" & _
Left(strTemp, InStr(strTemp, vbNewLine) - 1) & """)"

dbs.Execute strSQL

strTemp = Mid(strTemp, InStr(strTemp, vbNewLine) + 1)
Else
' insert text after final line break into
' new row in new table
strSQL = "INSERT INTO [YourNewtable]" & _
"([YourID],[YourMemoField]) " & _
"VALUES(" & .Fields("[YourID]") & ",""" & _
strTemp & """)"
dbs.Execute strSQL
Exit Do
End If
Loop
.MoveNext
End If
Loop
.Close
End With

End Sub

In the above YourOriginalTable and YourNewTable are the table names, YourID
is the primary key of the former (create one with an autonumber column if it
doesn't aerated have a primary key column) and also of the corresponding
foreign key in the latter, and YourMemoField is the name of the memo field in
each.

Once you've run the procedure and filled the new table you can then relate
them on the YourID columns.

Ken Sheridan
Stafford, England