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.  
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

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

Stressed Out?

Watch some penguins on the livecam!

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Suggested Courses

771 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