parsing text

[selahlynch]

I'm adjusting to visual basic after using Perl for a few years... it is difficult.

I'd like to parse this text...

1200:3012-4130(1-560) 1208:3012-4130(561-1120) 1216:3012-4130(1121-1680)

Into this table

rline   fstat   lstat   fchan  lchan
1200    3012    4130    1      560
1208    3012    4130    561    1120
1216    3012    4130    1121   1680

I could find SOME way to do it... but I'd like to find a nice elegant way.
If you have any suggestions I'd appreciate them.

Thanks.

 

[ajetrumpet]

the answer might be easier to give if you told us how all the records are similar. does the string ever change in length? do each sections of the string always look the same? for example, is the format between the () markings in the first string set always look like the following...?

#-###

if so, you can do it easily with the string functions LEFT(), MID and INSTR()

 

[selahlynch]

Yes, the string can change in length.
Any one of those numbers could range between 1-99999

 

[stopher]

As Adam said, you can use some functions to break down the string. This should get you started:

Public Sub parseText(strInput As String)
Dim strBlock As Variant
Dim rline As Integer
Dim fstat As Integer
Dim lstat As Integer
Dim fchan As Integer
Dim lchan As Integer

For Each strBlock In Split(strInput, " ")
    rline = CInt(Left(strBlock, InStr(1, strBlock, ":") - 1))
    strBlock = Right(strBlock, Len(strBlock) - InStr(1, strBlock, ":"))
    
    fstat = CInt(Left(strBlock, InStr(1, strBlock, "-") - 1))
    strBlock = Right(strBlock, Len(strBlock) - InStr(1, strBlock, "-"))
    
    lstat = CInt(Left(strBlock, InStr(1, strBlock, "(") - 1))
    strBlock = Right(strBlock, Len(strBlock) - InStr(1, strBlock, "("))
    
    fchan = CInt(Left(strBlock, InStr(1, strBlock, "-") - 1))
    strBlock = Right(strBlock, Len(strBlock) - InStr(1, strBlock, "-"))
    
    lchan = CInt(Left(strBlock, InStr(1, strBlock, ")") - 1))
    strBlock = Right(strBlock, Len(strBlock) - InStr(1, strBlock, ")"))

Debug.Print rline, fstat, lstat, fchan, lchan
'put your table write code here e.g. SQL or recordset expressions

Next

End Sub

hth
Chris

 

[ajetrumpet]

Yes, the string can change in length.
Any one of those numbers could range between 1-99999

try not to JUST answer the question. how about expanding upon it to try and solve this yourself? don't expect to be spoon fed the answer. try to make some effort to expand the explanation to help US help you. luckily for you, stopher has assumed the rest and got it started

btw chris,

nice clean solution! awesome..

 

[selahlynch]

Hey, I was just curious to see how other people would approach the problem. Looking to share ideas.

This is what I had imagined doing:

