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