Hey, Scripting Guy! Question

Hey, Scripting Guy! How can I truncate file names by removing a common beginning and ending, leaving only the unique middle portion of the name?

-- RE

SpacerHey, Scripting Guy! AnswerScript Center

Hey, RE. Before we answer your question we’d like to point out that we’ve finally posted the winners of the Scripting Guys TechEd Challenge. (If you are a winner we’ve got good news: our shipping materials have arrived, which means that, at long last, your prizes are out the door and on the way.) We’ve also posted one possible solution to the puzzle, a solution the Scripting Guys came up with and – we add proudly – a solution that would have earned us a perfect score of 140 points. Wasn’t it really, really hard to come up with a perfect score? No, of course not. Not for the Scripting Guys anyway.

Note. OK, so that might be due, in part, to the fact that we cheated: we started with a list of words and then derived the puzzle using that list. But hey, that’s about the only way the Scripting Guys could even hope to come up with a perfect score.

And even that took a few tries.

When we first posted the challenge we were concerned that people would find it too difficult. “This is hard,” we thought. “We need to offer a really big incentive in order to get people to even try solving the puzzle.” With that in mind, we made a ridiculous offer that we knew we’d never have to follow up on: if anyone turned in a perfect score, we’d rename the Script Center after them (well, for one day, that is).

Much to our surprise, nine different people submitted perfect scores, and some of those people submitted more than one perfect entry. (At some point you’d think we’d learn that just because the Scripting Guys find something to be really, really hard that doesn’t mean that anyone else will find that same task to be even the slightest bit challenging.) To tell you the truth, we weren’t prepared for all those perfect entries. Nevertheless, a deal is a deal, so sometime in the very near future we’ll begin renaming the Script Center in honor of our grand prize winners.

Note. So how does TechNet feel about us renaming the Script Center in honor of our grand prize winners? Surprisingly enough, TechNet is 100% in favor of this; in fact, the good people at TechNet think that this is by far the best idea the Scripting Guys have ever come up with.

Or at least that’s what they would think, provided they actually knew about all this.

Or at least that’s what we think they would think.

Now back to RE’s question. RE has a folder that has a series of files with names similar to this:

8901 George.txt
File 1234 George.txt
File 2345 George.txt
File 3456 George.txt
File 4567 George.txt
File 5678 George.txt
File 6789 George.txt
File 7890.txt

As you can see, most of the file names start with File and then end with George; a few files start with File but don’t end with George, and a few others end with George but don’t start with File. All the files, however, have a unique “middle name;” for example, the file File 1234 George.txt has a “middle name” of 1234. What RE would like to do is take that file name and remove the File and/or the George, resulting in file names like this:

8901.txt
1234.txt
2345.txt
3456.txt
4567.txt
5678.txt
6789.txt
7890.txt

How can he do that? Here’s one way:

strComputer = "."

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

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

For Each objFile In colFiles
    strPath = objFile.Drive & objFile.Path
    strExtension = objFile.Extension
    strFileName = objFile.FileName

    If Left(strFileName, 5) = "File " Then
        intLength = Len(strFileName)
        strFileName = Right(strFileName, intLength - 5)
    End If

    If Right(strFileName, 7) = " George" Then
        intLength = Len(strFileName)
        strFileName = Left(strFileName, intLength - 7)
    End If

    strNewName = strPath & strFileName & "." & strExtension
    errResult = objFile.Rename(strNewName)
Next

We actually get quite a few questions similar to this, questions about removing specific bits of text that have been tacked onto the beginning and/or the end of file names. Because of that, and because that’s what we’re supposed to do in this column anyway, let’s take a few minutes to walk through the code and see if we can figure out what’s going on here.

The script starts out by connecting to the WMI service on the local computer. Could we modify this script to perform the same task against a remote computer? You bet we can; to do that, simply assign the name of the remote computer to the variable strComputer:

strComputer = "atl-fs-01"

After connecting to the WMI service we then use the following bit of code to return a collection of all the files (instances of the CIM_DataFile class) found in the folder C:\Test:

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

When the collection comes back we set up a For Each loop to loop through all the files in that collection. And then we get down to business.