for each spreadline in split(spread)

    spreadline = (replace(spreadline,":"," ")
    spreadline = (replace(spreadline,"-"," ")
    spreadline = (replace(spreadline,"("," ")
    spreadline = (replace(spreadline,")"," ")

    data = split spreadline

    rline = data(1)
    fstat = data(2)
    lstat = data(3)
    fchan = data(4)
    lchan = data(5)

    'put into database

next

I liked stopher's approach also, it seems like it would be more generally useful. Thanks stopher.

Any other ideas?

 

[selahlynch]

An attempt to explain the problem better, here are three examples of input...

1048:2628-3186(1-280) 1056:2628-3186(281-560) 1064:2628-3186(561-840)

1136:2628-3184(3070-3348) 1144:2628-3184(3349-3627) 1152:2628-3184(3628-3906) 1160:2628-3184(3907-4185) 1168:2628-3180(4186-4464)

1136:998-1244(3070-3348) 1144:998-1244(3349-3627)

You can assume there is only one space between each segment of the line. Each number can range from 1-99999. Each segment has the same structure A:B-C(D-E)

 

[stopher]

Hey, I was just curious to see how other people would approach the problem. Looking to share ideas.

This is what I had imagined doing:

for each spreadline in split(spread)

    spreadline = (replace(spreadline,":"," ")
    spreadline = (replace(spreadline,"-"," ")
    spreadline = (replace(spreadline,"("," ")
    spreadline = (replace(spreadline,")"," ")

    data = split spreadline

    rline = data(1)
    fstat = data(2)
    lstat = data(3)
    fchan = data(4)
    lchan = data(5)

    'put into database

next

I liked stopher's approach also, it seems like it would be more generally useful. Thanks stopher.

Any other ideas?

Very neat solution

 

[vbaInet]

I thought this was a rather interesting task. So, for those of us who love arrays :

Option Compare Database
Option Explicit

Dim arrayBlock$()

Public Function SplitIntoArray(inputString As String)

    Dim splitString$(), i%, j%, k%, pos%
    
    splitString = Split(inputString, " ")
    ReDim arrayBlock(UBound(splitString), 4)
    pos = 1
    
    For i = 0 To UBound(splitString)
        For j = 0 To 4
            For k = pos To Len(splitString(i))
                If IsNumeric(Mid(splitString(i), k, 1)) Then
                    arrayBlock(i, j) = arrayBlock(i, j) & Mid(splitString(i), k, 1)
                Else
                    pos = k + 1
                    Exit For
                End If
            Next
        Next
        pos = 1
    Next

End Function

To get the first "record":

Debug.Print arrayBlock(0, 0) & vbTab & arrayBlock(0, 1) & vbTab & arrayBlock(0, 2) & vbTab & arrayBlock(0, 3) & vbTab & arrayBlock(0, 4)

 

[SOS]

Hey, I was just curious to see how other people would approach the problem. Looking to share ideas.

This is what I had imagined doing:

for each spreadline in split(spread)
 
    spreadline = (replace(spreadline,":"," ")
    spreadline = (replace(spreadline,"-"," ")
    spreadline = (replace(spreadline,"("," ")
    spreadline = (replace(spreadline,")"," ")
 
    data = split spreadline
 
    rline = data(1)
    fstat = data(2)
    lstat = data(3)
    fchan = data(4)
    lchan = data(5)
 
    'put into database
 
next

I liked stopher's approach also, it seems like it would be more generally useful. Thanks stopher.

Any other ideas?

I would have approached it with a nested split as well.

 

[ajetrumpet]

Option Compare Database
Option Explicit

Dim arrayBlock$()

Public Function SplitIntoArray(inputString As String)

    Dim splitString$(), i%, j%, k%, pos%
    
    splitString = Split(inputString, " ")
    ReDim arrayBlock(UBound(splitString), 4)
    pos = 1
    
    For i = 0 To UBound(splitString)
        For j = 0 To 4
            For k = pos To Len(splitString(i))
                If IsNumeric(Mid(splitString(i), k, 1)) Then
                    arrayBlock(i, j) = arrayBlock(i, j) & Mid(splitString(i), k, 1)
                Else
                    pos = k + 1
                    Exit For
                End If
            Next
        Next
        pos = 1
    Next

End Function

the most for and while loops I have ever nested is 5. what about you mr. genius?

 

[vbaInet]

the most for and while loops I have ever nested is 5. what about you mr. genius?

I've never needed to nest more than 4 ever and that was for a Convolution exercise. If I was getting that deep I would be thinking of using UDTs just to level things out a bit. It can get confusing

what about you mr. genius?

This is exactly what Genius' do, calling someone else a Genius

 

[ajetrumpet]

This is exactly what Genius' do, calling someone else a Genius

ummm, i dont' think so. where is your proof anyway?

on a side note that is kinda interesting...based on some of the feedback I've gotten from select members here, I would actually be contributing MORE if I left. It'll be a cold day in heck before I understand that logic...

 

[vbaInet]

ummm, i dont' think so. where is your proof anyway?

A Genius is not known in his own country hehe!

on a side note that is kinda interesting...based on some of the feedback I've gotten from select members here, I would actually be contributing MORE if I left. It'll be a cold day in heck before I understand that logic...

You might need some AND, OR, XOR gates plus some AI, then throw in some exponential functions to calculate the probabilities I think you should get an explanation from whoever made that comment.

 

[ajetrumpet]

I think you should get an explanation from whoever made that comment.

i have convinced myself that it's not worth the finger speed...

by the way, you wanted to know why all of my code snippets are wrapped by PHP? basically because the color looks nice. it's rainbow like! changes the scenery...and plus, I love the PHP language. I think it's easy, free and well structured. And may I say, it's about time that they are finally adding a GOTO statement to the language. My god...it's been 5+ versions already! I've seen a lot of complaints about that problem on PHP forum.

I will go out of my way to write something in PHP before I attempt it with visual basic.

 

[vbaInet]

It's powerful enough as a scripting language and serves that purpose well. Anything beyond scripting, we start seeing limitations.

Well, we better not confuse the OP