Friday, August 11, 2006

VBScript: Using Disconnected Recordset for Sorting Data

Assuming you need to create a script to list all the subfolders and their sizes within a particular folder, and sort them to see which folders occupy maximum space (e.g., all the user profiles within \Documents and Settings). A traditional approach, at least to somone new to VB Scripting, would be to dump the output in CSV format in a file and open the file in Excel to sort the list. Thats what I used to do so far!

A better approach would be to use something called Disconnected Recordset. Recordsets are usually associated with Databases and connection objects. Disconnected Recordset is similar but not associated with any back-end databases. It remains in memory only within the scope of code execution. Although limited in featues, disconnected recordsets still offer basic functionalities like Sorting. So, with disconnected recordsets, for the above script, we can create on-the-fly recordset, sort the folder names based on their sizes within the code itself and write the sorted list in any text file format (CSV, HTML). Here is how we go about doing it:

Const adBSTR = 8
Const adDouble = 5
Const MaxCharacters = 255
Set DataList = CreateObject("ADOR.Recordset"
)
DataList.Fields.Append
"UserName"
, adBSTR , MaxCharacters
DataList.Fields.Append
"ProfileSize", adDouble

DataList.Open

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder("C:\Documents And Settings\")
Set colSubfolders = objFolder.Subfolders
For Each objSubfolder In colSubfolders
    
DataList.AddNew
    DataList(
"UserName").Value = CStr(objSubfolder.Name)
    DataList(
"ProfileSize").Value = objSubfolder.Size
    DataList.Update
Next

DataList.Sort = "ProfileSize DESC" ' Use DESC/ASC to specify sort order.
DataList.MoveFirst

Do Until DataList.EOF
    WScript.Echo DataList.Fields.Item("UserName") _
        &
vbTab & DataList.Fields.Item("ProfileSize")
    DataList.MoveNext
Loop

There are few things you need to take care of when using disconnected recordsets. While defining the fields ("UserName","ProfileSize") make sure you specify the right kind of data type for the field that matches the kind of data it needs to store. In this case, folder size could be as large as couple of GBs, therefore Integer would not be the right datatype for "ProfileSize" field. The complete list of all possible data types is available here. You might also have to use data conversion functions while storing values to these fields as I had to use CStr() for getting folder name. If you are not sure what datatype the retrieved value will have, you can use VarType() function to find that out, which will give a numeric value corresponding to its datatype. Here is the complete table.

1 comment:

Anonymous said...

Thanks a million! JUST what I was looking for! (for a slightly different purpose..)