Hey, Scripting Guy! How can I get the Date Picture Taken property from a .JPG file?

— MW

Hey, MW. Before we answer your question we should point out that this is likely to be the last Hey, Scripting Guy! column for quite some time to come. Not that we’re going anywhere, mind you. It’s just that on Saturday, the Seattle Seahawks will host the Dallas Cowboys in a nationally-televised football game. And that’s a problem. Early in December the Seahawks hosted Green Bay in a nationally-televised game and the Seattle area was promptly hit with a freak ice storm that shut the city down for two days. Shortly before Christmas the Seahawks hosted the San Francisco 49ers in a nationally-televised game. That same evening a “once-in-a-century” windstorm ripped through the region, and, among other things, the Scripting Guy who writes this column lost electricity for 6 long, cold days. Granted, the odds suggest that nothing that bad could possibly happen again. Nevertheless, the Scripting Guy who writes this column has been building an ark in his back yard, just in case. All he has to do now is round up two of every kind of animal and he’ll be in business.

Of course, as long as we have a little time before the next disaster strikes we might as well see if we can find a solution for you. Admittedly, a month or so ago we could have answered this question without any problem: “Sorry, MW, but there’s no way to get at that information, at least not by using a script.” However, that was before Microsoft released Windows Desktop Search 3.0. As it turns out, now there is a way to use a script to determine the date a digital photo was taken:

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open "Provider=Search.CollatorDSO;Extended Properties='Application=Windows';"

objRecordSet.Open "SELECT System.FileName, System.Photo.DateTaken FROM SYSTEMINDEX " & _
    "Where System.ItemFolderPathDisplay = 'D:\Europe' and System.FileExtension = '.jpg'", _ 
   objConnection

objRecordSet.MoveFirs

tDo Until objRecordset.EOF
    Wscript.Echo objRecordset.Fields.Item("System.FileName"), _
        objRecordset.Fields.Item("System.Photo.DateTaken")
    objRecordset.MoveNext
Loop

A few caveats before we explain how this script works. First and foremost, this solution does require you to install Windows Desktop Search 3.0; you can get more information on how to do that here. But that’s not a bad thing: setup is quick and easy, and Desktop Search 3.0 has all sorts of cool uses beyond determining the date a digital photo was taken. Second, this solution works only on Windows XP, Windows Server 2003, and Windows Vista; that’s because Desktop Search 3.0 is only available on those platforms. Will that change in the future? To be honest, we don’t know. But we hope so, because this really is a nice little technology.

As for the script itself, we start out by creating two objects: ADODB.Connection and ADODB.Recordset. If these names ring a bell, well, that’s not surprising: these are the two fundamental objects used with ActiveX Data Objects (ADO), the scripting technology that lets you connect to and work with such disparate data sources as text files, Active Directory, and, of course, databases. That’s one of the things we really like about Desktop Search 3.0: if you know how to write ADO scripts then you pretty much know how to write Desktop Search scripts as well.

After creating the Connection and Recordset objects we use the Open method to make a connection to the Desktop Search data store. Don’t worry about the details of this code; just consider it boilerplate text that you can paste in any time you want to use Desktop Search 3.0:

objConnection.Open "Provider=Search.CollatorDSO;Extended Properties='Application=Windows';"

Once we make the connection we then use the following code to return the FileName and DateTaken properties for all the .JPG files found in the folder D:\Europe:

objRecordSet.Open "SELECT System.FileName, System.Photo.DateTaken FROM SYSTEMINDEX " & _
    "Where System.ItemFolderPathDisplay = 'D:\Europe' and System.FileExtension = '.jpg'", _
    objConnection

And, sure, we could easily return this information for a single file, or for all the .JPG files on the computer. (Wouldn’t that script take forever to run? Obviously you haven’t tried Desktop Search 3.0, have you?) For more information on writing queries for Desktop Search 3.0 see our article Seek and Ye Shall Find. For now, simply note that we’re using a basic SQL statement that retrieves two property values (System.FileName and System.Photo.DateTaken) from the SYSTEMINDEX data store (the only data store available when using Desktop Search). Likewise, note the Where clause, which limits returned data to items found in the folder D:\Europe (that is, items where the ItemFolderPathDisplay is equal to D:\Europe) and have a FileExtension equal to .jpg.

And yes, unlike WMI you do need to include the dot as part of the file extension.

When we execute the Open method Desktop Search returns a recordset containing all the .JPG files found in the folder D:\Europe (just like we asked it to). All we have to do at that point is set up a Do Until loop that loops through that recordset (that is, that runs until the recordset’s EOF – End-of-File – property is True). Inside that loop we simply echo back the file name and the date the picture was taken; we then use the MoveNext method to move to the next item in the recordset and echo back that same information. That block of code looks like this:

Do Until objRecordset.EOF
    Wscript.Echo objRecordset.Fields.Item("System.FileName"), _
        objRecordset.Fields.Item("System.Photo.DateTaken")
    objRecordset.MoveNextLoop

And that’s all we have to do. When we run the script, we should get back information similar to this:

europe001.jpg 8/16/2005 11:22:30 AM
europe002.jpg 8/16/2005 11:22:49 AM
europe003.jpg 8/16/2005 11:23:02 AM
europe004.jpg 8/16/2005 11:24:41 AM
europe005.jpg 8/16/2005 11:25:14 AM

Best of all, we get this information back in just a few seconds. Very cool.

So are the Scripting Guys getting nervous as we get closer and closer to game time? No, not really. After all, Microsoft would never let anything happen to its most-valued employees; therefore, we simply plan on watching the game with those guys.