Vista - Sort files collection ?

Hello,

I use FileSystemObject to get files collection for some folder( Files
property )
How do I sort this collection by name by date and etc.
I can build sort logic of course but maybe there are easy standard ways ?

thanks
Vilius

Vilius Mockûnas schrieb:(1) use .Run or .Exec to do a "dir /O ..." command and use its
output: simple, fast, but not very flexible (what hides behind
your "and etc"?)

(2) put the objects of the files collection in an array, write a
sort sub/function that uses the relevant property/ies for
comparison: a lot of work

(3) put the properties of the objects in the files collection in
a disconnected ADO recordset, use its .Sort method to solve
your problem: easy and flexible

Here's a basic script that alphabetizes all file
names in C:\Windows. It's not terribly complex,
but it would get more tricky if you want to sort
by date. This particular QuickSort sorts non-case-
sensitive. (Otherwise you get all capitalized
names first.) For dates, as an offhand guess, I
think you'd want to convert the dates to numeric
and then use another version of QuickSort in
which you drop the Ucase operation. (The sorting
uses > comparison, so it can be adapted to both
words and numbers.)

I was actually just exploring sort routines a few
days ago. Interesting stuff. On my old Win98SE
machine with a Sempron 1,800 Mhz CPU, a basic
QuickSort can sort close to 30,000 words per second
in a VBScript, and it doesn't seem to slow down with
increasing size. (The more well known bubble sort,
by comparison, is lucky to do 1,000 in a second and
quickly slows to the point of unusability as the array to
sort gets bigger.

'--------------------------------------------------
Sort all files in C:\Windows alphabetically
'------------------------------

Dim FSO, oFol, oFils, oFil, AFils(), i2
Set FSO = CreateObject("Scripting.FileSystemObject")
Set oFol = FSO.GetFolder("C:\Windows")
Set oFils = oFol.Files
ReDim AFils(oFils.count - 1)
i2 = 0
For Each oFil in oFils
AFils(i2) = oFil.Name
i2 = i2 + 1
Next
Set oFils = Nothing
Set oFol = Nothing
Set FSO = Nothing

QuickSort AFils, 0, 0

Dim S2
S2 = Join(AFils, vbCrLf)

'-- watch out for this. An oversize message box
'-- sometimes puts the close button offscreen.
MsgBox S2


Sub QuickSort(AIn, LBeg, LEnd)
Dim LBeg2, vMid, LEnd2, vSwap
If (LEnd = 0) Then LEnd = UBound(AIn)
LBeg2 = LBeg
LEnd2 = LEnd
vMid = UCase(AIn((LBeg + LEnd) \ 2))
Do
Do While UCase(AIn(LBeg2)) < vMid And LBeg2 < LEnd
LBeg2 = LBeg2 + 1
Loop
Do While vMid < UCase(AIn(LEnd2)) And LEnd2 > LBeg
LEnd2 = LEnd2 - 1
Loop
If LBeg2 <= LEnd2 Then
vSwap = AIn(LBeg2)
AIn(LBeg2) = AIn(LEnd2)
AIn(LEnd2) = vSwap
LBeg2 = LBeg2 + 1
LEnd2 = LEnd2 - 1
End If
Loop Until LBeg2 > LEnd2
If LBeg < LEnd2 Then QuickSort AIn, LBeg, LEnd2
If LBeg2 < LEnd Then QuickSort AIn, LBeg2, LEnd
End Sub

If .NET is installed, use the .Sort method of a
CreateObject("System.Collections.ArrayList") object.
If not, do the ugly vbscript sort junk.
If you can get a directory listing to a file, use the directory options /OD
to sort.
If not, sorting by file date could get ugly.

"mayayana" <mayaXXyana@xxxxxx> wrote in message
news:uOpH2baIKHA.4436@xxxxxx

> If .NET is installed, use the .Sort method of aThat's an interesting logic: Loading a 200MB+
dependency, that won't be on all machines, is
less "ugly" than 20-odd lines of VBScript "junk"?

Eric schrieb:AFAIK, you can use the ArrayList only with one-dimensional arrays (with
items of the same type). So sorting a file collection (essentially a
table) will be difficult, perhaps even ugly in the sense that you
have to do silly things - like putting the sizes right aligned in the
front of the strings or formatting the m/d/y dates to make them
sortable.
Why do you say that? What is wrong with mayayana's quicksort?
Using dir will force you to shell out for each order you need.

The best method - in my opinion - would be the ADO approach.

[...]

"ekkehard.horner" <ekkehard.horner@xxxxxx> wrote in message
news:4a8db4bd$0$31331$9b4e6d93@xxxxxx-online.net...I would use the ADO disconnected recordset and the Sort method provided with
that object. I use it often. However, I don't know if it is faster than
mayayana's sort.

--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.rlmueller.net
--

> Even though I hate to require .NET, I foundat.....I'd be interested in .Net if it were all scriptable
COM, and if the runtime were not so gigantic. But as
it is, like Java, .Net seems to have very little purpose
outside of corporate intranet applet writing. I don't
have either the JVM or the .Net runtime installed
myself. I've never had any reason to do so, so it
would just be adding extra bloat and security risks
for no reason. And I wouldn't assume that others have
it installed. ...Which is not even getting into the
gargantuan resource load and initial lagtime that
System.Collections.ArrayList must be involve. (I
assume the whole 200+ MB of the .Net runtime
gets loaded for that call; and .Net software is
notoriously slow to get started because of that.)

But I suppose if one is writing only to a known
target audience that's known to be running .Net
software, then there's no reason not to
use what's available.

----------

With the code below: I don't do much with databases
and have never used ADO. So I find it hard to make
sense of what you wrote. The idea here is that you
have to create a record with several fields for each
item? And create a database for those records? In my
own sort tests I've been dropping a text file onto a script,
which uses Split(filetext, " ") to create an array of words
to sort. How does such an array get put into records?
Would it be something like:

For i = 0 to UBound(a)
objDataList.AddNew
objDataList("Word") = a(i)
objDataList.Update
Next

OR

For each fil in oFils
objDataList.AddNew
objDataList("Word") = Fil.Name
objDataList.Update
Next

Does that make sense? I'm not clear
about which fields are required or what
"Number" and "Index" are for.

And what do you mean by, "generally
you already have a recordset"? I was assuming
that the point was that ADO sorting could be
used to sort something like an array efficiently,
without necessarily having a database. Can it
really be more efficient to create a database
to be sorted, rather than just sorting an array?
The QuickSort I posted is virtually instant up
to several thousand items. It only took about
2 1/2 seconds to sort 70,000+ words.

> There are a lot of other sources on sorting algorithms and their benefitshowYes. I didn't do very extensive testing myself but
I did notice that. Bubble sort, especially, seems to get
slower and slower as the number of items increases.
Though with the exception of bubble sort, the methods I
was testing don't seem to matter all that much until one
gets into the hundreds of thousands or millions
of items. If a sort routine can sort, say, 10,000 items
in 150 ms, it may take 300 ms on the second run and
220 ms on the third. VBScript is just too crude to get
high accuracy in tests on that scale. But even though
there's a 200% difference in the range of results, they're
all essentially instant for most purposes. It's unlikely
that I'll ever need to sort more than a few hundred items.

mayayana wrote:
I use ADO to sort recordsets resulting from queries of Active Directory or
of SQL Server databases. You are correct that if the data is in an array,
you have the extra step of looping through the array and adding the values
to a disconnected recordset. Yes, this recordset is like a database, but it
is in memory and is much faster to work with than any database residing in a
file system. If you are sorting an array, I'm thinking the data came from
somewhere, and it might be possible to read the data into a disconnected
recordset in the first place instead of an array. I think it would be almost
as fast to populate a recordset as the array.

Following are two examples using disconnected ADO recordsets to sort. The
first converts an array of string values into a disconnected recordset to
sort. The second example retrieves all user names from Active Directory in
an ADO recordset, disconnects the recordset, and then sorts the values
before displaying. The example I posted earlier might have been confusing.
The "Number" and "Index" fields were just examples. My example below has
just one field.

This first example is one I used when I was comparing the performance of
several sort methods, including bubble sort (slowest), something called a
Benny sort (Benny Pedersen was active in the newsgroups), and using .NET to
sort (I have to admit the fastest method I found). This script shows that
the sort takes almost no time, but a fraction of a second is required to
setup the recordset and read the array values into it. It requires MDAC on
the client, but I believe any version will do:
==============
' ArraySort.vbs
' VBScript program to test several methods to sort.
Option Explicit

Dim arrAscending, adoDataList, strValue, intCount
Dim dtmT1, dtmT2, dtmT3

Const adVarChar = 200
Const MaxCharacters = 255

arrRandom = Array("Potato", "Lettuce", "Onion", "Bread", _
"Apple", "Orange", "Cherry", "Pear", "Milk", "Eggs", _
"Hamburger", "Ham", "Corn", "Beans", "Soup", "Pepper", _
"Salt", "Cornmeal", "Swiss Cheese", "Cheddar", "Basil", _
"Paprika", "Oregano", "Chili Powder", "Cottage Cheese", _
"French Fries", "Onion Rings", "Cola", "Waffles", "Peas", _
"Baked Beans", "Chili", "Pinto Beans", "Black Eyed Peas", _
"15 Bean Soup", "Cream", "Cream Cheese", "Yogurt", _
"Cake", "Ice Cream", "Cookies", "Butter", "Rye Bread", _
"Rice Cakes", "Whole Wheat Bread", "Butter Cookies", _
"Green Onion", "Celery", "Garlic Bread", "Pizza", _
"Tomato", "Grapes", "Black Beans", "Pancakes", "Red Beans", _
"Frozen Yogurt", "Tomato Paste", "Yeast", "Stewed Tomatoes", _
"French Toast", "Ginger Snaps", "Candy", "Chocolate", _
"Raisins", "Bay Leaves", "Rosemary", "Garlic Powder", _
"Thyme", "Macaroni", "Clam Chowder", "Split Pea")

dtmT1 = Timer()

' Setup disconnected recordset.
Set adoDataList = CreateObject("ADODB.Recordset")
adoDataList.Fields.Append "Value", adVarChar, MaxCharacters
adoDataList.Open

For Each strValue In arrRandom
adoDataList.AddNew
adoDataList("Value") = strValue
adoDataList.Update
Next

dtmT2 = Timer()

adoDataList.Sort = "Value"

dtmT3 = Timer()

' Display sorted values.
intCount = 0
adoDataList.MoveFirst
Do Until adoDataList.EOF
Wscript.Echo adoDataList.Fields.Item("Value")
intCount = intCount + 1
adoDataList.MoveNext
Loop
adoDataList.Close

Wscript.Echo "Number of values: " & CStr(intCount)
Wscript.Echo "ADO Sort setup: " & FormatNumber(dtmT2 - dtmT1, 4)
Wscript.Echo "ADO Sort : " & FormatNumber(dtmT3 - dtmT2, 4)
Wscript.Echo "ADO Sort total: " & FormatNumber(dtmT3 - dtmT1, 4)
==========
This second example shows how to sort all user names retrieved from Active
Directory. The trick is to specify the CursorLocation, CursorType, and
LockType to allow the recordset to be disconnected and sorted.
==========
Option Explicit

Dim objRootDSE, strDNSDomain, adoConnection
Dim strBase, strFilter, strAttributes, strQuery, adoRecordset
Dim strDN, intCount, strName

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3

' Determine DNS domain name.
Set objRootDSE = GetObject("LDAP://RootDSE")
strDNSDomain = objRootDSE.Get("defaultNamingContext")

' Use ADO to search Active Directory.
Set adoConnection = CreateObject("ADODB.Connection")
adoConnection.Provider = "ADsDSOObject"
adoConnection.Open "Active Directory Provider"

Set adoRecordset = CreateObject("ADODB.Recordset")
adoRecordset.ActiveConnection = adoConnection
adoRecordset.CursorLocation = adUseClient
adoRecordset.CursorType = adOpenStatic
adoRecordset.LockType = adLockOptimistic

' Search entire domain.
strBase = "<LDAP://" & strDNSDomain & ">"

' Filter on all user objects.
strFilter = "(&(objectCategory=person)(objectClass=user))"

' Comma delimited list of attribute values to retrieve.
strAttributes = "distinguishedName,sAMAccountName"

' Construct the LDAP query.
strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree"

' Run the query.
adoRecordset.Source = strQuery
adoRecordset.Open

' Disconnect the recordset.
Set adoRecordset.ActiveConnection = Nothing
adoConnection.Close

' Sort the recordset.
adoRecordset.Sort = "sAMAccountName"
adoRecordset.MoveFirst

' Enumerate the resulting recordset.
intCount = 0
Do Until adoRecordset.EOF
' Retrieve values.
strDN = adoRecordset.Fields("distinguishedName").Value
strName = adoRecordset.Fields("sAMAccountName").Value
Wscript.Echo strName & ": " & strDN
intCount = intCount + 1
adoRecordset.MoveNext
Loop

' Clean up.
adoRecordset.Close

Wscript.Echo "Number of objects: " & CStr(intCount)
--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.rlmueller.net
--