Hey, Scripting Guy! In yesterday’s column you showed us how to search for phone numbers in a text file; you also promised us that in today’s column you’d show us how to modify that script so that it can search through all the text files in a folder. Are you still going to do that?

— SE

Hey, SE. Yes, we’re going to – wait a second. SE? Interesting; you have the exact same initials as the Scripting Editor, the same Scripting Editor who began nagging the Scripting Guy who writes this column the very second he said he was going to do a two-part Hey, Scripting Guy! this time around. “Two parts? Why are you doing two parts?” said our SE. “You always say you’re going to do a second part or a third part of something and then you never do it. What I think you should do is blah, blah, blah, blah ….”

At any rate, SE – whoever you are – yes, we are going to show you how you can search for phone numbers in all the text files found in a specified folder. Before we do that, however, we feel we should apologize to our readers. After all, we assume that none of you were able to sleep last night, tossing and turning and fretting and fuming, thinking to yourself, “How could they do this to me? How am I supposed to sleep, heck, how am I supposed to do anything until I find out how this story ends? How can I search for phone numbers in all the text files found in a specified folder?” You guys are right: that was a terrible thing for us to do, and we apologize. Never again will we do a multi-part Hey, Scripting Guy! column.

At least not until the next time we do a multi-part Hey, Scripting Guy! column.

And yes, we know: the suspense is killing you, isn’t it? Therefore, without any further ado, let’s take a look at a script that can open up all the files in the folder C:\Scripts and then search for (and echo back) any phone numbers found in those files:

Const ForReading = 1

Set objFSO = CreateObject(“Scripting.FileSystemObject”)
strComputer = “.”
Set objWMIService = GetObject(“winmgmts:\\” & strComputer & “\root\cimv2”)
Set colFileList = objWMIService.ExecQuery _
    (“ASSOCIATORS OF {Win32_Directory.Name=’C:\Scripts’} Where ” _
        & “ResultClass = CIM_DataFile”)
For Each objFile In colFileList
    strName = objFile.FileName 
    strName = Replace(strName, “_”, ” “)
    Wscript.Echo strName
    Set objFile = objFSO.OpenTextFile(objFile.Name, ForReading)
    strSearchString = objFile.ReadAll
    objFile.Close
    Set objRegEx = CreateObject(“VBScript.RegExp”)
    objRegEx.Global = True   
    objRegEx.Pattern = “\d{3}-\d{3}-\d{4} \([a-zA-Z]*\)”
    Set colMatches = objRegEx.Execute(strSearchString)  
    If colMatches.Count > 0 Then
       For Each strMatch in colMatches   
           Wscript.Echo strMatch.Value 
       Next
    End If
    Wscript.Echo
Next

You’re right: this is a fairly long script, at least for a Hey, Scripting Guy! column. But don’t fret; remember, we went over most of this in yesterday’s column. Therefore, we’re going to skip the part about using regular expressions to search for phone numbers and focus, instead, on getting that search to take place against all the files in a folder.

Our first step in that journey (after defining the constant ForReading and after creating an instance of the Scripting.FileSystemObject) is to connect to the WMI service on the local computer. And before you ask, no, you can’t use this script as-is to search text files on a remote computer; that’s because the FileSystemObject isn’t designed for working remotely. That doesn’t mean that it’s impossible to search files on a remote machine, it just means that you’ll have to make some additional modifications before you can do so. For more information, see our column How Can I Read a Text File on a Remote Computer?

Hmmm, you know we could turn this into a three-part column – well, never mind; we don’t like the look the Scripting Editor just gave us. Forget we even mentioned that.

After we make the connection to the WMI service we then use this line of code to retrieve a collection of all the files found in the folder C:\Scripts:

Set colFileList = objWMIService.ExecQuery _
    (“ASSOCIATORS OF {Win32_Directory.Name=’C:\Scripts’} Where ” _
        & “ResultClass = CIM_DataFile”)

What’s that? You say that your text files aren’t in a folder named C:\Scripts? Uh-oh ….

