Hey, Scripting Guy! How Can I Search a Text File for Terms Read In From a Second Text File?

ScriptingGuy1

Hey, Scripting Guy! I have a text file that has a list of search terms in it. I would like to write a script that can search for each of those terms in a second file, and then report back any of the terms that couldn’t be found in that second file. How do I do that?

— PH

Hey, PH. You know, this is a good day for the Scripting Guy who writes this column. That’s somewhat surprising, too, because – until he figured out what was going on – it came perilously close to being a very bad day.

Last night on his way home from work the Scripting Guy who writes this column stopped to buy two extra-large sandwiches, one for himself and one for the Scripting Son. As he paid for the two sandwiches the cashier asked him, “Is this for here or to go?”

For here? The Scripting Guy who writes this column had just ordered two gigantic sandwiches, and he was the only customer in the restaurant at the time. For a moment, this Scripting Guy was crushed: obviously the cashier saw him as the kind of person who could wolf down two huge sandwiches all by himself, and without batting an eye. Say it isn’t so: gluttony is one of the Seven Deadly Sins!

The Scripting Guy who writes this column was just about to storm out of the restaurant, never to return (well, at least not until he wanted another sandwich) when he realized what was going on. The cashier didn’t think he was a gluttonous slob; far from it. Instead, she was worried that this poor little Scripting Guy – with his tiny little stomach and his birdlike appetite – was malnourished. She wanted him to eat both those sandwiches, because she figured a skinny guy like him could actually stand to gain a few pounds. Once he realized this, the Scripting Guy who writes this column simply smiled, left the girl a tip, and headed out the door.

But don’t worry about the Scripting Guy who writes this column; he might be skinny, but he’s not malnourished. After all, if he was malnourished he’d never be able to muster the strength to write a script that grabs terms from one text file and then searches for each of those terms in a second text file:

Const ForReading = 1

Set objFSO = CreateObject(“Scripting.FileSystemObject”)
Set objFile = objFSO.OpenTextFile(“C:\Scripts\SearchText.txt”, ForReading)
strContents = objFile.ReadAll
objFile.Close
Set objFile = objFSO.OpenTextFile(“C:\Scripts\Terms.txt”, ForReading)
strSearchTerms = objFile.ReadAll
objFile.Close
arrSearchTerms = Split(strSearchTerms, vbCrLf)
For Each strItem in arrSearchTerms
    intFound = InStr(strContents, strItem)
    If intFound = 0 Then
        Wscript.Echo “The search term ” & strItem & ” was not found.”
    End If
Next

Let’s see if we can figure out how this works. To begin with, we define a constant named ForReading and assign it the value 1; we’ll use this constant each time we open a text file. (And yes, we’ll be working with two text files, but not because we’re a glutton: PH asked us to work with two text files.) After defining the constant we create an instance of the Scripting.FileSystemObject, then use the OpenTextFile method to open the file C:\Scripts\SearchText.txt for reading (this, by the way, happens to be the file we want to search):

Set objFile = objFSO.OpenTextFile(“C:\Scripts\SearchText.txt”, ForReading)

After that we use the ReadAll method to read in the entire contents of the text file, storing that information in a variable named strContents. Of course, this also gives us a virtual copy of the file safely tucked away in memory. And that’s good. Why? Because we’re going to search this virtual copy as opposed to searching the actual text file itself. (Which is pretty much the way we have to do this.) With that in mind, we then call the Close method and close the file SearchText.txt.

Got all that? Good. In case you’re wondering, the contents of SearchText.txt (and thus the value of the variable strContents) look like this:

‘Curiouser and curiouser!’ cried Alice (she was so much surprised, that 
for the moment she quite forgot how to speak good English); ‘now I’m 
opening out like the largest telescope that ever was! Good-bye, feet!’ 
(for when she looked down at her feet, they seemed to be almost out of 
sight, they were getting so far off). ‘Oh, my poor little feet, I wonder 
who will put on your shoes and stockings for you now, dears? I’m sure 
_I_ shan’t be able! I shall be a great deal too far off to trouble myself 
about you: you must manage the best way you can; –but I must be kind to 
them,’ thought Alice, ‘or perhaps they won’t walk the way I want to go!  
Let me see: I’ll give them a new pair of boots every Christmas.’

Our next step is to call OpenTextFile one last time, on this occasion opening the file C:\Scripts\Terms.txt (which contains the items we want to search for). And then once again we call the ReadAll method, this time storing the extracted data in a variable named strSearchTerms:

strSearchTerms = objFile.ReadAll

And then we call the Close method and dismiss the file Terms.txt.

That simply means that, at this point in time, the value of strSearchTerms looks like this (reflecting the data read in from the text file):

boots
hat
pants
shirt
shoes
telescope

Is that good? Well, no, not really; after all, we need to search for each item separately. (That is, we need to search for the word boots, then do a second search for the word hat, then do a third search for the word pants, and so on.) In turn, that means that we somehow need to take this single list and divvy it up into individual search times.

Which is exactly what this line of code is for:

arrSearchTerms = Split(strSearchTerms, vbCrLf)

Here we’re simply using the Split function to convert the string value strSearchTerms into an array named arrSearchTerms. By splitting on the carriage return-linefeed character (vbCrLf) we end up with an array consisting of the following items:

If you’re thinking, “Gee, each of those items looks an awful lot like one of the terms we want to search for,” well, you’re absolutely right: each item in the array is one of the terms we want to search for. Imagine that!

Of course, it wouldn’t be much fun to have a set of search terms and then not actually search for them, would it? That’s what this block of code is for:

For Each strItem in arrSearchTerms
    intFound = InStr(strContents, strItem)
    If intFound = 0 Then
        Wscript.Echo “The search term ” & strItem & ” was not found.”
    End If
Next

What we’ve done here is set up a For Each loop to walk us through each item in the array arrSearchTerms. For each search term we use the InStr function to determine whether or not that value can be found anywhere in our text file (or, more precisely, anywhere in the value of the variable strContents). That’s what this line of code is for:

intFound = InStr(strContents, strItem)

If the search term is found, InStr returns an integer corresponding to the character position in the string where the term begins. If the search term can’t be found, then InStr returns a 0. How can we report back which terms can’t be found in the file? That’s easy; we simply check to see if the value of the variable intFound (the starting character position) is equal to 0:

If intFound = 0 Then

If intFound is anything but 0 that can only mean one thing: the search term appears somewhere in strContents. Thus if we get back a value other than 0 we simply go back to the top of the loop and repeat the process with the next term. If intFound is equal to 0, that can also mean only one thing: the search term doesn’t appear anywhere in strContents. In that case, we echo back a message stating that the term in question could not be found:

Wscript.Echo “The search term ” & strItem & ” was not found.”

Using our sample text file and our sample search terms, that means we’ll get back a report that looks like this:

The search term pants was not found.
The search term shirt was not found.

As soon as we’ve finished looping through all the items in the array we can then call it a day. And what the heck, you can call it a day, too. Take the rest of the day off, and if your boss decides to dock you a day’s pay, well, just send the bill to the Scripting Guys and we’ll take care of it.

As a postscript, when the Scripting Guy who writes this column got home and took the sandwiches out of the bag, he discovered that the cashier had helpfully included about 253,000 napkins. Apparently she must have thought that the Scripting Son was a messy eater.

ScriptingGuy1

Follow