Hey, Scripting Guy! Question

Hey, Scripting Guy! I have a bunch of files that I need to move to new folders; the folder a given file will be moved to is based on the year, a value that appears in the file name. How can I write a script to move these files?

-- LP

SpacerHey, Scripting Guy! AnswerScript Center

Hey, LP. Before we launch into today’s column, we should clarify a couple of points which came up in an “Editor’s Note” added to yesterday’s column. Yesterday the Scripting Editor – for no apparent reason – felt the need to point out two things. To quote from her Note:

Last week, the Scripting Guy who writes this column mentioned the Washington vs. Oregon football game. This week he didn’t. Enough said.

The Scripting Guy who writes this column predicted the winner of the World Series. He also mentioned public ridicule…Boston Red Sox fans, feel free to ridicule.

Note. No, we don’t hold this unprovoked attack against her at all. As it turns out, the Scripting Editor got a ticket yesterday morning for parking her broom in a no-parking zone, and that obviously affected her attitude towards her work. Usually the Scripting Editor is out-going, fun-loving, and a sheer delight to be around.

Or at least she would be if we had a different Scripting Editor.

At any rate, in response to that Editor’s Note the Scripting Guy who writes this column feels the need to point out two things himself:

Whatever it was that Oregon and Washington played last Saturday it definitely wasn’t a football game. 55-34? That’s not football, that’s … uh … something that isn’t football. Besides, Oregon fans have to live in Oregon. To defeat their football team on top of that would be just plain cruel.

Although the Scripting Guy who writes this column did pick the Cleveland Indians to win the World Series (come on, Cleveland; you can’t win 1 lousy game after taking a 3-games-to-1 lead?) he also gave himself an out by saying that, if his prediction did not hold up, he would simply travel back in time and change that prediction. This is something he plans to do later tonight. If you wake up tomorrow morning and discover that dinosaurs once again rule the world, well, then you’ll know that his time-traveling expedition didn’t go off exactly as planned.

We should also note that the Scripting Editor has a tendency to point out, over and over again, that, “It takes you forever to get around to answering the day’s question.” To be honest, we have no idea what she means by that. After all, here we are only 7 or 8 paragraphs into today’s column and we’re already showing you a script that can move files to a new folder based on a year value that appears in the file name:

strComputer = "."

Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")

Set colFiles = objWMIService.ExecQuery _
    ("ASSOCIATORS OF {Win32_Directory.Name='C:\Test'} Where " _
        & "ResultClass = CIM_DataFile")

Set objRegEx = CreateObject("VBScript.RegExp")

For Each objFile in colFiles
    objRegEx.Global = True   
    objRegEx.Pattern = "\d{4}"

    strSearchString = objFile.FileName
    Set colMatches = objRegEx.Execute(strSearchString)

    strYear = colMatches(0).Value

    strNewFile = "C:\Test\" & strYear & "\" & objFile.FileName & _
        "." & objFile.Extension
    objFile.Copy(strNewFile)
    objFile.Delete
Next

Take forever, indeed. Somebody got up on the wrong side of the coven this morning!

Before we talk about how this script works let’s talk about the files we’re dealing with. We’re assuming we have a bunch of files with names similar to these:

File2005a.txt
File2006a.txt
File2006b.txt
File2006c.txt
File2006d.txt
File2007a.txt
File2007b.txt

The truth is, it doesn’t really matter what the file names look like, with two exceptions: the year must be specified using 4 digits, and we can only have one 4-digit number in the name. Why that latter stipulation? Well, suppose we have a file name like this:

File2005_3000.txt

With a name like File2005_3000.txt we actually have two 4 digits numbers: 2005 and 3000. In this case we could probably guess which number is the year and which number is, well, some other number. But suppose have a file name like this:

File1998_1999_2000.txt

Now what do we do? Most likely file names like this follow some sort of hard-and-fast rule, e.g., “The second 4-digit number represents the year.” However, because we don’t know what your hard-and-fast rules are we can’t account for them all. Therefore, we’re only going to show you how to grab all the 4-digit values; it’s up to you to determine which of those values represents the year.

As for the script itself, we start out by connecting to the WMI service on the local computer. Could we perform this same task against a remote computer? We knew someone was going to ask that, which is why we opted to use WMI to move these files rather than, say, the FileSystemObject. And because we did use WMI, the answer to your question is this: yes, we can perform this same task against a remote computer. All we have to do is assign the name of that computer to the variable strComputer, like so:

strComputer = "atl-fs-01"

After we make our connection to the WMI service, we use this crazy-looking line of code to retrieve a collection of all the files found in the folder C:\Test:

Set colFiles = objWMIService.ExecQuery _
    ("ASSOCIATORS OF {Win32_Directory.Name='C:\Test'} Where " _
        & "ResultClass = CIM_DataFile")

