0

vbScript - A Multi-column Sort With Minimal Coding

Please see my post vbScript - The Basics for more details on vbScript.

A previous post dealt with sorting. The code easily handles sorting simple things like lists of words, lines of text - cases where you only had to compare two things. What about the case where you want the option to sort on an arbitrary column of data, or you want to sort on multiple columns such as by birthdate, then by last name. That becomes more difficult to code.

Databases handle that with ease by allowing you to specify complex sorts in a SQL string. But sometimes you don't want to have to define a database just for the convenience of sorting.

In vbScript you can use ADO (ActiveX Data Objects) to create recordsets that are not attached to a database. You can add records, then sort them on the fly. You define a recordset by

Set rec = CreateObject("ADODB.RecordSet")

Then you add fields to the recordset with the Append method. You have access to all of the standard field type like VarChar, Int, Float, etc. I'll give the full list at the end. Fields can be accesses by a user specified field name, or by a zero-relative index. In the sample code we'll be sorting

the,9
quick,4
brown,7
fox,7
jumped,2
over,1
the,18
lazy,4
lazy,12
white,8
dog,9

so we'll need two fields - a VarChar (we'll name the field Word) and an Int (we'll call it Num)

rec.Fields.Append "Word",200,255       '200 = adVarChar             '
rec.Fields.Append "Num" ,2             '2   = adSmallInt            '

then to begin adding data we open the recordset

rec.Open

and to add a record we do

rec.AddNew
rec.Fields("Word") = "splunge"
rec.Fields("Num")  = 17

To sort the records you just tell the recordset what field or fields to use as

rec.Sort = "Word"
rec.Sort = "Num,Word"

You are not limited to just two sort fields. To step through the sorted data you move to the first record then process until EOF, doing a MoveNext to access each record in succession.

rec.MoveFirst

Do While Not rec.EOF
    wscript.Echo rec.Fields("Word"), vbTab, rec.Fields("Num")
    rec.MoveNext
Loop

When you are all done just

rec.Close

to close the recordset. You can sort on any, or as many columns as you want and you never have to write a line of sorting code.

Here are the values for the various field types

' ADODB.DataTypeEnum
Const adBigInt = 20
Const adBinary = 128
Const adBoolean = 11
Const adBSTR = 8
Const adChapter = 136
Const adChar = 129
Const adCurrency = 6
Const adDate = 7
Const adDBDate = 133
Const adDBFileTime = 137
Const adDBTime = 134
Const adDBTimeStamp = 135
Const adDecimal = 14
Const adDouble = 5
Const adEmpty = 0
Const adError = 10
Const adFileTime = 64
Const adGUID = 72
Const adIDispatch = 9
Const adInteger = 3
Const adIUnknown = 13
Const adLongVarBinary = 205
Const adLongVarChar = 201
Const adLongVarWChar = 203
Const adNumeric = 131
Const adPropVariant = 138
Const adSingle = 4
Const adSmallInt = 2
Const adTinyInt = 16
Const adUnsignedBigInt = 21
Const adUnsignedInt = 19
Const adUnsignedSmallInt = 18
Const adUnsignedTinyInt = 17
Const adUserDefined = 132
Const adVarBinary = 204
Const adVarChar = 200
Const adVariant = 12
Const adVarNumeric = 139
Const adVarWChar = 202
Const adWChar = 130
''#region Header                                                                        '
''                                                                                      '
''  Name:                                                                               '
''                                                                                      '
''    Detached.vbs                                                                      '
''                                                                                      '
''  Description:                                                                        '
''                                                                                      '
''    This script gives an example of how to create and use a detached recordset to     '
''    sort a list of items.                                                             '
''                                                                                      '
''  Audit:                                                                              '
''                                                                                      '
''    2005-03-02  jdeg  original code                                                   '
''                                                                                      '
''#endregion                                                                            '

file = Array("the,9", "quick,4", "brown,7", "fox,7", "jumped,2", "over,1", _
             "the,18", "lazy,4", "lazy,12", "white,8", "dog,9")

'create the recordset, define the data fields and open it for processing

Set rec = CreateObject("ADODB.RecordSet")
    rec.Fields.Append "Word",200,255       '200 = adVarChar             '
    rec.Fields.Append "Num" ,2             '2   = adSmallInt            '
    rec.Open

'add the lines into the recordset

For Each line In file
    flds = Split(line,",")
    if ubound(flds) = 1 Then
        rec.AddNew
        rec.Fields("Word") = flds(0)
        rec.Fields("Num")  = flds(1)
    end if
Next

WScript.Echo VbCrLf & "Unsorted" & VbCrLf
DumpRecordSet

WScript.Echo VbCrLf & "Sorted by 'Word'" & VbCrLf
rec.Sort = "Word"
DumpRecordSet

WScript.Echo VbCrLf & "Sorted by 'Num'" & vbCrLf
rec.Sort = "Num"
DumpRecordSet

WScript.Echo VbCrLf & "Sorted by 'Word' then 'Num'" & vbCrLf
rec.Sort = "Word,Num"
DumpRecordSet

WScript.Echo VbCrLf & "Sorted by 'Num' then 'Word'" & vbCrLf
rec.Sort = "Num,Word"
DumpRecordSet

rec.Close

Sub DumpRecordSet ()

    rec.MoveFirst
    
    Do While Not rec.EOF
        wscript.Echo rec.Fields("Word"), vbTab, rec.Fields("Num")
        rec.MoveNext
    Loop

End Sub

Computer languages in which I have developed applications

Assembler (DEC, Data General, 8080, GE, SEL, IBM 360)
WATFOR (Waterloo FORTRAN)
FORTRAN (SEL)
APL (IBM 360, IBM VSAPL)
PL/1
C/C++
Borland Paradox
VB.net
vbScript

1
Contributor
0
Replies
11
Views
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.