Hey, Scripting Guy! How can I list all the files in a folder, ordered by creation date?

— CL

Hey, CL. You know, if we were ambitious and hard-working, we’d sit down and write you a script that uses WMI to return all the files in a folder. That script would grab information about all those files and store that data in a disconnected recordset. We’d then set the sort order on that recordset to arrange the files by creation date and time. (Well, after we went through a lot of gyrations to convert WMI’s date-time values to a readable date-time format.) Finally we’d echo all the values in the recordset back to the screen. It would take a lot of time and involve a lot of coding, but in the end you’d have your sorted list of files and everyone would say, “Wow, those Scripting Guys really go the extra mile for their readers, don’t they?”

As it turns out, though, we aren’t ambitious and hard-working; instead, we’re the Scripting Guys. And as the Scripting Guys we’re always looking for the quickest and easiest way to solve a problem. Therefore, instead of writing a long and complicated script we grabbed a copy of Log Parser 2.2 and dashed off just a few lines of code:

Set objLogParser = CreateObject(“MSUtil.LogQuery”)
Set objInputFormat = CreateObject(“MSUtil.LogQuery.FileSystemInputFormat”)
objInputFormat.Recurse = 0

Set objOutputFormat = CreateObject(“MSUtil.LogQuery.NativeOutputFormat”)
objOutputFormat.rtp = -1
strQuery = “SELECT Name, CreationTime FROM ‘C:\Scripts\*.*’ ” & _
    “WHERE NOT Attributes LIKE ‘%D%’ ORDER BY CreationTime”
objLogParser.ExecuteBatch strQuery, objInputFormat, objOutputFormat

And guess what? Not only does this script work, but it returns the list of files – sorted by creation date and time – in practically no time whatsoever. Very cool.

Admittedly, we usually don’t recommend solutions that aren’t built into the operating system; that’s because we hate to make people download and install something if it’s not absolutely necessary. When it comes to enumerating files, however, it’s more than worth your while to download and install Log Parser; any time you have to grab information about a set of files you’ll find Log Parser way better than either WMI or the FileSystemObject. Seven or eight lines of Log Parser code versus 60 or 70 lines of WMI code? We’ll leave that decision up to you.

We won’t take time to explain everything there is to know about Log Parser; for more information, you might want to see the Tales from the Script column All You Need is Log (Well, Log Parser). For now, we’ll just note that the script begins by creating an instance of the Log Parser object, better known by the catchy name MSUtil.LogQuery. We then create two additional objects, the first specifying the object we’re working with (in this case, the file system, though we could also work with event logs, Active Directory, the registry, and other items), the second indicating the output type we want to use (in this sample script, all we’re doing is writing data to the command window). These two lines of code create the input object and tell Log Parser not to retrieve files from any subfolders:

Set objInputFormat = CreateObject(“MSUtil.LogQuery.FileSystemInputFormat”)
objInputFormat.Recurse = 0

What if we did want to retrieve values for any and all subfolders? In that case all we’d have to do is set the value of the Recurse property to -1:

objInputFormat.Recurse = -1

Meanwhile, these two lines of code create the output object, and tell Log Parser to display all the data without pausing:

Set objOutputFormat = CreateObject(“MSUtil.LogQuery.NativeOutputFormat”)
objOutputFormat.rtp = -1

Alternatively, we could have told Log Parser to show us 10 rows of data, pause until we pressed a key on the keyboard, then show us the next 10 rows of data. To show data in batches of 10 all we’d have to do is set the value of the rtp property to 10:

objOutputFormat.rtp = 10

Next we configure the SQL query for retrieving file information. If you have a little knowledge about SQL this query should be relatively easy to parse; as you can see, we’re just asking for the Name and CreationTime of all the files in C:\Scripts. In addition, we want the returned data ordered by creation date and time, with the oldest files listed first:

strQuery = “SELECT Name, CreationTime FROM ‘C:\Scripts\*.*’ ” & _
    “WHERE NOT Attributes LIKE ‘%D%’ ORDER BY CreationTime DESC”

The only unusual thing in this query is our WHERE clause: WHERE NOT Attributes LIKE ‘%D%‘. Without going into a long explanation, this clause filters out folders, and thus returns only files. A file system object that contains the Directory attribute is a folder; because we don’t want folders we use the WHERE NOT syntax to eliminate any object possessing the Directory (%D% for short) attribute.

Finally we call the ExecuteBatch method to run the query and write the returned data to the command window. And, in a second or two, we get back something that looks like this:

And, no, we didn’t have to enter any special commands to get this nice tabular output; Log Parser takes care of all that for us. That is really nice of it, isn’t it?

Granted, we didn’t have to work very hard to get back these results. But it’s quick and it’s easy. And look at it this way: nobody has to know we didn’t work very hard, do they?