Read Text table

[AccessNow]

I have an access db that has a table with 3 fields in it and in the last
field it is all text with delimitors.
My question is how do I read this field (PERFDATA), so that I can separate
out the data to print on reports?
I enclosed two records below both starting with WONUM. Near as I can tell,
looking at the output, the numbers with a | right after them, I'll need to
read another table with that number to get its description to print. If it
doesnt have a | right after it, that actually prints on the report, like the
word PASS and .25, etc.

WONUM TASKNUM
0000023104 0003

PERFDATA
"""~"""" ""5474|""PASS""""~""""""""~""""COMMENTS:
""""GOOD""""~""15|""""""~""16|"".25""""HOURS""""~"""


WONUM TASKNUM
0000023104 0003

PERFDATA
"6006|""5""""~""""~""""SEQUENCE:""5473|""DATE:""7688|""TIME:""""12:47:
50""""~""""~""""OP CODE:""""KEA""""~""""~""2|""~"""" TYPE:""5591|""MANF:
""5466|""LOC:""5467|""~"""" MODEL:""5468|""SERIAL #:
""""""""~""5469|""SRD009""""~""""~""8|""~""""""""~"""" """"~""""~""""
1""5518|""STORED""""~"""" 2""5600|""STORED"""


Thank You

TextMan

 

[Guest]

If these are real records I see a problem in that there a different number of
delimiters in each. I think it will be impossible to parse the data.

Another thing, how to know what table, what field, and what record to fill
in the information?

 

[AccessNow via AccessMonster.com]

Karl,
I can read the table in Access and come up with the right records.. I just
havent found a way yet to automate separating that one text field using the
delimitors. I have been able to separate them calling them into Excel
so Im guessing there is a way in Access also, it's just beyond my expertise
right now.....

Thx

Bob

If these are real records I see a problem in that there a different number of
delimiters in each. I think it will be impossible to parse the data.

Another thing, how to know what table, what field, and what record to fill
in the information?

I have an access db that has a table with 3 fields in it and in the last
field it is all text with delimitors.

[quoted text clipped - 26 lines]

TextMan

 

[John Nurick]

It's only possible to parse data out of strings like this if you know
the rules - but if you can specify the rules it's possible to extract
the data.

If I understand you right, you have a table that contains a field
PERFDATA which contains values something like this

"""~"""" ""5474|""PASS""""~""""""""~""""COMMENTS:
""""GOOD""""~""15|""""""~""16|"".25""""HOURS""""~"""

and this

"6006|""5""""~""""~""""SEQUENCE:""5473|""DATE:""7688|
""TIME:""""12:47:50""""~""""~""""OP CODE:""""KEA""""~
""""~""2|""~"""" TYPE:""5591|""MANF:""5466|""LOC:""
5467|""~"""" MODEL:""5468|""SERIAL #:""""""""~
""5469|""SRD009""""~""""~""8|""~""""""""~""""
""""~""""~""""1""5518|""STORED""""~"""" 2""5600|
""STORED"""


You say this is "text with delimiters" but you don't say which
characters are the delimiters and what role the " characters play. In
addition, the newsgroup software has introduced linebreaks which are
probably in misleading places. If you can do the following, I'll try to
help with a way of parsing the strings:

1) Re-present the two samples, but this time in a way that leaves no
room for doubt about the actual contents of the PERFDATA field.

2) Explain about the delimiters and what all those multiple quote marks
are doing.

3) For each sample, show exactly what you expect to get out of it (the
field values, their names if known, and the substrings of the PERFDATA
value to which they correspond).

 

[AccessNow via AccessMonster.com]

ok ..in this example:
"6006|""5""""~""""~""""SEQUENCE:""5473|""DATE:""7688|
""TIME:""""12:47:50""""~""""~""""OP CODE:""""KEA""""~
""""~""2|""~"""" TYPE:""5591|""MANF:""5466|""LOC:""
5467|""~"""" MODEL:""5468|""SERIAL #:""""""""~
""5469|""SRD009""""~""""

It takes any number before a | and reads a another table.. so like it'll take
6006 and read a table to get a description of MedTester Rec # and take 5473
and reads a table to get CHECKLIST and takes 7688 and reads a table and gets
05/23/06 etc. and prints out a report. The report looks like:

MedTester Rec # 5

SEQUENCE: CHECKLIST DATE: 05/23/06 TIME: 12:47:50

OP CODE: KEA

DEVICE INFORMATION
TYPE: DEFIB 9A MANF:
LOC:
MODEL: SERIAL:
CONTROL #: SRD009

It's only possible to parse data out of strings like this if you know
the rules - but if you can specify the rules it's possible to extract
the data.

If I understand you right, you have a table that contains a field
PERFDATA which contains values something like this

"""~"""" ""5474|""PASS""""~""""""""~""""COMMENTS:
""""GOOD""""~""15|""""""~""16|"".25""""HOURS""""~"""

