Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Parse data of varying lengths in MS Access

Posted on 2011-03-03
Medium Priority
Last Modified: 2013-11-29
I need help in parsing data from one field into multiple fields.  I've tried different mid/len/first/last statements and none have worked.

Example of the data in the field:





The data can be any length -- one character or a hundred characters -- No change is needed for the fields that are less than 100 characters
The fields that have characters over 100 I need to parse them into separate fields so that I can join the fields for other analysis and MS Access
will not let you join fields over 255 characters which is the reason I need to break it up.

I'm tracking how many users click a link but I can't compare usage on any one link because of the inability to join the fields that are over 255 but if I could parse the data
I could get the other logic to work.
Question by:TCCC_KO
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2

Author Comment

ID: 35028491
The first example came across on 3 lines but it's actually one long string of data
LVL 12

Expert Comment

ID: 35028784
I think you need to provide a bit more information about how fields are delimited within the string.
It's not clear to me what your expected results should be with the supplied example input.

For anything complex like this,  I would use access VB to break out the values in the string.    You could then write the values into a table for use in queries etc.

Author Comment

ID: 35029139
The fields are delimited with "/" and the character length can be 5 characters to 260 characters.

Field 1: (this is all in one field without any return characters)

EXPECTED OUTPUT:  Field 1:  irj/?NavTarget=ROLES://portal_content/com/com.ko.ep/com.ko.ep.sec
Field 2:  /com.pi.roles/com.ko.ep.ep_roles/com.ko.ep.role_sup/com.pi.career/perfor/com.ko.ep._plans

Field 2:

EXPECTED OUTPUT:  Field 1:  /rety56//
Field 2:  NULL

I want to be able to join two tables on Field 1 and Field 2 and pull in other data elements as needed but I have to be able to join them.  right now I can't join b/c this field is imported into Access with a field type of 'Memo' and it has to be a field type of 'Text' so that I can create queries and join the field as needed.  The fields that have more than 255 characters are preventing me from doing this so I determined if I parse the data into two (or more) fields, then I can successfully import as text and run queries as needed.  
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 11

Expert Comment

ID: 35029250
Well you could feed Field1 into the SPlit function and get yourself an array to work with. In broad outline that would be somthing like...

Dim varParts as Variant

   varParts = Split(Field1,"\")

Then you can loop though the array and build your sub-string as you want

Const MAXLEN = 100
Dim n as Long
Dim strComponent As String

    strComponent = ""
    For n = UBound(varParts) to LBound(varParts)
        IF Len(strComponent) + Len(varParts(n) < MAXLEN Then
             strComponent = strComponent & varParts(n)
             ' we hit the limit. Store strComponent in table and then
             ' start another component string
             SaveData strComponent
             strComponent = varParts(n)
        End If
     Next n

LVL 12

Expert Comment

ID: 35029251
But your expected output for Field1 contains /  - if you split your string whenever you find a / you will not get your output.

Are you sure that the format of your input will actually allow a parsing?   i.e. is there a set of codable rules that can reliably be applied to your data?

LVL 12

Expert Comment

ID: 35029279
Using the split function is rarely good enough unless you KNOW that the incoming data abides by very simple rules.  e,g,  there are no user supplied strings that could contain your delimitor characters.

Author Comment

ID: 35029436
Actually, maybe I will split the field wherever I find a '/' .  It will give me a bunch of rows that I don't want but because of the inconsistency of the character length, maybe that's the only way.

Author Comment

ID: 35029479
Actually, maybe I will split the field wherever I find a '/' .  It will give me a bunch of columns that I don't want but because of the inconsistency of the character length, maybe that's the only way.

Where would I put the split code?  in VB?  I'm using MS Access 2007

LVL 11

Accepted Solution

LambertHeenan earned 2000 total points
ID: 35031512
Here is some more code that I have actually tried out (not the air code from before). It takes care of the "losing" of the delimiter characters when using Split().

Sub SplitTest()
Const MAXLEN = 100
Const MyStr = "NavTarget=ROLES://portal_content/com/com..ep/com.ep.sec/com.pi.roles/com.ko.ep.ep_roles/com..ep.role_sup/com.pi.career/perfor/com.ko.ep._plans"
Dim varParts As Variant
Dim strParts() As String
Dim n As Long
Dim nParts As Long ' how many < MAXLEN string do we have?
Dim strComponent As String
Dim boolStartwithDelim As Boolean

    varParts = Split(MyStr, "/")
    Debug.Print MyStr
    ReDim strParts(UBound(varParts))
    boolStartwithDelim = Left(MyStr, 1) = "/"
    nParts = 0
    ' now lets contstuct some lines paying attention to the "lost" delimiters
    For n = LBound(varParts) To UBound(varParts)
        If Len(strComponent & varParts(n) & "/") < MAXLEN Then
            If varParts(n) & "" = "" Then
                ' array element empty, which means the original string contained "//"
                strComponent = strComponent & "/"
                ' restore the 'missing' "/" character
                If n = LBound(varParts) Then
                    If boolStartwithDelim = True Then
                        strComponent = "/" '& varParts(n) & "/"
                    End If
                End If
                    ' after the first item in the array, every element originally had an initial "/"
                    ' so put it back
                strComponent = strComponent & varParts(n) & "/"
            End If
            ' we hit the limit. Store strComponent
            ' start another component string
            strParts(nParts) = strComponent
            nParts = nParts + 1
            boolStartwithDelim = False ' no longer working with the start of the string
            strComponent = ""
            strComponent = strComponent & "/" & varParts(n)
        End If
    Next n
    strParts(nParts) = strComponent
    ReDim Preserve strParts(nParts)
    For n = LBound(strParts) To UBound(strParts)
        Debug.Print strParts(n)
    Next n
End Sub

Open in new window

You should be able to adapt that for your needs.


Featured Post

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question