?
Solved

Sorting Files

Posted on 2000-03-08
4
Medium Priority
?
222 Views
Last Modified: 2010-05-02
How do you sort a file in VB.  I need to sort a file like the way Unix sorts a file with the sort command or the way NT sorts by passing the function sort a file name and a column to sort....  Please tell me if there is a function in VB or a control or someone who has a function they built....Thanks
0
Comment
Question by:strongd
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 14

Expert Comment

by:wsh2
ID: 2598407
You can sort the File when you create your recordset by using your OpenRecordSet SQL statement..

Dim dbDemo as Database
Dim rsDemo as Recordset

Set dbDemo = OpenDatabase("Nwind.mdb")
Set rsDemo = dbDemo.OpenRecordSet ( _
  "SELECT * FROM Employees ORDER BY LastName, FirstName", _
  dbOpenDynaset, _
  dbReadOnly)
   
Your Recordset will then appear in that order.
0
 
LVL 7

Expert Comment

by:Vbmaster
ID: 2598509
How does it work in Unix/NT, does the column 2 mean from character 2 to end of line (as the sort command looks like in Win95) or does column 2 mean that the program reads the empty lines and figures out where column2 might be... If you can tell me I can probably make a function for ya, it sounds like a easy task. ;)
0
 
LVL 14

Accepted Solution

by:
mcrider earned 200 total points
ID: 2599202
Here you go... This code includes two functions "SortArrayByField" and "SortArrayByColumn".

SortArrayByField will allow you to sort information based on a field delimiter. For example let's say that your file look like this:

   xxxx,yy,zzzzz
   xxxxx,pp,yyy
   lll,zz,aaaa

You could use SortArrayByField to sort on the 2nd field of each record (yy, pp and zz in the above example)

SortArrayByColumn will allow to to sort information based on the character position of the record.  For example you could sort each record starting with the 5th character of each line.


To use these function, add the following code to a MODULE in your program:

'-------------------------------------------------------------------------------
    Sub SortArrayByField(InArray As Variant, FieldNumber As Long, Delimit As String, Optional IgnoreCase As Boolean)
        'THIS FUNCTION SORTS AN ARRAY BASED ON THE FIELDNUMBER IN THE ARRAY
        Dim lLoop1 As Long
        Dim lLoop2 As Long
        Dim tBuf As String
        If IgnoreCase = False Then
            For lLoop1 = UBound(InArray) To LBound(InArray) Step -1
                For lLoop2 = LBound(InArray) + 1 To lLoop1
                    If GetField(CStr(InArray(lLoop2 - 1)), Delimit, FieldNumber) > _
                        GetField(CStr(InArray(lLoop2)), Delimit, FieldNumber) Then
                        tBuf = InArray(lLoop2 - 1)
                        InArray(lLoop2 - 1) = InArray(lLoop2)
                        InArray(lLoop2) = tBuf
                    End If
                Next lLoop2
            Next lLoop1
        Else
            For lLoop1 = UBound(InArray) To LBound(InArray) Step -1
                For lLoop2 = LBound(InArray) + 1 To lLoop1
                    If UCase(GetField(CStr(InArray(lLoop2 - 1)), Delimit, FieldNumber)) > _
                        UCase(GetField(CStr(InArray(lLoop2)), Delimit, FieldNumber)) Then
                        tBuf = InArray(lLoop2 - 1)
                        InArray(lLoop2 - 1) = InArray(lLoop2)
                        InArray(lLoop2) = tBuf
                    End If
                Next lLoop2
            Next lLoop1
        End If
    End Sub
    Sub SortArrayByColumn(InArray As Variant, Start As Long, Optional Length As Long, Optional IgnoreCase As Boolean)
        'THIS FUNCTION SORTS AN ARRAY BASED ON THE CHARACTER POSITION IN THE ARRAY
        Dim lLoop1 As Long
        Dim lLoop2 As Long
        Dim tBuf As String
        If IgnoreCase = False Then
            For lLoop1 = UBound(InArray) To LBound(InArray) Step -1
                For lLoop2 = LBound(InArray) + 1 To lLoop1
                    If GetColumn(CStr(InArray(lLoop2 - 1)), Start, Length) > _
                        GetColumn(CStr(InArray(lLoop2)), Start, Length) Then
                        tBuf = InArray(lLoop2 - 1)
                        InArray(lLoop2 - 1) = InArray(lLoop2)
                        InArray(lLoop2) = tBuf
                    End If
                Next lLoop2
            Next lLoop1
        Else
            For lLoop1 = UBound(InArray) To LBound(InArray) Step -1
                For lLoop2 = LBound(InArray) + 1 To lLoop1
                    If UCase(GetColumn(CStr(InArray(lLoop2 - 1)), Start, Length)) > _
                        UCase(GetColumn(CStr(InArray(lLoop2)), Start, Length)) Then
                        tBuf = InArray(lLoop2 - 1)
                        InArray(lLoop2 - 1) = InArray(lLoop2)
                        InArray(lLoop2) = tBuf
                    End If
                Next lLoop2
            Next lLoop1
        End If
    End Sub
    Function GetField(Source As String, Delimiter As String, Optional Position As Variant) As String
        'THIS FUNCTION RETURNS THE SPECIFIED FIELD IN A STRING
        Dim iVal As Integer
        Dim jVal As Integer
        Dim kVal As Integer
        Dim tBuf As String
        jVal = 0: kVal = 1
        tBuf = Source + Delimiter
        If IsMissing(Position) = False Then
            For iVal = 1 To Val(Position) - 1
                jVal = InStr(jVal + 1, tBuf, Delimiter)
                If jVal = 0 Then
                    GetField = ""
                    Exit Function
                End If
            Next iVal
            kVal = jVal + 1
        End If
        iVal = InStr(jVal + 1, tBuf, Delimiter)
        If iVal = 0 Then
            GetField = ""
        Else
            GetField = Mid$(tBuf, kVal, iVal - kVal)
        End If
    End Function
    Function GetColumn(Source As String, Start As Long, Length As Long) As String
        'THIS FUNCTION RETURNS THE CHARACTER POSITION/LENGTH OF THE ARRAY
        If Length = 0 Then
            GetColumn = Mid$(Source, Start)
        Else
            GetColumn = Mid$(Source, Start, Length)
        End If
    End Function
'-------------------------------------------------------------------------------






Here is an example of how to read the file into an array and call the sorting routines...

    Dim fNum As Integer
    Dim fLines() As String
    Dim iVal As Long
   
    ReDim fLines(0) As String
    fNum = FreeFile
    Open "c:\windows\desktop\testfile.txt" For Input Access Read As fNum
    Do
        If EOF(fNum) Then Exit Do
        iVal = UBound(fLines)
        Line Input #fNum, fLines(iVal)
        ReDim Preserve fLines(iVal + 1) As String
    Loop
    ReDim Preserve fLines(UBound(fLines) - 1) As String
    Close fNum
   
    SortArrayByColumn fLines, 5, 3 'Sort starting a col 5 for 3 characters
   
    For iVal = 0 To UBound(fLines)
        Debug.Print "LINE:" + fLines(iVal)
    Next iVal




Cheers!.)
0
 
LVL 14

Expert Comment

by:mcrider
ID: 2599208
By the way, Don't you think the above code is worth more that 50 points?? ;-)


Cheers!®©
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses

777 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