Hey, Scripting Guy! How Can I Incrementally Rename Files Based on the File Creation Date?

ScriptingGuy1

Hey, Scripting Guy! I am trying to get my photos sorted in chronological order, even though – because they come from different cameras – they have different names. For example, one name type starts with DSC followed by a sequential number issued by one camera; another name type starts with IMG followed by a different sequential number. I want to rename the files in a folder based on the creation date and time to a name like “NY_2007_” + a number + “.jpg”. Is there a way to accomplish my renaming wishes?

— EF

Hey, EF. Before we launch into today’s column we thought we’d take a moment to update everyone on the status of the 2008 Winter Scripting Games prizes and Certificates of Excellence. As of today we have sent emails out to everyone who won a prize and/or earned a Certificate of Excellence, and we have gotten back mailing addresses for most of those people. However, there are still a few holdouts; remember, if you don’t send us your mailing address it’s going to be very hard to get your prize/certificate mailed out to you.

At any rate, right now we’re busy collating addresses and getting them in a format suitable for use with Microsoft’s internal shipping tool; with any luck, we’ll be able to start sending things out early next week. But be patient: we have nearly 400 prizes and well-over 700 certificates to send out. That’s going to take a couple weeks for us to finish.

Shipping out all these prizes takes even longer because we’re supposed to keep doing all our regular work while we’re at. Regular work like what? Well, like writing a script that can sort all the files in a folder in chronological order, and then rename each of those files:

Const adVarChar = 200
Const MaxCharacters = 255

Set DataList = CreateObject("ADOR.Recordset")
DataList.Fields.Append "FileName", adVarChar, MaxCharacters
DataList.Fields.Append "FileDate", adVarChar, MaxCharacters
DataList.Open

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder("C:\New York")

Set colFiles = objFolder.Files

For Each objFile in colFiles
    DataList.AddNew
    DataList("FileName") = objFile.Path
    DataList("FileDate") = objFile.DateCreated
    DataList.Update
Next

DataList.Sort = "FileDate"

DataList.MoveFirst
i = 1

Do Until DataList.EOF
    If i < 10 Then
        x = CStr("000" & i)
    ElseIf i < 100 Then
        x = CStr("00" & i)
    ElseIf i < 1000 Then
        x = CStr("0" & i)
    Else
        x = i
    End If

    strNewName = "C:\New York\NY_2007_" & x & ".jpg"
    objFSO.MoveFile DataList.Fields.Item("FileName"), strNewName

    i = i + 1
    DataList.MoveNext
Loop

As it turns out, renaming files using VBScript is easy; the FileSystemObject can do that with a single line of code. It’s also easy to retrieve the creation date for any given file; all we have to do is ask the FileSystemObject to return the value of the DateCreated property. What is difficult is taking a collection of files and sorting them in chronological order; that’s because neither VBScript nor the FileSystemObject provide the ability to sort data. Needless to say, our ability to carry out this task depends in large part on our ability to sort data.

That’s where the “disconnected recordset” comes in. We aren’t going to talk about disconnected recordsets in any detail today; if you need some background information on these babies then take a peek at the Microsoft Windows 2000 Scripting Guide. For now we’ll just note that a disconnected recordset is a sort of “virtual” database, a database that exists only in memory. Do we really need a database that exists only in memory? Believe it or not, we do: a virtual database like this provides a place where we can store our file paths and dates and then, more importantly, sort those files by creation date.

In order to create a disconnected recordset we start by defining a pair of constants, adVarChar (which we’ll use to create a database field that uses the variant datatype) and MaxCharacters (which we’ll use to indicate that our database field can contain as many as 255 characters). After defining the two constants we create an instance of the ADOR.Recordset object (the object that lets us create a disconnected recordset); from there we then use the Append method to add two new fields to our recordset, one field named FileName and the other named FileDate:

DataList.Fields.Append "FileName", adVarChar, MaxCharacters
DataList.Fields.Append "FileDate", adVarChar, MaxCharacters

After we create these two fields we then call the Open method to open our disconnected recordset. At this point we’re ready to start adding data to the recordset … or at least we would be if we actually had any data to add to the recordset.

Fortunately for us, that’s an easy problem to take care of. For starters, we create an instance of the Scripting.FileSystemObject, then use the GetFolder method to bind to the folder C:\New York. That’s what these two lines of code are for:

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder("C:\New York")

