parse out field from mainframe report text

[John1Chr]

Hi all,

I wonder if there is a way to parse this out using SQL? I'm struggling to create a function in excel that'll parse these rows into separate fields as listed below. Problem is that the mainframe report only uses spaces to distinctly separate the numbers.


01 - AITKIN 02 - USTH U.S. TRUNK 151,964 55,466,860 49.010
01 - AITKIN 03 - WITH Wisconsin TRUNK 406,446 148,352,790 214.257

This needs to be parsed into separate fields(columns) that contains separate fields that I indicate in brackets:

row 1
(01) (AITKIN 02) (USTH U.S. TRUNK) (151,964)(55,466,860)(49.010)

row2
(01) (AITKIN 03)(MNTH MINNESOTA TRUNK)(406,446) (148,352,790)(214.257)

 

[HarleyQuinn]

Is the data always going to split up into 6 columns?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[John1Chr]

yes, it will always be 6 columns

 

[HarleyQuinn]

OK, this will do it. I've created a User Defined Function to do this for you (there are several ways to do this within the function but I've chosen a simlpe to understand method as opposed to going into a full Regular Expressions solution, though they are my favourite )

I created a table (tbl_MainFrame), with one column (MainFrameOutPut) and filled it with the data you provided.

I queried this table using the following query:

SELECT ParseString([tbl_MainFrame]![MainFrameOutPut],1) AS Col1, ParseString([tbl_MainFrame]![MainFrameOutPut],2) AS Col2, ParseString([tbl_MainFrame]![MainFrameOutPut],3) AS Col3, ParseString([tbl_MainFrame]![MainFrameOutPut],4) AS Col4, ParseString([tbl_MainFrame]![MainFrameOutPut],5) AS Col5, ParseString([tbl_MainFrame]![MainFrameOutPut],6) AS Col6
FROM tbl_MainFrame;

This calls the function passing the number of the column you wish to return (this is so you can return any column you want rather than always having to return all of them, and is 1 based).

The function is as follows (paste into a module in Access):

Function ParseString(strInput As String, ReturnCol As Integer) As String

Dim strHolder() As String
Dim i As Integer
Dim lngPlaceHolder As Long
Dim strFinalFields As String

strHolder = Split(strInput, " - ")
strFinalFields = strHolder(2)

For i = 1 To 3
   lngPlaceHolder = InStrRev(strFinalFields, " ", -1 + lngPlaceHolder)
Next i

ReDim Preserve strHolder(6)

strHolder(2) = Left(strFinalFields, lngPlaceHolder - 1)
strHolder(3) = Replace(Mid(strFinalFields, lngPlaceHolder + 1), " ", "|")

ParseString = Split(Join(strHolder, "|"), "|")(ReturnCol - 1)

End Function

What the function basically does is split the original data into three (the parts seperated by " - ", so the first two columns and the rest). It then loops through the third part from the back looking for spaces and stops when it's got 3 (so we know where the end of what should be the third column is) and then puts the rest into another value in the array (replacing the spaces with pipes(|)). It then JOINs the whole array with pipes and then extracts the value for the column you specify in the call.

Hope that makes sense! If you need any more information just post back

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[HarleyQuinn]

Might be overkill now but I've got a working RegExp solution now. I'll post it when I get home (or work tomorrow) if anyone's interested?

Cheers

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[HarleyQuinn]

OK, I'm ready to be shot down in flames if it doesn't work with every possible combination that could be in that table but here goes anyway (it works correctly with the supplied data and is easily modify-able to accomodate other variances in the input string)...

Function ParseString2(ByVal strInput As String, ByVal ReturnCol As Integer) As String

        Dim RegEx As New RegExp ' Object
        Dim objMatches As MatchCollection ' Object
        Dim objMatch As Match ' Object

        'RegEx = CreateObject("VBScript.Regexp") Comment in and replace declares with Object if you don't want to use references (i.e. Late Binding)

        With RegEx
            .Global = True
            .IgnoreCase = True
            .Pattern = "[a-z]+.?([a-z]+.?[a-z]+|[0-9]|\.+.?[a-z]+)+|[0-9]+\,[0-9]+\,[0-9]+|[0-9]+\,[0-9]+|[0-9]+\.[0-9]+|[0-9]+"
            objMatches = .Execute(strInput)

        End With

        'For Each objMatch In objMatches
        '    MsgBox(objMatch.value)
        'Next

        ParseString2 = objMatches(ReturnCol - 1)

    End Function

Call it the same way you would call the first function but using the function ParseString2 instead.

This was more something I wanted to do for myself to see if I could get it to work but if it helps someone all the better (if the syntax doesn't work instantly it's because I had to do the final tests in .Net as opposed to Access, but the pattern should be sound)

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[John1Chr]

Harley thanks for your effort and I'm going to try it.

I solved it in excel with four funcions placed in four different columns. In the last column I used the data text to columns to separate. It works and is one way of doing it.

1.=TEXT(LEFT(A1,2),"00")

2.=MID(A1,FIND(" - ",A1)+3,FIND("#",SUBSTITUTE(A1,"-","#",2))-FIND(" - ",A1)-3)

3.=LEFT(MID(A1,FIND("#",SUBSTITUTE(A1,"-","#",2))+2,255),MIN(FIND(0,SUBSTITUTE(MID(A1,FIND("#",SUBSTITUTE(A1,"-","#",2))+2,255),{1,2,3,4,5,6,7,8,9},0)&0))-1)

4. =RIGHT(MID(A1,FIND("#",SUBSTITUTE(A1,"-","#",2))+2,255),LEN(MID(A1,FIND("#",SUBSTITUTE(A1,"-","#",2))+2,255))-MIN(FIND(0,SUBSTITUTE(MID(A1,FIND("#",SUBSTITUTE(A1,"-","#",2))+2,255),{1,2,3,4,5,6,7,8,9},0)&0))+1)

 

[HarleyQuinn]

Good stuff, thanks for trying it in Access as I think it'd be the easiest way to handle this.

Gld you got this sorted for you though

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[HarleyQuinn]

Plus, the regexp was a pain in the bum to work out

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.