[AccessD] Parsing Variable Length Text String with VBA - Somebody didn't understand Normalization

Jim Dettman jimdettman at verizon.net
Wed Mar 7 15:14:50 CST 2012


Brad,

<< I was curious if there was some sort of slick trick
to do this. >>

 Split() is probably your best bet.  But as an alternative, below is the
routine I use to return a string from within a string.  You may need to
modify it a bit.

  I was storing data like this:

::CDD::<tag>::<value>CRLF

 And I knew in advance what the tag value was. Ie.:

::CDD::LABELMSG::

 The deal here was this client had customers sending in a variety of
customer defined data, which needed to be returned in various EDI documents,
printed on labels or packing slips, etc.  The data varied from customer to
customer in terms of what it was and the amount of (some would have one
piece of data, others eight, etc).

 This emulates what's called a Entity Attribute Value (EAV) design.  It
really should be broken out into a table rather then stored in a string, but
sometimes shortcuts are required<g>.

  So as long as you know what the tags are before hand, then you can call
this routine from a query and let it extract the value for the given "tag"
for you and dump that in a table.

  But if you don't know all the tags up front, then I would stick with
split.

  Beyond that, you might want to consider using the EAV design, which would
be a table like this:

ID - PK Autonumber
<foreign Key>
Tag - Text
TagValue - Text

 and save them out that way.

Jim.


Function ExtractDataFromNotes(strTag As String, strDelimiter As String,
varData As Variant) As Variant

          ' Return embedded strings from another string.
          Dim lngPos As Long

10        ExtractDataFromNotes = ""

20        If strDelimiter = "" Then strDelimiter = vbCrLf
30        varData = Nz(varData, "")

40        If strTag <> "" Then
50            lngPos = InStr(1, varData, strTag)
60            If lngPos > 0 Then
70                ExtractDataFromNotes = Mid$(varData, lngPos)
80                lngPos = InStr(1, ExtractDataFromNotes, strDelimiter)
90                If lngPos > 0 Then
100                   ExtractDataFromNotes = left$(ExtractDataFromNotes,
lngPos - 1)
110                   ExtractDataFromNotes = Mid$(ExtractDataFromNotes,
Len(strTag) + 1)
120                   If ExtractDataFromNotes = "" Then ExtractDataFromNotes
= Null
130               End If
140           End If
150       End If

End Function 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Brad Marks
Sent: Wednesday, March 07, 2012 03:51 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Parsing Variable Length Text String with VBA -
Somebody didn't understand Normalization

Mark,

1. Yes, all of the pieces of data in the text string are all in one
string in one record.
  
2. The list of pieces of data in the text string is finite.  There will
never be more than a specific number of them.  We should be able to
count on consistent "eye-catchers" (tokens) such as the headers and the
brackets.

I believe that with some work, we can break the big text field down into
30 little fields.  I was curious if there was some sort of slick trick
to do this. 

Thanks,
Brad

PS.  Your suggestion to "drink and regroup" doesn't sound like a bad
idea  :-)  



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark A Matte
Sent: Wednesday, March 07, 2012 2:29 PM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Parsing Variable Length Text String with VBA -
Somebody didn't understand Normalization


I have worked with something like this before.
 
Now for what may seem like a silly question...in the text string...is it
only 1 record(do all of the fields belong, 1 to 1 with the record they
are attached)?  The data I was dealing with had a text field,
representing multiple records as a subset of the record it belonged to.
 
Second question...not so silly...you stated 10 to 30 pieces of
data...are there only 30...do you have a unique list of
possibilities...and are they ALWAYS formatted 
the way you show below: "header_name ["  ?
 
If the first question is 'yes, only 1 record'...and the second is
'yes'...then I would build a list of of the header_names, and loop
through the way you described below...for each header find position of
"header[" and pull all until next "]".
 
If the answer to the first is 'No'...we will need more to drink and
regroup.
 
Hope that helps.
 
mark
 
 
 
 
 

> Date: Wed, 7 Mar 2012 09:13:49 -0600
> From: BradM at blackforestltd.com
> To: accessd at databaseadvisors.com
> Subject: [AccessD] Parsing Variable Length Text String with VBA -
Somebody didn't understand Normalization
> 
> All,
> 
> We are using Access 2007 to pull data from a purchased package SQL
> Server database. 
> 
> One of the fields that we are working with is a long text string that
> actually contains multiple pieces of data that we would like to work
> with on an individual level.
> 
> Here is an example of how the pieces of data are found in the text
> string.
> 
> State [Florida] Country [USA] Color [Green] Division [Southeast] ...
> etc.
> 
> There can be from 10 to 30 individual pieces of data contained in the
> text string.
> 
> Ideally each of these pieces of data would be individual fields, but
we
> cannot change how the data is stored in the purchased package.
> 
> We currently use Access 2007 to present this data to our users. This
> works nicely for the most part. 
> 
> Recently one of our managers has requested that we change the color to
> red for all of the pieces of data that are contained in the brackets.
> In other words, if we have the string 
> 
> State [Florida] Country [USA] Color [Green] Division [Southeast]
> 
> the words Florida, USA, Green, and Southeast would be shown in red on
> Access reports.
> 
> 
> 
> What further complicates all of this is the fact that there is a
> variable number of pieces of data. In this example, there are 4 pieces
> of data, but there can be up to 30 or maybe even more in the future.
> 
> We have done some experimenting with parsing the big text field and
> creating small fields by looking for the brackets and the data between
> the brackets - [Florida] for example . This will probably work, but
> before we go further with this approach I wanted to see if anyone else
> has ever run into something like this.
> 
> Thanks for your help,
> 
> Brad 
> 
> PS. The data cannot be stored in Rich Text Format - it currently can
> only be stored as simple text. 
> 
> PSS. I have an extra copy of a book on database normalization that I
> may send to the software vendor :-) 
> 
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
 		 	   		  
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



More information about the AccessD mailing list