and this

"6006|""5""""~""""~""""SEQUENCE:""5473|""DATE:""7688|
""TIME:""""12:47:50""""~""""~""""OP CODE:""""KEA""""~
""""~""2|""~"""" TYPE:""5591|""MANF:""5466|""LOC:""
5467|""~"""" MODEL:""5468|""SERIAL #:""""""""~
""5469|""SRD009""""~""""~""8|""~""""""""~""""
""""~""""~""""1""5518|""STORED""""~"""" 2""5600|
""STORED"""


You say this is "text with delimiters" but you don't say which
characters are the delimiters and what role the " characters play. In
addition, the newsgroup software has introduced linebreaks which are
probably in misleading places. If you can do the following, I'll try to
help with a way of parsing the strings:

1) Re-present the two samples, but this time in a way that leaves no
room for doubt about the actual contents of the PERFDATA field.

2) Explain about the delimiters and what all those multiple quote marks
are doing.

3) For each sample, show exactly what you expect to get out of it (the
field values, their names if known, and the substrings of the PERFDATA
value to which they correspond).

I have an access db that has a table with 3 fields in it and in the last
field it is all text with delimitors.

[quoted text clipped - 26 lines]

TextMan

 

[AccessNow via AccessMonster.com]

Almost looks like every ~ is a new line and every " is a space??

It's only possible to parse data out of strings like this if you know
the rules - but if you can specify the rules it's possible to extract
the data.

If I understand you right, you have a table that contains a field
PERFDATA which contains values something like this

"""~"""" ""5474|""PASS""""~""""""""~""""COMMENTS:
""""GOOD""""~""15|""""""~""16|"".25""""HOURS""""~"""

and this

"6006|""5""""~""""~""""SEQUENCE:""5473|""DATE:""7688|
""TIME:""""12:47:50""""~""""~""""OP CODE:""""KEA""""~
""""~""2|""~"""" TYPE:""5591|""MANF:""5466|""LOC:""
5467|""~"""" MODEL:""5468|""SERIAL #:""""""""~
""5469|""SRD009""""~""""~""8|""~""""""""~""""
""""~""""~""""1""5518|""STORED""""~"""" 2""5600|
""STORED"""


You say this is "text with delimiters" but you don't say which
characters are the delimiters and what role the " characters play. In
addition, the newsgroup software has introduced linebreaks which are
probably in misleading places. If you can do the following, I'll try to
help with a way of parsing the strings:

1) Re-present the two samples, but this time in a way that leaves no
room for doubt about the actual contents of the PERFDATA field.

2) Explain about the delimiters and what all those multiple quote marks
are doing.

3) For each sample, show exactly what you expect to get out of it (the
field values, their names if known, and the substrings of the PERFDATA
value to which they correspond).

I have an access db that has a table with 3 fields in it and in the last
field it is all text with delimitors.

[quoted text clipped - 26 lines]

TextMan

 

[John Nurick]

Almost looks like every ~ is a new line and every " is a space??

You're the one who can see the data: you tell me. It seems very
unlikely, however, because the sample values you have posted contain
spaces: this suggests that a " and a space are not the same thing. Even
if they are, why are there different numbers of """ in different places?

