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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
User Level Security 6 38
deduplicating based on criteria 2 21
SQL Error - Query 6 25
Linking to an Excel file and determining the field types 3 13
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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…

770 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