String manipulation: Get all data between 2 points in a variable, and sort into alphabetical order.

Lostie
Lostie used Ask the Experts™
on
Hi Experts,

As with all questions I ask at the moment, please note I'm using Attachmate Extra! basic, which is a cut down version of MS VB. I don't know how good it's array support is, if at all. PLEASE feel free to post VB solutions, and I will try an adapt them, which I'm often able to do. Can only give points to the answer than works for me though, even if your answer may be correct for Microsoft VB. ***As far as I know, Attachmate Basic does not have the split() function! So if your solution uses that, you'll need to find a way around it. It does have len(), mid(), instr() and most other VB functions though.***

Right, here's some sample data:
---
blah blah test
etc
[names]
Paul Reynolds
Bill Gates
John Smith
Freddy
Aunt Agatha
Pete
James W.
[/names]
waffle etc
blah
---
I need firstly to get all data between the 2 [names] tags into a variable, lets call it "names" (the data above and below the names tags changes daily but is unimportant, and the names data also changes regularly, but the tags stay the same).

I then need to sort all data in the names variable into alphabetical order, ignoring upper or lower case, but final data must be in it's original case. Presumably each name can be seperated by the newline character? I already have newline defined as:
NL$ = Chr$(13) + Chr$(10)

Full points to the expert who provides a working solution, thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
OK since this could technically be considered 2 questions, I'll give 500 points for each one :-)

Commented:
I've done a first-letter comparison to sort them fairly alphabetically using only len/mid/instr but i did use collections instead of arrays since you were doubtful on support for it:

Private Sub Form_Load()
    Dim strContent As String
    Dim intPos1 As Integer, intPos2 As Integer
    'Load the file into a string
    Open "c:\test.txt" For Binary As 1
        strContent = Space(LOF(1))
        Get #1, 1, strContent
    Close 1
    'Parse contents
    intPos1 = InStr(1, strContent, "[names]")
    intPos2 = InStr(1, strContent, "[/names]")
    If intPos1 > 0 And intPos2 > 0 Then
        strContent = Mid(strContent, intPos1 + Len("[names]" & vbCrLf), intPos2 - intPos1 - Len("[/names]" & vbCrLf) - 1)
        strContent = SortVar(strContent)
        MsgBox strContent
    End If
End Sub
Private Function SortVar(ByVal strVar As String) As String
    'Split() would have helped a great deal :(
    Dim intPos1 As Integer, intCurrent As Integer, intAlpha As Integer
    Dim colVars As New Collection, colItem As Variant
    Do Until Len(strVar) = 0
        intPos1 = InStr(1, strVar, vbCrLf)
        If intPos1 = 0 Then
            colVars.Add strVar
            Exit Do
        Else
            colVars.Add Mid(strVar, 1, intPos1 - 1)
            strVar = Mid(strVar, intPos1 + Len(vbCrLf))
        End If
        DoEvents
    Loop
    intCurrent = 1
    intAlpha = 1
    Do Until colVars.Count = 0
        If GetAsc(colVars.Item(intCurrent), 1) < GetAsc(colVars.Item(intAlpha), 1) Then
            intAlpha = intCurrent
        End If
        intCurrent = intCurrent + 1
        If intCurrent > colVars.Count Then
            SortVar = SortVar & colVars.Item(intAlpha) & vbCrLf
            colVars.Remove intAlpha
            intCurrent = 1
            intAlpha = 1
        End If
        DoEvents
    Loop
End Function
Private Function GetAsc(ByVal strTemp As String, ByVal intPos As Integer) As Integer
    GetAsc = Asc(LCase(Mid(strTemp, intPos, 1)))
End Function





... Additionally you could just load all the items into a listbox which the sorted property set to true and visible=false which would sort them for you without parsing and comparing each character to the last one in the list.




