Hey, Scripting Guy! How Can I Sort Digital Files By the Date Each Picture Was Taken?

ScriptingGuy1

Hey, Scripting Guy! I read your article on renaming image files so that the pictures could then be sorted in chronological order. When I ran your script, however, it didn’t quite work for me: the files were renamed, but the pictures weren’t sorted the way I had hoped they would be. I think that’s because the pictures were being renamed based on the day I downloaded them to my computer rather than the date that the picture was taken. Is there a way to rename these files based on the date the picture was taken?
— JH

Hey, JH. You know, back in 1975, in the Bahamas, a man riding a moped was killed when he collided with a taxi. One year later the man’s brother was killed in the exact same way: he was riding a moped when he collided with a taxi. Kind of spooky? Just wait; you haven’t heard anything yet. For one thing, the man was riding the exact same moped his brother had been riding. For another, he crashed into the exact same taxi, driven by the same cab driver and ferrying the very same passenger!

And here’s the really weird part: neither of the two brothers had the least interest in writing scripts that can be used to rename image files!

Well, OK, maybe that last part isn’t all that weird. But hey, we had to find a way to explain what this anecdote has to do with a daily column devoted to system administration.

Oh, wait: here’s the really weird part: we actually do have a way to connect this anecdote to a daily column devoted to system administration. In our last column, a column that dealt with the horizontal and vertical sizes of a series of .JPG files, we came up with a solution that required us to use the Shell object and the GetDetailsOf method, an object and method we hadn’t relied on in quite some time. And now, in this very next column, we’re going to use the Shell object and the GetDetailsOf method again!

Oh, and did we mention that today’s column was written by the exact same Scripting Guy who wrote the last column!?! (And edited by the exact same Scripting Editor.) Amazing, but true.

And yes, this is getting a little creepy, isn’t it?

Here’s something a little more fun that you can try when you have a spare moment. Go to this Web site and enter your date of birth; an application connected to the site will then search the value of pi to see if those exact digits appear anywhere in that value. For example, the Scripting Editor was born on July 12, 1896 so we entered the following value:

07121896

Here’s what we got back:

The string 07121896 occurs at position 97,674,811 counting from the first digit after the decimal point.

Try it yourself. There’s a better than 80 percent chance that any such birthdate can be found somewhere in the value of pi.

But here’s the most amazing thing of all: not only does today’s column contain irrelevant ramblings about coincidences, but it also includes a script that can rename image files based on the date that the picture was taken! In fact, today’s column just happens to include this script:

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 objShell = CreateObject ("Shell.Application")
Set objFolder = objShell.Namespace ("C:\Images")

For Each objFile in objFolder.Items
    DataList.AddNew
    DataList("FileName") = objFile.Path
    DataList("FileDate") = objFolder.GetDetailsOf(objFile, 25)
    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:\Images\NY_" & x & ".jpg"
    objFSO.MoveFile DataList.Fields.Item("FileName"), strNewName

    i = i + 1
    DataList.MoveNext
Loop

As you can see, we start things out by creating a pair of constants, adVarChar (with a value of 200) and MaxCharacters (with a value of 255). Is it just a coincidence that we create two constants at the beginning of this script? Well, no, not really: we need adVarChar to create a pair of variant fields in our disconnected recordset, and we need MaxCharacters to set the maximum number of characters that can be stored in these fields to 255.

Oh, by the way: we’re going to use a disconnected recordset in our script. If you aren’t familiar with disconnected recordsets, they’re essentially database tables that exist only in memory; they aren’t tied to an actual database. We’re using a disconnected recordset because it provides an easy place for us to store data, and an even easier way to sort data.

Speaking of disconnected recordsets, by a remarkable coincidence the next four lines of code in our script actually create a disconnected recordset (amazing, but true):

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

In line 1, we create an instance of the ADOR.Recordset object; the object that – yes, that’s right, the object that creates a disconnected recordset. (Believe it or not, we were just about to say that exact same thing.) In the next two lines we create two fields, FileName and FileDate, fields that we’ll use to store the file name and the date that each digital photo was taken. As for line 4, well, that’s the best line of all: that’s the line we use to open the recordset so we can begin to add data to it.

We always save the best for last.

Once the recordset is open we create an instance of the Scripting.FileSystemObject, an object we’ll then set aside for future use. (This is the object we’ll use to actually rename the files.) We then create an instance of the Shell.Application object, the object that lets us mine the metadata of a file or folder (metadata like, say, the day that a digital photo was taken). After that we use this line of code to bind to the folder C:\Images, the folder where all of our digital photos are stored:

Set objFolder = objShell.Namespace ("C:\Images")

Ah, but wait. Maybe that’s currently the only place where our digital photos are stored, but in just a second we’re going to store those photos (or at least selected information about those photos) in a second spot: our disconnected recordset. In fact, that’s what this block of code is for:

For Each objFile in objFolder.Items
    DataList.AddNew
    DataList("FileName") = objFile.Path
    DataList("FileDate") = objFolder.GetDetailsOf(objFile, 25)
    DataList.Update
Next

So what are we doing here? Well, to begin with, we’re looping through all the digital photos (that is, all the objects in the C:\Images folder’s Items collection). For each photo in the collection we call the AddNew method to create a new, blank record in our recordset. We use the next line of code to assign the complete file Path to the new record’s FileName property, then call the GetDetailsOf method in order to retrieve the date that the picture was taken and assign that value to the FileDate field:

DataList("FileDate") = objFolder.GetDetailsOf(objFile, 25)

DataList("FileDate") = objFolder.GetDetailsOf(objFile, 12)

After that we call the Update method to add the record to the recordset, then go back to the top of the loop and repeat the process with the next digital photo in the collection.

As soon as we finish adding information about each file to the recordset we call the Sort method to sort the files by the date and time that each photo was taken:

DataList.Sort = "FileDate"

Once we have a sorted list we use the MoveFirst method to move to the first record in the recordset, then use this line of code to set the value of a counter variable named i to 1:

i = 1

What do we need a counter variable for? Believe it or not, we were just about to tell you that.

As it turns out, we’re now 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 grabbing items out of the recordset – and renaming files – 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 adding leading zeros to our counter variable as needed. 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. What does that mean? That means that, if i is equal to 1 (as it will be the first time through the loop), then x will be equal to this:

0001

See how that works? Now, what if i is greater than 9 but less than 100? In that case, 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? Because it gives us a nice, neat directory listing that looks like this:

NY_2008_0009.jpg
NY_2007_0099.jpg
NY_2007_0999.jpg
NY_2007_9999.jpg

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

strNewName = "C:\Images\NY_2008_" & x & ".jpg"

As you can see, there’s nothing very fancy here: we’re simply taking the string C:\Images\NY_2008, tacking on the value of x, then wrapping things up by adding the file extension .jpg. In case you’re wondering, C:\Images is, of course, the folder where our digital files are stored. As for NY_2008, well, that’s just a prefix we’re attaching to the beginning of each file name.

The first time through the loop that’s going to give us a file path that looks like this:

C:\Images\NY_2008_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_2008_0001.jpg
NY_2008_0002.jpg
NY_2008_0003.jpg
NY_2008_0004.jpg

Best of all, NY_2008_0001.jpg is the very first picture that was taken; NY_2008_0002.jpg is the second digital photo taken; etc., etc.

So there you have it, JH. Is it just a coincidence that you asked a question and the Scripting Guys actually answered it? Well, as a matter of fact it is. But let’s not tell anyone else that, OK?

ScriptingGuy1

Follow