Admittedly, there are a couple different ways we could go about this task; we’ve opted to use an approach that requires a few extra lines of code but is a little safer to use. What makes it “safer?” Here’s one example. We could just use the Replace function to replace any instances of target terms File and George in the file path. So why don’t we? Well, suppose we had a file named File George Washington File George.txt. Doing a blank search-and-replace operation would result in this:

Washington.txt

In other words, we would have removed more than just the beginning and the ending. In turn, that means that we could run into name collisions if the folder also included a file named, say, File Washington George.txt. Likewise, suppose the folder we’re working with is named Documents From George. Removing all instances of George would delete the term from the folder path as well, something which would lead to all sorts of trouble. And the Scripting Guys don’t want trouble.

Which is why we didn’t mention that rename-the-Script-Center thing to anyone from TechNet.

At any rate, the first thing we do inside the loop is execute the following three lines of code:

strPath = objFile.Drive & objFile.Path
strExtension = objFile.Extension
strFileName = objFile.FileName

All we’re doing here is grabbing the individual pieces of the file path. If we have a file named C:\Test\File 1234 George.txt, that means that our variables will end up being assigned the following values:

Variable

Value

strPath

C:\Test\

strExtension

txt

strFileName

File 1234 George

For more information about properties like Drive and Path, see the Microsoft Windows 2000 Scripting Guide.

Our next step is to determine whether or not the file name starts with the characters File and, if it does, delete those characters. That’s what this block of code is for:

If Left(strFileName, 5) = "File " Then
    intLength = Len(strFileName)
    strFileName = Right(strFileName, intLength - 5)
End If

As you can see, we’re using the Left function to determine whether the first five characters in our file name are equal to File (note the blank space at the end). Let’s assume that they are. In that case, we then use the Len function to determine the number of characters in the string (i.e., the length of the string), storing that value in a variable named intLength:

intLength = Len(strFileName)

That brings us to this line of code:

strFileName = Right(strFileName, intLength - 5)

This is where we strip the word File from the beginning of the string. What we’re doing here is using the Right function to grab characters from the end of the string, working our way back towards the beginning. How many characters are we going to grab? We’re going to grab the total number of characters in the string, minus 5. And why “minus 5?” That’s easy: this will scoop all the characters in the string except for the first 5. In other words, if we have a file named File 1234 George strFileName will be assigned the following value:

1234 George

Which means that we’re now halfway towards our goal of truncating the first file name.

We then use a similar process to determine whether the term George (again, note the blank space, this time in front of the term) appears at the end of the file name; if so, we go ahead and delete that term as well. That’s what this block of code is for:

If Right(strFileName, 7) = " George" Then
    intLength = Len(strFileName)
    strFileName = Left(strFileName, intLength - 7)
End If

What’s the net result of all this? The net result is that strFileName is now equal to the file’s “middle name:”

1234

That’s great; this is just what we wanted. However, we still have one problem: when using WMI you can’t rename a file unless you pass the Rename method the complete file path. (Which means that simply having a file name of 1234 doesn’t do us much good.) With that in mind, we use the following line of code to construct a new file path, storing that value in the variable strNewName:

strNewName = strPath & strFileName & "." & strExtension

Nothing too fancy here; we’re simply combining the following values:

C:\Test\

1234

.

txt

Put them all together and strNewName ends up with this value:

C:\Test\1234.txt

And you know what? You’re right: that does look an awful lot like a complete file path, doesn’t it? We’re glad you pointed that out, because that means we can go ahead and call the Rename method and actually rename the file:

errResult = objFile.Rename(strNewName)

And then it’s back to the top of the loop, where we repeat this process with the next file in the collection. When all is said and done, the folder C:\Test will be the proud owner of the following set of files:

8901.txt
1234.txt
2345.txt
3456.txt
4567.txt
5678.txt
6789.txt
7890.txt

Not bad, not bad at all.

Incidentally, if you liked the TechEd Challenge we will likely have some similar puzzles for you in the near future: we’re currently negotiating with TechNet Magazine to create a puzzle for each and every issue. Nothing is set in stone just yet, but we’ll likely end up creating these puzzles for them. Nonetheless, we do expect that the negotiations will be a bit contentious; after all, they pay us absolutely nothing to write the Hey, Scripting Guy! column for the magazine, and we need an iron-clad guarantee that we’ll get the exact same deal before we agree to start doing puzzles for them as well.

Don’t worry: no one takes advantage of the Scripting Guys.