Once we’re connected to the folder C:\New York we retrieve a collection of all the files in that folder simply by setting an object reference to the folder’s Files property:

Set colFiles = objFolder.Files

Pretty easy, huh?

Our next task is to retrieve the file path and creation date for each of these files, then add that information to the disconnected recordset. To that end we set up a For Each loop that loops us through each file in the collection. Inside that loop, we use this block of code to create a new record for the first file in the collection, setting the FileName field to the value of the file’s Path property and the FileDate field to the value of the DateCreated property:

DataList("FileName") = objFile.Path
DataList("FileDate") = objFile.DateCreated

After we call the Update method to officially add the record to the recordset we go back to the top of the loop and repeat the process with the next file in the collection.

Eventually we’ll have created a record for each file in the folder C:\New York. Once we’ve done that our next chore is to sort all the files by creation date. That’s a long and somewhat arduous process that involves a lot of – oh, sorry; as it turns out this isn’t really all that long and arduous after all. In fact, all we have to do is call the Sort method followed by the name of the field we want to sort by:

DataList.Sort = "FileDate"

At this point we have a disconnected recordset containing file paths and creation dates, a recordset in which the files have been sorted in chronological order. That can mean only one thing: it’s time for lunch. We’ll be back in an hour or so.

OK, we’re back. (Sorry we had to leave so abruptly, but today is Taco Salad Day.) As we were saying right before lunch, we’re now ready to start renaming files. Or at least we will be in a few seconds; before we can do any of that we need to call the MoveFirst method to position the cursor at the beginning of our recordset, then set the value of a counter variable named i to 1:

DataList.MoveFirst
i = 1

OK, now we’re ready to start renaming files. To do that, we start by setting up a Do Until loop designed to run until the recordset’s EOF (end-of-file) property is True. (In other words, we want to keep looping until we run out of records.) The first thing we do inside the loop? This:

If i < 10 Then
    x = CStr("000" & i)
ElseIf i < 100 Then
    x = CStr("00" & i)
ElseIf i < 1000 Then
    x = CStr("0" & i)
Else
    x= i
End If

What we’re doing here is – if necessary – adding leading zeros to our counter variable. For example, suppose i is less than 10 (the first condition in our If Then statement). In that case, we’re going to assign three leading zeroes (000) plus the value of i to the variable x. If i is equal to 1 (as it will be the first time through the loop) then x will be equal to this:

0001

If i is greater than 9 but less than 100 we’ll add two leading zeroes. If i is greater than 99 but less than 999 we’ll add one leading zero. And if i is greater than 999 we’ll just assign x the value of i. Depending on the number of files in the folder that will give us values similar to this:

0009
0099
0999
9999

Why do we bother with all that? Mainly it’s an aesthetic thing; that gives us a nice, neat directory listing that looks like this:

NY_2007_0003.jpg
NY_2007_0077.jpg
NY_2007_0444.jpg
NY_2007_1111.jpg

We like that better than a list that doesn’t use leading zeroes:

NY_2007_3.jpg
NY_2007_77.jpg
NY_2007_444.jpg
NY_2007_1111.jpg

After we’ve assigned a value to x we construct a new file name using this line of code:

strNewName = "C:\New York\NY_2007_" & x & ".jpg"

As you can see, there’s nothing very fancy here: we’re simply taking the string C:\New York\NY_2007, tacking on the value of x, then wrapping things up by adding the file extension .jpg. The first time through the loop that’s going to give us a file path that looks like this:

C:\New York\NY_2007_0001.jpg

We can then rename the first file in the collection (DataList.Fields.Item(“FileName”)) using a single line of code, just like we said we could:

objFSO.MoveFile DataList.Fields.Item("FileName"), strNewName

From there we increment the value of i by 1, call the MoveNext method to move to the next file in the recordset, and then repeat the process. When we’re done we should have a folder filled with files similar to these:

NY_2007_0001.jpg
NY_2007_0002.jpg
NY_2007_0003.jpg
NY_2007_0004.jpg

Etc. etc.

That should do it, EF; let us know if you have any questions. Remember, too that if you won a prize (or earned a certificate) during the Scripting Games you need to get your mailing address to us as soon as you can. That’s especially true for anyone who won a Dr. Scripto bobblehead doll. If you don’t claim your bobblehead soon, well, there will be plenty of other people more than willing to claim it for you.

ScriptingGuy1

Follow