Solved

Sorting Files

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
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…

743 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

12 Experts available now in Live!

Get 1:1 Help Now