Private Sub Form_Load()
    Dim strContent As String
    Dim intPos1 As Integer, intPos2 As Integer
    'Load the file into a string
    Open "c:\test.txt" For Binary As 1
        strContent = Space(LOF(1))
        Get #1, 1, strContent
    Close 1
    'Parse contents
    intPos1 = InStr(1, strContent, "[names]")
    intPos2 = InStr(1, strContent, "[/names]")
    If intPos1 > 0 And intPos2 > 0 Then
        strContent = Mid(strContent, intPos1 + Len("[names]" & vbCrLf), intPos2 - intPos1 - Len("[/names]" & vbCrLf) - 1)
        strContent = SortVar(strContent)
        MsgBox strContent
    End If
End Sub
Private Function SortVar(ByVal strVar As String) As String
    'Split() would have helped a great deal :(
    Dim intPos1 As Integer, intLoop As Integer
    Do Until Len(strVar) = 0
        intPos1 = InStr(1, strVar, vbCrLf)
        If intPos1 = 0 Then
            List1.AddItem strVar
            Exit Do
        Else
            List1.AddItem Mid(strVar, 1, intPos1 - 1)
            strVar = Mid(strVar, intPos1 + Len(vbCrLf))
        End If
        DoEvents
    Loop
    For intLoop = 0 To List1.ListCount - 1
        SortVar = SortVar & List1.List(intLoop) & vbCrLf
    Next intLoop
End Function


Author

Commented:
Sorry about this, after testing your 2 *excellent* examples it transpires that this silly version of VB doesn't support Listboxes* or collections, but DOES support arrays, they may well be a bit limited however. If I post some example code of how this awful version of VB uses arrays, would you be able to convert your first example to use arrays instead of collections? (Sorry, I'm somewhat of a VB newbie, especially where arrays are concerned!).

Thanks very much for the time you took in writing the above examples, I've learned a lot from them already!

(nb: It does have listboxes, but not in the same way MS VB does, and not with a sorted property, as far as I can tell!)

Example array code:

This example displays a dialog box with a check box, labeled ''Display List'', and an empty list box. If the user clicks the check box, the list box is filled with the contents of the array called ''myarray''. The DlgListBox Array function makes sure the list box is empty.

Declare Function FileDlgFunction(identifier$, action, suppvalue)
Sub Main
   Dim button as integer
   Dim identifier$
   Dim action as Integer
   Dim suppvalue as Integer
   Begin Dialog newdlg 186, 92, "DlgListBoxArray Example", .FileDlgFunction
      '$CStrings Save
      OKButton  130, 6, 50, 14
      CancelButton  130, 23, 50, 14
      ListBox  19, 26, 74, 59, "", .ListBox1
      CheckBox  12, 4, 86, 13, "Display List", .CheckBox1
      '$CStrings Restore
   End Dialog
   Dim dlg As newdlg
   button = Dialog(dlg)
End Sub

Function FileDlgFunction(identifier$, action, suppvalue)
Dim myarray$(3)
Dim msgtext as Variant
Dim x as Integer
For x= 0 to 2
   myarray$(x)=Chr$(x+65)
Next x
   Select Case action
      Case 1
      Case 2                     'user changed control or clicked a button
         If DlgControlID(identifier$)=3 then
             If DlgListBoxArray(2)=0 then
                     DlgListBoxArray 2, myarray$()
         End If    
      End Select
End Function
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
If this helps, found out some more about array support and array functions that can be used:

Erase array1[, array2]...
rc% = LBound (arrayVariable [,dimension])
ReDim variableName (subscriptRange,...)[As [New] type],...
rc% = UBound(arrayVariable [,dimension])

There are also some more string handling functions available, I think they're mostly the same as MS VB but not positive:

