Solved

Sorting Files

Posted on 2000-03-08
4
215 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
  • 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 50 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

920 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now