Nah, just kidding: all you have to do is replace the path C:\Scripts with the path to the folder where your text files are located. For example, if your text files are in D:\Employees then you need to use this line of code:

Set colFileList = objWMIService.ExecQuery _
    (“ASSOCIATORS OF {Win32_Directory.Name=’D:\Employees’} Where ” _
        & “ResultClass = CIM_DataFile”)

Admittedly, these Associators Of queries are nasty looking, to say the least. But their bark is far worse than their bite.

Once we get our collection back we then set up a For Each loop to loop through all the items in that collection (that is, through all the files in the folder C:\Scripts). The first thing we do inside that loop is execute this block of code:

strName = objFile.FileName 
strName = Replace(strName, “_”, ” “)
Wscript.Echo strName

As we noted yesterday (typical Scripting Guys: always living in the past) we have a series of files with names like this:

Jonathan_Haas.txt
Ken_Myer.txt
Pilar_Ackerman.txt

As you can see, each file is named after the employee whose personal data is stored in that file. Does that matter? Yes, it does. Yesterday we only searched one file (Ken_Myer.txt); because that file path was hard-coded into our script it wasn’t too hard to figure out whose phone numbers we were retrieving. (No, sorry, Scripting Editor; the correct answer is this: Ken Myer’s.) Today, however, things have changed: today we’re retrieving a bunch of different phone numbers belonging to a bunch of different people. Needless to say, we need some way to keep track of which phone numbers belong to which employee.

And thanks to our file-naming convention we can do this simply by retrieving the name of the file and then making a tiny little tweak to the file name. In our first line of code we retrieve the value of the FileName property (that’s the name of the file, minus the file extension) and store it in a variable named strName:

strName = objFile.FileName

That’s going to make strName equal to this:

Jonathan_Haas

That’s OK, but we decided to go one step further and replace the underscore character with a blank space. Interestingly enough, we do that by using the VBScript Replace function, replacing the underscore (_) with a blank space (“ “):

strName = Replace(strName, “_”, ” “)

Guess what we see onscreen when we echo back the new value of strName? Um, no, sorry again, Scripting Editor; the correct answer is this:

Jonathan Haas

After echoing back the employee name we’re now ready to search the first file (Jonathan_Haas.txt) for phone numbers. From here on most of the script is exactly the same as the code we showed you yesterday. One major exception: when we call the OpenTextFile method (which we need to do in order to open and read the text file) we don’t use a hard-coded file path like C:\Scripts\Ken_Myer.txt. Instead, we pass the method the value of the Name property (which just happens to be equivalent to the file path):

Set objFile = objFSO.OpenTextFile(objFile.Name, ForReading)

From there we go ahead and read the file, store the contents in a variable named strSearchString, then use a regular expression to search those contents for phone numbers. After echoing the back any phone numbers found in the file we then use the Wscript.Echo statetment to echo a blank line to the screen. Having finished with Jonathan Haas, we then loop around and repeat the process with the next file in the collection. When we’re all done our screen should look something like this:

Jonathan Haas
425-555-4241 (office)
425-555-9977 (home)

Ken Myer
425-555-1212 (office)
425-555-5656 (cell)
425-555-3434 (home)
Pilar Ackerman
425-555-2134 (office)
425-555-6341 (cell)
425-555-8900 (home)

And there you have it. Do you want to perform some task (like search for phone numbers) against all the files in a folder? No problem; just follow this pattern: Use WMI to retrieve a collection of all those files, then use the Name property to grab the path for that file (for example, if you need to pass that path to another object, like the FileSystemObject). Readers are always asking us how to perform reading/writing tasks against all the files in a folder; well, now you know!

At any rate, that concludes our two-part episode on searching text files for phone numbers. Like we said, we’ll try to steer clear of multi-part series’ from now on. Granted, that’s going to cause some problems for the Scripting Guys; for one thing, we’re not totally sure what we’re going to do with the first 67 columns for our intended 300-part series on declaring variables. But don’t worry about it; we’ll think of something.