Parse data of varying lengths in MS Access

Posted on 2011-03-03
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
  • 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/

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.  
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

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 500 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/"
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
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.

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now