Like we said, it’s crazy looking, which is the nature of WMI’s Associators Of queries. However, all we’re doing is retrieving all the files (instances of the CIM_DataFile class) that can be found in (are associators of) the folder C:\Test (Win32_Directory.Name='C:\Test'). Once we have that collection in hand we next create an instance of the VBScript.RegExp object; at that point, we’re ready to do some serious file management.

What’s that? What’s the VBScript.RegExp object for? Well, that’s the object that enables us to do a regular expressions search on each file name. We don’t know, in advance, what year (or years) are going to appear in each file name; the year could be 1912, it could be 1987, it could be 2008. If we wanted to, we could create a seemingly-endless series of InStr commands that methodically look for each of these possibilities: 1913; 1914; 1915; 1916; 1917; etc. Alternatively, we could use a simple regular expression to search for any value consisting of 4 consecutive digits. Hmmm, do a lot of work, or do hardly any work at all? Try to guess which option the Scripting Guys chose.

Speaking of the Scripting Guys, any time we get back a collection of all the files in a folder we can never resist setting up a For Each loop and looping through each and every file in that collection. Today’s script is no exception. Consequently, we set up a For Each loop to do that very thing: loop through each and every file in our collection. Inside that loop, we kick things off by assigning values to two properties of our regular expressions object:

objRegEx.Global = True   
objRegEx.Pattern = "\d{4}"

Setting the Global property to True simply tells the script to search for all instances of the target pattern; if this property were not set to True the script would find the first 4-digit number and then quit searching. In our case that doesn’t matter; after all, our file names have only one 4-digit number. We just tossed this in because, in general, it’s a good idea to set the Global property to True when doing a regular expressions search.

As for the Pattern property, this is simply the value we’re searching for. Granted, we don’t know the exact value we’re searching for. But because we’re using regular expressions, we don’t have to know the exact value; instead, we can search for a specified pattern (like, say, 4 consecutive digits). The \d{4} is simply regular expression syntax for 4 consecutive digits.

Once we’ve assigned values to our two regular expression properties we then assign the Name of the first file in the collection to a variable named strSearchSrting:

strSearchString = objFile.FileName

From there we can call the Execute method to search the file name for any instances of our target pattern (4 consecutive digits):

Set colMatches = objRegEx.Execute(strSearchString)

Assuming we find an instance of the target pattern (something we can be pretty sure of in this case) those instances will all be stored in the regular expression Matches collection. Because the first such instance (and only instance) will be the year, and because the first such instance will be given an index number of 0, that means we can retrieve the actual year (the match Value) by using this line of code:

strYear = colMatches(0).Value

Just like that, the file year (or at least the year specified in the file name) will get stored in the variable strYear.

The rest is easy. Once we know the year we can then construct a brand-new file path using this line of code:

strNewFile = "C:\Test\" & strYear & "\" & objFile.FileName & _
    "." & objFile.Extension

In that line of code we’re simply stringing together the following values:

C:\Test\

2005 (the file year, as stored in the variable strYear)

\ (to separate the folder name from the file name)

File2005a (the file name, stored in the FileName property)

. (the period required to delineate the file extension)

txt (the file extension, stored in the Extension property)

Put them altogether and they spell this:

C:\Test\2005\File2005a.txt

The key part? The 2005 embedded in the middle of the path; that’s the folder (C:\Test\2005) we’ll be moving this file to.

For some strange reason, WMI doesn’t actually have a method for moving files. Therefore, we need to take a two-step approach to moving files: we first copy the original file to the new folder (C:\Test\2005), then we delete the original file (leaving the copy File2005a.txt in the 2005 folder). That’s what these two lines of code are for:

objFile.Copy(strNewFile)
objFile.Delete

It’s not as elegant as having a single Move command, but it accomplishes the same thing, albeit in one extra step.

And then it’s back to the top of the loop, where we repeat the process with the next file in the collection.

Note. We should add that this script assumes that the target folders (e.g., the 2005 folder) already exist. What if those folders don’t exist? In that case, take a peek at one of our previous columns, which provides a little more information on checking for the existence of a folder and, if the folder doesn’t exist, creating it.

That should do it, LP. Before we go we would also like to apologize to the Scripting Editor. Yesterday we stated that the Scripting Editor was third runner-up in the 1949 Miss Iowa Beauty Pageant. Needless to say, that made the Scripting Editor seem far older than she really is, a misconception we are truly sorry for creating. In truth, the Scripting Editor was third runner-up in the 1953 Miss Iowa Beauty Pageant, several years later than originally stated.

Scripting Guys Trivia: At the time, the Scripting Editor was also the oldest person to ever win a major beauty contest in the USA. Amazing but true!

Again, our apologies to both the Scripting Editor and our readers for any inconvenience or misconceptions this error might have caused.

Editor’s Note: Given that the Scripting Guy who writes this column admitted, twice, that the Scripting Editor was fully capable of winning “a major beauty contest,” the Scripting Editor graciously accepts his apology. (Even though he’s still off by quite a few years.)