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
Solved

Sorting Files

Posted on 2000-03-08
4
218 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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

860 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