For now, ignore the business of looking up stuff in the other table. We
just need to understand the rules for splitting the values in this
PERFDATA field into their component pieces, and the meaning of each
piece. You need to be able to take a value (e.g. your sample

"6006|""5""""~""""~""""SEQUENCE:""5473|""DATE:""7688|
""TIME:""""12:47:50""""~""""~""""OP CODE:""""KEA""""~
""""~""2|""~"""" TYPE:""5591|""MANF:""5466|""LOC:""
5467|""~"""" MODEL:""5468|""SERIAL #:""""""""~
""5469|""SRD009""""~""""

and explain the significance of every single character in it. For
example, is the first "piece"
6006|
or
"6006|"
If the first "piece" is
"6006|"
and the second is
"5"
, the third is presumably
""
.. What does that mean?

It's only possible to parse data out of strings like this if you know
the rules - but if you can specify the rules it's possible to extract
the data.

If I understand you right, you have a table that contains a field
PERFDATA which contains values something like this

"""~"""" ""5474|""PASS""""~""""""""~""""COMMENTS:
""""GOOD""""~""15|""""""~""16|"".25""""HOURS""""~"""

and this

"6006|""5""""~""""~""""SEQUENCE:""5473|""DATE:""7688|
""TIME:""""12:47:50""""~""""~""""OP CODE:""""KEA""""~
""""~""2|""~"""" TYPE:""5591|""MANF:""5466|""LOC:""
5467|""~"""" MODEL:""5468|""SERIAL #:""""""""~
""5469|""SRD009""""~""""~""8|""~""""""""~""""
""""~""""~""""1""5518|""STORED""""~"""" 2""5600|
""STORED"""


You say this is "text with delimiters" but you don't say which
characters are the delimiters and what role the " characters play. In
addition, the newsgroup software has introduced linebreaks which are
probably in misleading places. If you can do the following, I'll try to
help with a way of parsing the strings:

1) Re-present the two samples, but this time in a way that leaves no
room for doubt about the actual contents of the PERFDATA field.

2) Explain about the delimiters and what all those multiple quote marks
are doing.

3) For each sample, show exactly what you expect to get out of it (the
field values, their names if known, and the substrings of the PERFDATA
value to which they correspond).

I have an access db that has a table with 3 fields in it and in the last
field it is all text with delimitors.

[quoted text clipped - 26 lines]

TextMan

 

[AccessNow via AccessMonster.com]

OK... a very little history of this data. This database is created from a
very old system called Sentenel, that no one seems to know anything about and
there are no docs. So I am trying to take the 20+ tables that are created
from this system and create an Access system, that has worked thus far until
this text field problem.
Even tho ACCESS didnt create these tables, I have been able to recreate much
of this system in ACCESS, reading these tables. So YES, Im guessing at what
things mean by looking at the output. I believe you may be right about the "
wrapping aroung each piece like "6006|" etc. The extra quotes dont show up or
seem to mean much on the output. The ~ means next line. So following that
logic, once fields are broke out I will ignore the ", if thats all there is
in that field.

Thanks

Bob

Almost looks like every ~ is a new line and every " is a space??

You're the one who can see the data: you tell me. It seems very
unlikely, however, because the sample values you have posted contain
spaces: this suggests that a " and a space are not the same thing. Even
if they are, why are there different numbers of """ in different places?

For now, ignore the business of looking up stuff in the other table. We
just need to understand the rules for splitting the values in this
PERFDATA field into their component pieces, and the meaning of each
piece. You need to be able to take a value (e.g. your sample

"6006|""5""""~""""~""""SEQUENCE:""5473|""DATE:""7688|
""TIME:""""12:47:50""""~""""~""""OP CODE:""""KEA""""~
""""~""2|""~"""" TYPE:""5591|""MANF:""5466|""LOC:""
5467|""~"""" MODEL:""5468|""SERIAL #:""""""""~
""5469|""SRD009""""~""""

and explain the significance of every single character in it. For
example, is the first "piece"
6006|
or
"6006|"
If the first "piece" is
"6006|"
and the second is
"5"
, the third is presumably
""
. What does that mean?

It's only possible to parse data out of strings like this if you know
the rules - but if you can specify the rules it's possible to extract [quoted text clipped - 43 lines]

Please respond in the newgroup and not by email.

 

[John Nurick]

Happy to help with inferring the rules and then implementing them in
code - but you're the only one who can see the data and work out how
many pieces each value splits into!

OK... a very little history of this data. This database is created from a
very old system called Sentenel, that no one seems to know anything about and
there are no docs. So I am trying to take the 20+ tables that are created
from this system and create an Access system, that has worked thus far until
this text field problem.
Even tho ACCESS didnt create these tables, I have been able to recreate much
of this system in ACCESS, reading these tables. So YES, Im guessing at what
things mean by looking at the output. I believe you may be right about the "
wrapping aroung each piece like "6006|" etc. The extra quotes dont show up or
seem to mean much on the output. The ~ means next line. So following that
logic, once fields are broke out I will ignore the ", if thats all there is
in that field.

Thanks

Bob

Almost looks like every ~ is a new line and every " is a space??

You're the one who can see the data: you tell me. It seems very
unlikely, however, because the sample values you have posted contain
spaces: this suggests that a " and a space are not the same thing. Even
if they are, why are there different numbers of """ in different places?

For now, ignore the business of looking up stuff in the other table. We
just need to understand the rules for splitting the values in this
PERFDATA field into their component pieces, and the meaning of each
piece. You need to be able to take a value (e.g. your sample

"6006|""5""""~""""~""""SEQUENCE:""5473|""DATE:""7688|
""TIME:""""12:47:50""""~""""~""""OP CODE:""""KEA""""~
""""~""2|""~"""" TYPE:""5591|""MANF:""5466|""LOC:""
5467|""~"""" MODEL:""5468|""SERIAL #:""""""""~
""5469|""SRD009""""~""""

and explain the significance of every single character in it. For
example, is the first "piece"
6006|
or
"6006|"
If the first "piece" is
"6006|"
and the second is
"5"
, the third is presumably
""
. What does that mean?

It's only possible to parse data out of strings like this if you know
the rules - but if you can specify the rules it's possible to extract

[quoted text clipped - 43 lines]

Please respond in the newgroup and not by email.

 

[AccessNow via AccessMonster.com]

Ok so assuming that " is the deliminter, how do I separate the data out in
that one text field.... like "6006|" etc. The extra quotes dont show up or
seem to mean much on the output. The ~ means next line. So following that
logic, once fields are broke out I will ignore the " or spaces, if thats all
there is
in that field. That will seem to work if I can get them separated.

Happy to help with inferring the rules and then implementing them in
code - but you're the only one who can see the data and work out how
many pieces each value splits into!

OK... a very little history of this data. This database is created from a
very old system called Sentenel, that no one seems to know anything about and

[quoted text clipped - 54 lines]

 

[John Nurick]

Ok so assuming that " is the deliminter, how do I separate the data out in
that one text field.... like "6006|" etc. The extra quotes dont show up or
seem to mean much on the output. The ~ means next line. So following that
logic, once fields are broke out I will ignore the " or spaces, if thats all
there is
in that field. That will seem to work if I can get them separated.

Are you sure? One of the example values you posted was

"6006|""5""""~""""~""""SEQUENCE:""5473|""DATE:""7688|
""TIME:""""12:47:50""""~""""~""""OP CODE:""""KEA""""~
""""~""2|""~"""" TYPE:""5591|""MANF:""5466|""LOC:""
5467|""~"""" MODEL:""5468|""SERIAL #:""""""""~
""5469|""SRD009""""~""""

If the field delimiter (separator) is ", this translates into the
following. Each field has a line to itself, and this
<empty>
represents an empty field (i.e. the value between two adjacent "s):

<empty>
6006|
<empty>
5
<empty>
<empty>
<empty>
~
<empty>
<empty>
<empty>
~
<empty>
<empty>
<empty>
SEQUENCE:
<empty>
5473|
<empty>
DATE:
<empty>
7688|
<empty>
TIME:
<empty>
<empty>
<empty>
12:47:50
<empty>
<empty>
<empty>
~
<empty>
<empty>
<empty>
~
<empty>
<empty>
<empty>
OP CODE:
<empty>
<empty>
<empty>
KEA
<empty>
<empty>
<empty>
~
<empty>
<empty>
<empty>
~
<empty>
2|
<empty>
~
<empty>
<empty>
<empty>
TYPE:
<empty>
5591|
<empty>
MANF:
<empty>
5466|
<empty>
LOC:
<empty>
5467|
<empty>
~
<empty>
<empty>
<empty>
MODEL:
<empty>
5468|
<empty>
SERIAL #:
<empty>
<empty>
<empty>
<empty>
<empty>
<empty>
<empty>
~
<empty>
5469|
<empty>
SRD009
<empty>
<empty>
<empty>
~


If we ignore multiple "s, your example translates into:

<empty>
6006|
5
~
~
SEQUENCE:
5473|
DATE:
7688|
TIME:
12:47:50
~
~
OP CODE:
KEA
~
~
2|
~
TYPE:
5591|
MANF:
5466|
LOC:
5467|
~
MODEL:
5468|
SERIAL #:
~
5469|
SRD009
~

Both these look unpromising to me, because I have no clue what each
piece means or how they relate. But they make sense to you, great. To
split the values up this way, if necessary use the VBA Replace()
function to replace multiple "s with a single " . Then use the Split()
function with " as the delimiter; this returns an array in which each
element contains one item from the field value.

 

[AccessNow via AccessMonster.com]

That is exactly how I need the data split out.... but not knowing Access
real well, how do I use the code you provided in Access database.... The
table is laid out such as the first field WO = 0000008, the next field is
TASKNO = 0002 and then PERFDATA = the text data

Thank You

Bob

 

[John Nurick]

You mentioned earlier that if an element of PERFDATA consists of a
string of digits followed by a |, it is necessary to look the number up
in another table and retrieve a string value to use instead.

You also said you want to use the result in a report. I suppose the
first question is, (a) do you want PERFDATA to be broken up, have the
values substituted, and then be reassembled into a single string that
can be used as the datasource of a textbox on the report?

Or (b) do you want to split PERFDATA, substitute the values, and store
the resulting elements in a related table for easy use in future. In
this case you'd use a sub-report to display the elements related to each
record in the main table.

Either way needs a significant amount of not-absolutely-simple VBA code.
How much programming experience do you have?

 

[AccessNow via AccessMonster.com]

My programming knowledge is nil unless u count Cobol years ago..., but as far
as VBA..nope
I would like to create a button to click on, that basically does option (b)...
split the data out to another table.

You mentioned earlier that if an element of PERFDATA consists of a
string of digits followed by a |, it is necessary to look the number up
in another table and retrieve a string value to use instead.

You also said you want to use the result in a report. I suppose the
first question is, (a) do you want PERFDATA to be broken up, have the
values substituted, and then be reassembled into a single string that
can be used as the datasource of a textbox on the report?

Or (b) do you want to split PERFDATA, substitute the values, and store
the resulting elements in a related table for easy use in future. In
this case you'd use a sub-report to display the elements related to each
record in the main table.

Either way needs a significant amount of not-absolutely-simple VBA code.
How much programming experience do you have?

That is exactly how I need the data split out.... but not knowing Access
real well, how do I use the code you provided in Access database.... The

[quoted text clipped - 169 lines]

 

[John Nurick]

This is air code. See if you can work out what it does! The procedure
AppendPerf() uses the function PerfSplit() to actually split the value
from the PERFDATA field in MainTable into the individual chunks to be
appended to RelatedTable. Records in the two tables are related on the
common field WONUM.


Sub AppendPerf()
Dim dbD As DAO.Database
Dim rsMaster As DAO.Recordset
Dim rsRelated As DAO.Recordset
Dim strWoNum As String
Dim strPerfData As String
Dim arPerfDataValues As Variant
Dim j As Long


'Open recordsets on the two tables
Set dbD = CurrentDb()
Set rsMaster = dbD.OpenRecordset("MainTable")
Set rsRelated = dbD.OpenRecordset("RelatedTable")

'Iterate through main recordset
Do Until rsMaster.EOF
strWoNum = rsMaster.Fields("WONUM").Value
strPerfData = rsMaster.Fields("PERFDATA").Value
arPerfDataValues = PerfSplit(strPerfData)

'Iterate through PerfData values
'adding new records to the related table
For j = 0 To UBound(arPerfDataValues)
With rsRelated
.AddNew
.Fields("WONUM").Value = strWoNum
.Fields("PerfValue").Value = arPerfDataValues(j)
.Update
End With
Next j

Loop

rsMaster.Close
rsRelated.Close

End Sub



Public Function PerfSplit(PerfData As Variant) As Variant
Dim S As String
Dim oRE As Object

If IsNull(PerfData) Then
PerfSplit = Null
Exit Function
End If

S = Cstr(PerfData)

'replace multiple quote marks
Set oRE = CreateObject("Vbscript.Regexp")
With oRE
.Pattern = """+"
.Global = True
S = .Replace(S, """")
End With

'Split using " as delimiter
PerfSplit = Split(S, """")

End Function

My programming knowledge is nil unless u count Cobol years ago..., but as far
as VBA..nope
I would like to create a button to click on, that basically does option (b)...
split the data out to another table.

You mentioned earlier that if an element of PERFDATA consists of a
string of digits followed by a |, it is necessary to look the number up
in another table and retrieve a string value to use instead.

You also said you want to use the result in a report. I suppose the
first question is, (a) do you want PERFDATA to be broken up, have the
values substituted, and then be reassembled into a single string that
can be used as the datasource of a textbox on the report?

Or (b) do you want to split PERFDATA, substitute the values, and store
the resulting elements in a related table for easy use in future. In
this case you'd use a sub-report to display the elements related to each
record in the main table.

Either way needs a significant amount of not-absolutely-simple VBA code.
How much programming experience do you have?

That is exactly how I need the data split out.... but not knowing Access
real well, how do I use the code you provided in Access database.... The

[quoted text clipped - 169 lines]

Please respond in the newgroup and not by email.