Hey, Scripting Guy! Question

Hey, Scripting Guy! I have a file that has IP addresses and server names on each line. How can I delete the IP addresses, leaving just the server names?

-- RR

SpacerHey, Scripting Guy! AnswerScript Center

Hey, RR. In case you’re wondering, as we’re writing today’s column temperatures are still right around freezing and there’s still snow and ice everywhere you look. Admittedly, many of you aren’t feeling particularly sorry for the Scripting Guys. “It’s 33 degrees and you’re saying it’s too cold?!? Holy smokes, here in [insert place name] it’s 3 degrees and you don’t hear anyone whining and crying about the weather! Is everyone in Seattle a wimp?”

Well, here’s what we have to say about that: yes, yes we are. Each and every one of us.

In fact, not only are we all wimps, but some of us are downright weird as well. For example, the other morning the Scripting Guy who writes this column arrived at the bus stop to find several people dutifully waiting for the good old 245. One guy waiting was wearing a heavy sweatshirt with the hood pulled up over his stocking cap. He was also wearing tennis shoes with no socks and a pair of running shorts. As he stood there, holding a pair of gloves in his hands (no, not on his hands, in his hands) he said to no one in particular, “Sure is cold this morning, isn’t it?”

Yeah, sure is, isn’t it?

So how do weather wimps like us get through these gloomy winter days? Well we can’t speak for any of our fellow Seattleites, but the Scripting Guys spend their time writing scripts that can delete just the IP addresses from a file, leaving behind nothing but server names:

Const ForReading = 1
Const ForWriting = 2

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

Do Until objFile.AtEndOfStream
    strLine = objFile.ReadLine
    arrItems = Split(strLine, ", ")
    strNewContent = strNewContent & arrItems(1) & vbCrLf
Loop

objFile.Close

Set objFile = objFSO.OpenTextFile("C:\Scripts\Test.txt", ForWriting)
objFile.Write strNewContent
objFile.Close

Ah, yes: there’s nothing like a script to make you feel all warm and toasty, is there? We’ll explain how this script works in just a second. Before we do that, however, we should note that we’re assuming that the text file in question looks something like this:

192.168.1.1, atl-dc-01
192.168.1.2, atl-dc-02
192.168.1.3, atl-dc-03
192.168.1.4, atl-dc-04

What we want it to look like is this:

atl-dc-01
atl-dc-02
atl-dc-03
atl-dc-04

How are we going to do that? Here’s how.

To begin with, we define a pair of constants, ForReading (with a value of 1) and ForWriting (with a value of 2); we’ll use these constants when we open our text file. And yes, we need two constants because we’ll actually have to open the text file twice: once to read in the existing contents, and once to save the revised contents. That’s just the way you have to do things when working with text files.

After defining the constants we then use these two lines of code to create an instance of the Scripting.FileSystemObject and to open the file C:\Scripts\Test.txt for reading:

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

And now the fun begins.

For starters, we create a Do Until loop that runs until we’ve reached the end of the text file (or, as we Seattleites like to say, until the file’s AtEndOfStream property is True). Inside the loop we use the ReadLine method to read the first line of the text file and store it in a variable named strLine. That means that, the first time through the loop, strLine will be equal to this:

192.168.1.1, atl-dc-01

That’s all well and good, of course, except for one thing: that’s not what we want strLine to be equal to. Instead, we want it to be equal to this:

atl-dc-01

Consequently, in our next line of code we use the Split method to create an array (named arrItems), designating a comma followed by a blank space (, ) as the delimiter we want to split on. What will that do for us? That will give us an array consisting of these two items:

192.168.1.1

atl-dc-01

As you can see, the second item (which has an index number of 1; remember, the first item in an array always has an index number of 0) just happens to be the name of the computer, the value we want saved in our text file. Because of that we use this line of code to grab the value of item 1 and store it in a variable named strNewContent:

strNewContent = strNewContent & arrItems(1) & vbCrLf

OK, you’re right: we’re actually storing more than just the value of that array item in strNewContent. Instead, we’re assigning strNewContent the existing value of strNewContent plus array item 1 plus a carriage return-linefeed (vbCrLf). Of course, the first time through the loop strNewContent won’t have a value; therefore, strNewContent will end up being equal to this:

atl-dc-01

We then loop around and repeat the process with the second line in the text file. That results in arrItems(1) being equal to atl-dc-02 and, when all is said and done, makes strNewContent equal to this:

atl-dc-01
atl-dc-02

And then we simply loop around and start all over again, repeating the process until we’ve read and modified each and every line in the text file. When we’re done strNewContent will be equal to this:

atl-dc-01
atl-dc-02
atl-dc-03
atl-dc-04

Which, remarkably enough, is exactly what we want our text file to look like.

The rest is easy. We close the file Test.txt and then immediately reopen it, this time for writing (using the constant ForWriting). We use the WriteLine method to write the value of strNewContent to the file, then close the file for the last time. At that point we can go back to whining and complaining about the weather.

Incidentally, as an alumnus of the University of Washington the Scripting Guy who writes this column feels he should add one final note. The crazy guy at the bus stop, the one who was wearing running shorts and no socks and yet was complaining about it being cold? His sweatshirt was emblazoned Washington State University. No further comment needed, if you know what we mean.