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
rec.Fields.Append "Word",200,255 '200 = adVarChar ' rec.Fields.Append "Num" ,2 '2 = adSmallInt '
then to begin adding data we open the recordset
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
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