rc$ = GetField$( string$, fieldnumber%, separatorchars$ )
left()
right()
len()
rc = string Like pattern (Used to compare a string or string expression against a supplied pattern (including wildcard characters)).
rc$ = LTrim[$](string$) (also RTrim()
mid()
rc$ = SetField$(string$,field-number%,field$,separator-chars$)
rc = StrComp(string1$, string2$ [, comparetype%])
trim()

Would that make things any easier?

Also what do you mean by "mostly" alphabetic in example 1, is it possible for it to be fully alphabetic given the new commands available above?

Thanks!

- A certified VB newbie.

Commented:
Does it support "Redim Preserve"? It's a method of expanding the array while not erasing previous values in other arrays before it.

Author

Commented:
Unfortunately not :-( I've finally gotten around to the long process of converting the code over to MS VB6 anyway, something I should have done a long time ago. Your listbox example seems the best to use, would this code be made any better by the fact that I can now use split() and arrays, or won't there be much difference? Cheers :)

Commented:
Alot simpler with split() and the use of a listbox's sorted property:


Private Sub Form_Load()
    Dim strContent As String, intPos1 As Integer, intPos2 As Integer
    Dim strArray() As String, intLoop As Integer
    'Load the file into a string
    Open "c:\test.txt" For Binary As 1
        strContent = Space(LOF(1))
        Get #1, 1, strContent
    Close 1
    'Parse contents
    intPos1 = InStr(1, strContent, "[names]")
    intPos2 = InStr(1, strContent, "[/names]")
    If intPos1 > 0 And intPos2 > 0 Then
        strContent = Mid(strContent, intPos1 + Len("[names]" & vbCrLf), intPos2 - intPos1 - Len("[/names]" & vbCrLf) - 1)
        strArray = Split(strContent, vbCrLf)
        For intLoop = 0 To UBound(strArray)
            List1.AddItem strArray(intLoop)
        Next intLoop
        strContent = ""
        For intLoop = 0 To List1.ListCount - 1
            strContent = strContent & List1.List(intLoop)
            If intLoop <> List1.ListCount - 1 Then strContent = strContent & vbCrLf
        Next intLoop
        MsgBox strContent
    End If
End Sub

Author

Commented:
Thank you very much, I'll test this when I get home later today :-)
Commented:
Lostie,

First and foremost, Redim Preserve works in Attachmate Extra! Basic. I've tried some code with my Attachmate Extra! Basic and have come up with a solution with just the code without any visual components. Just copy the following code into a new macro and try running it. It will show 2 message boxes. First one the original data and the second one, the sorted one. I have tested this on my Extra! Basic editor and works perfect. My Extra! version is 6.7 and i have tried it on 6.5 also. Have a go at it.

If you want to try this code in VB, just change the "Global" at the top of the code to "Public" and copy the "Sub Main" code into the form load or something.

'***********************************************************
Global strNewLine As String

Function GetNames(strTemp$) As String
    Dim nStart As Integer, nEnd As Integer
   
    nStart = InStr(1, strTemp, "[names]")
    nEnd = InStr(1, strTemp, "[/names]")

    GetNames = Mid(strTemp, nStart + 9, nEnd - nStart - 9)
End Function

Function SortNames(strTemp$) As String
    Dim i As Integer, j As Integer, nNewLineCount As Integer
    Dim nPos As Integer, nPrevPos As Integer
    Dim strData() As String
   
    nNewLineCount = 0
    nPos = 1
    nPrevPos = 1
    Do
        nPos = InStr(nPos + 1, strTemp, strNewLine)
        If (nPos > 0) Then
            nNewLineCount = nNewLineCount + 1
            ReDim Preserve strData(nNewLineCount)
            strData(nNewLineCount) = Mid(strTemp, nPrevPos, nPos - nPrevPos)
            nPrevPos = nPos + 2
        Else
            Exit Do
        End If
    Loop
   
    For i = 1 To UBound(strData) - LBound(strData)
        For j = i + 1 To UBound(strData) - LBound(strData)
            If (StrComp(strData(i), strData(j)) > 0) Then
                strTemp = strData(j)
                strData(j) = strData(i)
                strData(i) = strTemp
            End If
        Next
    Next
   
    strTemp = ""
    For i = 1 To UBound(strData) - LBound(strData)
        strTemp = strTemp & strData(i) & strNewLine
    Next

    SortNames = strTemp
End Function

Function GetContent(strContent$, strSortedNames$) As String
    Dim nStart As Integer, nEnd As Integer
   
    nStart = InStr(1, strContent, "[names]")
    nEnd = InStr(1, strContent, "[/names]")

    'nStart + 9 => 6 Chars for [Names], 1 for Chr$(13), 1 for Chr$(10)
    GetContent = Mid(strContent, 1, nStart + 8) & strSortedNames & Mid(strContent, nEnd)
End Function

Sub Main
    Dim strContent As String, strNames As String

    strNewLine = Chr$(13) + Chr$(10)

    strContent = "blah blah test" & strNewLine
    strContent = strContent & "etc" & strNewLine
    strContent = strContent & "blah blah test" & strNewLine
    strContent = strContent & "etc" & strNewLine
    strContent = strContent & "[names]" & strNewLine
    strContent = strContent & "Paul Reynolds" & strNewLine
    strContent = strContent & "Bill Gates" & strNewLine
    strContent = strContent & "John Smith" & strNewLine
    strContent = strContent & "Freddy" & strNewLine
    strContent = strContent & "Aunt Agatha" & strNewLine
    strContent = strContent & "Pete" & strNewLine
    strContent = strContent & "James W." & strNewLine
    strContent = strContent & "[/names]" & strNewLine
    strContent = strContent & "waffle etc" & strNewLine
    strContent = strContent & "blah" & strNewLine
   
    strNames = strContent
    strNames = GetNames(strNames)
    strNames = SortNames(strNames)
    strNames = GetContent(strContent, strNames)
   
    MsgBox strContent
    MsgBox strNames
End Sub

Commented:
Populate strContent with your actual data before calling the functions:

strNames = strContent
strNames = GetNames(strNames)
strNames = SortNames(strNames)
strNames = GetContent(strContent, strNames)

strContent will have the original data as is, and strNames also will have the original data but with the names sorted.

cheers,
srimanth.

Author

Commented:
Wow, and here's me thinking I was the only Extra! basic user here :-)
Unfortunately I have to use both Extra! basic and VB6 for different things and on different PC's, what I'm doing is a little project to automate some repetitive maintenance tasks on a web forum I moderate on (vBulliten). So to have a solution for BOTH Extra and VB6 is simply amazing! Will post again when I've tested them later.
As for points, you both deserve them for effectively answering 2 questions AND for both versions of Visual Basic I use, will dish 'em out later. Thank you both :-)

Commented:
Lostie,

The solution i provided works in both Extra! Basic and in VB.

Also, I am not an Extra! User but a hardcore VB guy. It just so happens that i have to connect to an IBM Mainframe and we have a corporate license with Attachmate and hence have most of Attachmate's Editors, ActiveX Ojbects and other stuff. So, i write small macros on Extra to run on the mainframes for repetitive tasks.

cheers,
srimanth.

Author

Commented:
Given the points/grade to srimanth in this question as it was _exactly_ what I needed, zzzzzooc you can pick your points up from the following thread as I will probably use your listbox code in the VB6 version of my app:
http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_20725740.html

srimanth: We also use Extra to connect to mainframes at work here, I'm writing the same app in Extra basic and VB6 as I can't use VB6 at work, and can't use Extra at home. I find there's VERY few resources on the web for programming in Extra basic, one usually has to look at MS VB code and hack it somewhat to make it work :(

Commented:
Lostie,

If it is a licensing issue, then it won't be of any help.

Attachmate have Enterprise ActiveX Objects (EAO) Version 3.0. This version has been running since the last decade and on contacting them was informed that they are never gonna get another version of the EAO. These objects are very helpful. I use VB and have stopped using Extra! Macros. I write all my code now in VB and have a EAO embeded in my VB programs. So, i access the mainframes through my VB which makes my life lot simpler.

cheers,
srimanth.

Author

Commented:
Srimanth - it works great but there is one small problem: It cannot seem to handle tags of different lengths, ie. [longernames][/longernames] or [nms][/nms], but [names][/names] is fine. Any ideas ? :)

Author

Commented:
NM, I figured this one out for myself :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial