How Can I Locate a Value in a Text File and Then Parse the Line of Text Where the Value Was Found?

ScriptingGuy1

Hey, Scripting Guy! How can I open a CSV file, locate a value in column 1, then store the value in column 2 in a variable?

— THvdH

Hey, HvdH. We have to be honest with you, it’s a little difficult for the Scripting Guy who writes this column to stay focused on his work today. After all, tomorrow is one of the biggest and best of all holidays in the US: the annual Terducken Bowl.

Thanksgiving? Sure, tomorrow is also Thanksgiving in the US … we think. To tell you the truth, we feel a little sorry for Thanksgiving: it’s a shame it has to fall on the same day as the Terducken Bowl. But those are the breaks, right?

On the off-chance that there’s someone out there who isn’t familiar with the Terducken Bowl, the Bowl is an annual touch football game played between two teams: the Scripting Guy who writes this column and his Scripting Nephew (Jordan) versus the Scripting Son and yet another Scripting Nephew (Blake). To say that this year’s game is the most important event in the history of the universe would be an understatement. The Scripting Guy who writes this column (and his faithful sidekick, Jordan) won the first two Terducken Bowls; that was mainly because, way back then, the Scripting Guy who writes this column was bigger and faster than the Scripting Kids. In year 3, however, the Scripting Guy who writes this column somehow became the smallest and the slowest of all the players on the field. As a result, even though each game in years 3-through-5 were hard-fought, Dylan the Scripting Son and his faithful sidekick won the all 3 games (7 points per touchdown, first one to 100 wins).

Ah, but then came the 2005 Terducken Bowl. In that game, the Scripting Guy and Jordan stunned the crowd (and, interestingly enough, there actually was a crowd, consisting of neighborhood kids who came out to watch) by grabbing a 21-0 lead and then coasting in from there. That tied the series at 3 wins apiece, making this year’s game all the more important.

At any rate, the Scripting Guy who writes this column is a bit preoccupied today, what with having to plan his strategy for tomorrow’s game. (of course, most years that strategy consists solely of him saying, “OK, Jordan, run out as far as you can and I’ll try to hit you with pass.”) However, in the spirit of the holidays, the Scripting Guy who writes this column has agreed to to take a brief timeout and show you how to write a script that can open a CSV file, locate a value in column 1, then store the value in column 2 in a variable.

You know, a script like this one:

Const ForReading = 1
Set objFSo = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("C:\Scripts\Test.txt")
Do Until objFile.AtEndOfStream
    strText = objFile.ReadLine
    If InStr(strText, "HE123-ASD-FRE-R4") Then
        arrText = Split(strText, ",")
        strVariable = arrText(1)
        Exit Do
    End If
Loop
objFile.Close
Wscript.Echo strVariable

Based on your email, HvdH, it sounds like your text file looks something like this, with a bunch of computer names followed by the IP address of that computer:

HE123-ASD-FRE-R01,192.168.1.5
HE123-ASD-FRE-R02,192.168.1.6
HE123-ASD-FRE-R03,192.168.1.33
HE123-ASD-FRE-R04,192.168.1.9
HE123-ASD-FRE-R05,192.168.1.44

Our job – or at least the job of our script – is to locate a specific computer (in this case, HE123-ASD-FRE-R04) and return the IP address: 192.168.1.9. Let’s see if our proposed solution will actually do that.

As you can see, we begin by defining a constant named ForReading and setting the value to 1; we’ll use this constant when we open our text file. After defining the constant we create an instance of the Scripting.FileSystemObject, then use the OpenTextFile method to open the file C:\Scripts\Test.txt. That’s what we do here:

Set objFSo = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("C:\Scripts\Test.txt")

Our next step is to create a Do Until loop that will read through the file, line-by-line, until each and every line has been read. (How do we know when each and every line has been read? When the AtEndOfStream property is True.) Admittedly, checking the file line-by-line is sort of a brute force method; there are probably more elegant ways to approach this problem. So then why did we settle for the brute force method? Two reasons. First, we thought this approach was far easier than, say, trying to write a regular expression to ferret out the target value. Second, when it comes to reading text files the FileSystemObject is pretty dang fast: even if your file has thousands of computer names the FileSystemObject can process that file in a matter of seconds. Because of that there’s no appreciable performance penalty in taking the brute force approach over some other method. Given the choice between quick and easy and slightly-quicker but also-more-complicated, well, we’ll take quick and easy every time.

Inside the Do loop we use the ReadLine method to read the first line from the text file and store that value in a variable named strText. We then use the InStr function and the following line of code to determine whether or not the name of our target computer (HE123-ASD-FRE-R4) can be found in that line:

If InStr(strText, "HE123-ASD-FRE-R4") Then

If InStr returns a 0 that means the target computer name doesn’t appear anywhere in the line of text. Therefore, we simply loop around and repeat the process using the next line from the text file.

Ah, but suppose InStr returns something other than 0. (Technically, InStr returns the starting position of the target string if that string can be found). In that case, we execute the following block of code:

arrText = Split(strText, ",")
strVariable = arrText(1)

In the first line we use the Split function to split the line of text into an array named arrText. Because we’re using the comma as the delimiter that means we end up with an array consisting of these two items:

The item we’re interested in, of course, is item 2, the IP address. As you know, in VBScript each item in an array is automatically assigned an index number: the first item is given the index number 0, the second item is given the index number 1, and so on. Because we’re interested in the second item (which has an index number of 1) we can use this line of code to grab the value of that item and store it in a variable named strVariable:

strVariable = arrText(1)

At this point we’re pretty much done: we’ve located the target computer, grabbed the IP address, and stored that address in a variable named strVariable. Among other things, that means that we don’t need to read through the rest of file; we’ve already found what we were looking for. Therefore, we call the Exit Do statement to break out of the loop. Once we’re out of the loop we use the Close method to close the file, then echo back the value of strVariable:

192.168.1.9

And now we truly are done. And ready to get back to the real task at hand: tomorrow’s Terducken Bowl.

Incidentally, this is the last Hey, Scripting Guy! column until Monday, November 27^th; that’s because we Microsoft employees get the next four days off in honor of the Terducken Bowl. And yes, that also means it will be awhile before the results of this year’s game are publicly available. In fact, depending on who wins, it might be a long while before the results of this year’s game are publicly available.

ScriptingGuy1

Follow