Hey, Scripting Guy! How Can I Copy Files Based on Multiple Criteria?

Hey, Scripting Guy! How Can I Copy Files Based on Multiple Criteria?

  • Comments 1
  • Likes

Hey, Scripting Guy! Question

Hey, Scripting Guy! How can I match multiple criteria in a file name? I need to copy all the files in a folder that are less than 24 hours old, and that have file names beginning with the letters FS, CG, or GPS. How can I do that?

-- EM

SpacerHey, Scripting Guy! AnswerScript Center

Hey, EM. You know, today is an exciting day at Microsoft; it’s the day when we get our Spam Notification. The Spam Notification is a list of all the emails that were addressed to us but were stopped by our “Spam Quarantine filtering service.” The Scripting Guy who writes this column loves to get the monthly Spam Notification, because he gets a kick out of looking at the Subject lines of the emails that have been tagged as Spam. Now, for better or worse, a lot of those Subject lines can’t be (or at least shouldn’t be) published on TechNet; you’ll have to wait for the unrated director’s cut of Hey, Scripting Guy! to see those. Nevertheless, there are a few intriguing Subject lines that we can print:

flimflam cableway. This would be a great name for a movie star: Summer Romance, starring Kate Hudson and Flimflam Cableway.

Tablet of happiness. We assume this a pill of some kind, although it would be very cool if it was a tablet of paper that made you happy.

Dont let her laugh at you again. Usually not a problem for the Scripting Guy who writes this column: no one ever laughs at him, no matter how hard he tries. Admit it: you’re not laughing now, are you? That’s what we thought.

No more falls in bed committed. To be honest, we’re not exactly sure what this means. But it sounds like good advice.

Favorite shop of Indiana Jones. We never really stopped to think about where Indiana Jones does his shopping; in fact, Indiana Jones doesn’t seem like the shopping type to us. But, then again, those fedoras don’t grow on trees.

Well, maybe on a fedora tree. But they don’t have many of those in the Seattle area

Note. Do you have a hankering to look just like Indiana Jones? Then buy your very own fedora online. Or, if you prefer, pick up your own Star Wars lightsaber. Note that the lightsaber requires three AA batteries, just like Luke Skywalker’s lightsaber.

Anyway, you can see why we like Spam Notification day so much: it’s fun to see all the different things people are trying to sell these days, even if we aren’t always sure what those things are (e.g., “Certainly blank,” which we believe refers to the Achievements and Accomplishments section of the Scripting Guys’ recent performance review). However, we did notice one glaring omission: no one is offering a script that can look through a folder, identify files less than 24 hours old that have file names beginning with the letters FS, CG, or GPS, and then copy those files to another location. (Well, OK, so maybe that’s what the email with the subject line What She Wants is all about. But we doubt it.)

But that’s OK. If Internet spammers aren’t going to write a script like that then the Scripting Guys will do it themselves:

strComputer = "."

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

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

Set objRegEx = CreateObject("VBScript.RegExp")
objRegEx.Pattern = "^FS|^CG|^GPS."

strCurrentDate = Now

For Each objFile In colFiles
    strFileDate = WMIDateStringToDate(objFile.CreationDate)
    intHours = DateDiff("h", strFileDate, strCurrentDate)
    If intHours <= 24 Then
        strSearchString = objFile.FileName
        Set colMatches = objRegEx.Execute(strSearchString)  
        If colMatches.Count > 0 Then
            strNewFile = "C:\Test\" & objFile.FileName & "." & objFile.Extension
            objFile.Copy(strNewFile)
        End If
    End If
Next

Function WMIDateStringToDate(dtmInstallDate)
    WMIDateStringToDate = CDate(Mid(dtmInstallDate, 5, 2) & "/" & _
        Mid(dtmInstallDate, 7, 2) & "/" & Left(dtmInstallDate, 4) _
            & " " & Mid (dtmInstallDate, 9, 2) & ":" & _
                Mid(dtmInstallDate, 11, 2) & ":" & Mid(dtmInstallDate, _
                    13, 2))
End Function

Let’s see what we have here. We start out in the same way many of our scripts start out: by binding to the WMI service on the local computer. After that we add the comment that we add to many of our scripts: yes, you can run this script against a remote computer. All you have to do is assign the name of that remote computer to the variable strComputer, like so:

strComputer = "atl-fs-001"

Once we’ve connected to the WMI service we can then use this line of code to retrieve a collection of all the files found in the folder C:\Scripts:

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

And yes, we know: EM doesn’t want all the files in the folder C:\Scripts; instead, she only wants files that were created in the past 24 hours, and that have file names starting with the characters FS, CG, or GPS. Unfortunately, though, it’s very difficult to work with date-time values in a WMI query, and it’s impossible to use regular expression matching in a WMI query. That means we don’t have much choice but to retrieve a collection of all the files in the folder and then run a couple different tests (Was the file created in the past 24 hours? Does the file name meet one of our three criteria?) on each and every file.

