Separating Text Field into Columns at Line Break 1

[DougTucker]

I need your help to automate the process to break a text field in MS Access(with multiple lines separated with the line break character) into separate columns. I've struggled for several days and have seen various code samples using the "Split" function, but nothing that exactly works.

Here's what I have:
1. Canned report that can be exported from a web page as Excel, BUT Excel is losing leading zeros on account numbers. I NEED the leading zeros (i.e., must be pulled in as TEXT and not number).

2. I can preserve the leading zeros by opening the file and saving as HTML, then importing into MS Access. This puts all the fields in the long text field into a single field that looks like this (no quotes in the original text):

COLUMN 1:
"Customer"

COLUMN 2 (5 fields concatenated with line breaks):
"Bob Smith
0001234
ACME Company
123 Main Street
Anywhere, USA"

The fields are always in the same place, so I want to parse it into separate columns (where "|" illustrates a column delimiter):

Bob Smith|0001234|ACME Company|123 Main Street|Anywhere, USA

What I envision is creating a function or procedure that spins through the table and breaks the values into separate columns in the same table. I can also use a query with calculated fields for each column (where I parse out [WholeString] into [StringPart1], [StringPart2]...[StringPart10] (there won't be more than 10 rows in the freeform text).

I'm fairly handly with MS Access, and a beginner at VBA.
Please reply with any further questions. Thanks in advance for your help.

- Doug T.

 

[dhookom]

You should be able to use split. For instance, try create a function like:

Function GetPart(strText As String, intPart As Integer, strDelimiter As String) As Variant
    On Error GoTo errGetPart
    Dim varText As Variant
    varText = Split(strText, strDelimiter)
    GetPart = varText(intPart - 1)
exitGetPart:
    Exit Function
    
errGetPart:
    Select Case Err
        Case Else
            GetPart = Null
            Resume exitGetPart
    End Select
End Function

Then use this function in a query
[tt]
SecondField: GetPart([Column 2],1,Chr(10))
[/tt]


Duane
Hook'D on Access
MS Access MVP

 

[DougTucker]

Duane: Thanks for the help. This is REALLY close! The first field works fine, but I'm getting a hanging character (displayed as a box at the end of the string). I think may be MS Access has an end of line (Chr$(13)) AND a line break (chr$(10)).

I'm also getting "#Error" (as the resulting value) when the function runs against nulls. I overcame this by only running the function if [WholeString] is not empty (as shown below in the calc for [SecondField]). The error trapping doesn't seem to catch this scenario.

Here's what I see as the result:
COLUMN 1 [WholeString]

COLUMN 2: SecondField: IIf([WholeString]<>"",GetPart([WholeString],1,Chr$(10)),"")
This produces "Bob Smith"

COLUMN 3: ThirdField: GetPart([WholeString],2,Chr(10))
This produces "0001234[]"
(where "[]" is the little box character)

I've continued the calculations and they work consistent with Column 3 (data displays with box character).

Any additional error handling guidance would be greatly appreciated!

Thank you.

~ Doug

 

[dhookom]

Try this modification which should handle nulls. You might want to call it by sending in the LF and CR.
[tt][blue]
SecondField: GetPart([Column 2],1,Chr(13) & Chr(10))
[/blue][/tt]

Function GetPart(varText As Variant, intPart As Integer, strDelimiter As String) As Variant
    On Error GoTo errGetPart
    Dim varText As Variant
    If Not IsNull(varText) Then
        varText = Split(varText, strDelimiter)
        GetPart = varText(intPart - 1)
    End If
exitGetPart:
    Exit Function
    
errGetPart:
    Select Case Err
        Case Else
            GetPart = Null
            Resume exitGetPart
    End Select
End Function

Duane
Hook'D on Access
MS Access MVP

 

[DougTucker]

The changes worked when the input field [varText] was null. I then ran into trouble where there was only one row of data in [varText]. In this case the [strDelimiter] was not present and the function threw a "Subscript Out of Range" error. I overcame it with if/then statements instructing the function to return the original string only for the first [intPart], then null for subsequent [intPart] values.

I don't know that it's the most efficient (I'll try it against several 1000 rows and see how it does).

Function GetPart(varText As Variant, intPart As Integer, strDelimiter As String) As Variant
    'Source:  [URL unfurl="true"]http://www.tek-tips.com/viewthread.cfm?qid=1556680[/URL]

    On Error GoTo errGetPart
    'Dim varText As Variant 'COMMENTED: (This is already declared above)
    If Not IsNull(varText) Then  'only run the function if the value exists
        If InStr(1, varText, strDelimiter) Then
            'Only run the procedure if the delimiters are found
            varText = Split(varText, strDelimiter)
            GetPart = varText(intPart - 1)
            'If delimiters aren't found, return the entire varText ONLY for the first intPart
            Else: If intPart > 1 Then GetPart = Null Else GetPart = varText
        End If
    End If
exitGetPart:
    Exit Function
    
errGetPart:
    Select Case Err     'an error exists
        Case Else       'any other error exists
            GetPart = Null  'return a null value
            Resume exitGetPart
    End Select
End Function

Thank you for your help!

~ Doug T.

 

[PHV]

What about this ?

Function GetPart(varText As Variant, intPart As Integer, strDelimiter As String) As Variant
Dim a
If IsNull(varText) Then Exit Function
a = Split(varText, strDelimiter)
If (intPart - 1) <= UBound(a) Then
  GetPart = a(intPart - 1)
End If
End Function

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DougTucker]

PH: That's TOO EASY!!! You responded just as I was working on the "UBound" condition. My query will have 10 columns (which accommodates the expected max of 8 items in the array, plus 2 more just in case), and there are situations where there are only 5 values in the array (which would cause columns 6-10 to error out). Your code handles this fine, and the simple error handling (just exit the function) does what I need.

THANK YOU all for your help. I'll follow up if I run into another snag.

This leads me to the quote for the day: "When you've been spinning your wheels so much that you have no more tire, rim, or axle, it's time to ask for help."

~ Doug T.

 

[DougTucker]

The above solution (from PHV) works well, except that it's a resource hog for my larger data sets. It's fine for a 100+ row query, but some larger records have several thousand rows, and it's taking over an hour to process the data.

Right now I have a query with a value "WholeString" (Column 1). This value is passed into the function to return columns 2-11 (10 calculated fields, named [StringRow1] through [StringRow10]). The query fields look like this:

StringRow1: GetPart([WholeString],1,Chr(13) & Chr(10))
StringRow2: GetPart([WholeString],2,Chr(13) & Chr(10))
...
StringRow10: GetPart([WholeString],10,Chr(13) & Chr(10))

It's inefficient to call the function 10 times, when each column only cares about one of the 10 values in the array.

How can I run the function once (per row) to create an array that is held in memory, then pull from the array for columns 2-11?

Any other optimization tricks would be appreciated. Thanks in advance for you help.

~ Doug T.

 

[joelflorendo]

I think a query with calculated fields would be more efficient than code...here are the first few columns:

Name: Left([Col2],InStr([Col2],Chr(10)))

Acct#: Mid([Col2],Len([Name])+1,InStr(Len([Name])+1,[Col2],Chr(10))-Len([Name]))

Company: Mid([Col2],Len([Name])+Len([Acct#])+1,InStr(Len([Name])+Len([Acct#])+1,[Col2],Chr(10))-Len([Name])-Len([Acct#]))

Addr: Mid([Col2],Len([Name])+Len([Acct#])+Len([Company])+1,InStr(Len([Name])+Len([Acct#])+Len([Company])+1,[Col2],Chr(10))-Len([Name])-Len([Acct#])-Len([Company]))


The first column can just use the Left function to get the name but everything after that follows the pattern (basically just adding the length of the previous column to the "start" of the mid function and the "start" and "length" of the instr function). The last few fields are going to get pretty gnarly but once your write them, just save it and it's done.

I omitted it for the sake of making it easier to read, but you might want to enclose each statement in a Trim function to get rid of any extra non-printing characters.