Note. Admittedly, we don’t have to use WMI here; for example, we could use the FileSystemObject instead. However, we still can’t use just one query to retrieve the exact files we want; that’s because the FileSystemObject doesn’t have a query language of any kind. And while we probably could use Windows PowerShell to attack this problem with a single (albeit long) line of code, we’d then run into two other problems: 1) EM isn’t using Windows PowerShell; and, 2) with Windows PowerShell 1.0 we wouldn’t be able to run this script against a remote computer.

In other words, maybe we do have to use WMI here after all.

After we retrieve our collection of files we create an instance of the VBScript.RegExp object, the object that lets us do regular expression matching in a VBScript script. That brings us to this line of code:

objRegEx.Pattern = "^FS|^CG|^GPS."

What we’re doing here is defining the criteria for our regular expression search. In this case, we’re looking for one of three possible matches. How do we know that there are three possible matches? Because of the pipe character (|), which serves the same function as the word or; in this Pattern we’re looking for ^FS or we’re looking for ^CG or we’re looking for ^GPS. See how that works?

That’s nice, but what exactly are we searching for (i.e., what does the syntax ^FS actually mean)? Well, in regular expressions the caret symbol (^) means “at the beginning of the string.” (Actually, the caret symbol has another meaning besides that, but we’ll ignore that other meaning for now.) As it turns out, this syntax is remarkably easy to decipher: we’re simply looking for the letters FS at the beginning of the string. And if we don’t find those two letters we’ll check to see if the letters CG or GPS can be found at the beginning of the string instead. If any of those things are true, then we have ourselves a match.

Next we use the Now function to assign the current date and time to a variable named strCurrentDate. Why do we do that? That’s right: we can’t determine if a file was created in the past 24 hours unless we know the current date and time. And then – finally! – we’re ready to start looping through our collection of files and checking to see if any of the file names meet our criteria.

So what’s the best way to loop through a collection of files? Well, it’s hard to go wrong with a For Each loop that loops through that collection. Therefore, our first step is to, well, set up a For Each loop that loops through our collection of files:

For Each objFile In colFiles

Inside this loop the first thing we do is execute the following line of code:

strFileDate = WMIDateStringToDate(objFile.CreationDate)

What we’re doing here is taking the value of the file’s CreationDate property and then using that value to call a function named WMIDateStringToDate. As you probably know, WMI uses the UTC (Universal Time Coordinate) format for storing date-time values; that means that a date like June 26, 2008, 8:30 AM, might be stored like this:

20080626083000.000000+480

Needless to say, that doesn’t do us a lot of good. Therefore, we use the function WMIDateStringToDate to convert this to a “normal” date-time value. We won’t discuss the UTC format or the workings of the WMIDateStringToDate function in any detail today; if you’d like a little more information about these things take a peek at the Microsoft Windows 2000 Scripting Guide. Suffice to say that the function grabs the individual date-time pieces from the UTC value (for example, the first four digits in the CreationDate represent the year that the file was created) and creates a date-time value that VBScript can work with.

And what exactly do we want VBScript to do with this converted date-time value? Well, we’re going to ask it to use the DateDiff function to determine whether the file was created in the past 24 hours:

intHours = DateDiff("h", strFileDate, strCurrentDate)

What DateDiff does is subtract the file’s creation date from the current date and time (stored in the variable strCurrentDate). Because we used the “h” parameter that means that DateDiff will return the difference in hours. In other words, if our first file was created 12 hours ago then DateDiff will return the value 12 (which we promptly stash in the variable intHours).

At that point we can check to see if intHours is less than or equal to 24:

If intHours <= 24 Then

What if intHours isn’t less than or equal to 24? That’s fine; that just means that the file wasn’t created in the past 24 hours. In turn, that means we simply go back to the top of the loop and try again with the next file in the collection.

But what if intHours is less than or equal to 24? That means we need to check and see if the file name starts with any of our three designated letter sets. And how do we do that? Why, by running these two lines of code, of course:

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

In the first line we’re simply taking the value of the FileName property (minus the file extension) and storing it in a variable named strSearchString. In line 2, we use the Execute method to run a regular expression search against this file name.

And how do we know if the file name fit our criteria? That’s easy; we just check the regular expression Matches collection; more specifically, we check to see if the collection’s Count property is greater than 0:

If colMatches.Count > 0 Then

If the Count is greater than 0 that can mean only one thing: we have a match. In turn, that means we need to do two things: we need to specify the location to where we want to copy the file (a location that must consist of a complete file path, including file name and extension); and, we need to copy the file to that location. That’s what these two lines of code are for:

strNewFile = "C:\Test\" & objFile.FileName & "." & objFile.Extension
objFile.Copy(strNewFile)

And then it’s back to the top of the loop, where we do this all over again with the next file in the collection.

And that should do it, EM. It’s not exactly what you were after, but it should work. And, as a little bonus, we showed you how you can search for any one of three possible patterns in a single regular expression. Can’t beat that, huh?

Anyway, if that doesn’t solve all your problems, EM, well, just let us know; after all, we have access to an email that promises to “Put an end to your woes.” That sounds pretty nice. (Well, for those of you who, unlike the Scripting Guys, actually have woes.) And, of course, there’s always the Tablet of Happiness. Like we said, just let us know.

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • Hi,

    How to work for sub folders also