Hey, Scripting Guy! Question

Hey, Scripting Guy! I need a script that can read a file and, based on the contents, insert some additional lines of text into that file. Can you help me with that?
-- JS

SpacerHey, Scripting Guy! AnswerScript Center

Hey, JS. We’re sure that most of you have heard by now that Microsoft has made an offer to buy Yahoo!, an offer currently valued at around $42 billion. (Because the offer is based, in part, on stock prices the value of the deal tends to fluctuate on a daily basis.) So far, however, Yahoo! has held out for an even better deal, noting in a letter to Microsoft CEO Steve Ballmer, “We are steadfast in our commitment to choosing a path that maximizes stockholder value and we will not allow you or anyone else to acquire the company for anything less than its full value.”

Because, obviously, a company that doesn’t actually make anything (including money) is worth way more than $42 billion.

Editor’s Note: Yes, it appears the Scripting Guy who writes this column is trying to get himself (and the Scripting Editor) into trouble once again. Last time the Scripting Guy who writes this column even mentioned another popular Web site that article was removed immediately. Once people see what’s in here, well … read quickly and assume you’re one of the few who will actually see what’s in here.

Microsoft has yet to respond to this latest salvo from Yahoo!, which can mean only one thing: it’s time for the Scripting Guys to step in. Obviously money is not important to the folks at Yahoo!; if it was, they would take the $42 billion and run. Instead, they must be holding out for something better and more meaningful than money. Which is why the Scripting Guys have decided to enter the negotiations: if Yahoo! will give us the entire company, in turn, we will give every Yahoo! shareholder their very own Dr. Scripto bobblehead doll. That’s something that even $42 billion can’t buy.

Note. Well, OK, if you want to get technical, $42 billion would actually buy you over 4 billion Dr. Scripto bobblehead dolls. But let’s not tell anyone at Yahoo! that, OK?

At any rate, if anyone from the Yahoo! board of directors is reading this, drop us a line and let us know what you think. We should note, however, that this offer is non-negotiable. That’s not because we aren’t willing to negotiate, it’s just because we don’t have anything other than a few bobbleheads we can negotiate with. The Scripting Editor says she’s willing to toss her Lexus into the deal, but it would be up to you guys to figure out who got to drive it, and when.

Note. OK, so the Scripting Editor didn’t actually say she would toss her Lexus into the deal, in part because we didn’t actually ask her. But the Scripting Editor’s a team player, and we don’t expect to have any problems with her.

Well, not unless she finds out about this.

Editor’s Note: The Scripting Editor is going to need her Lexus so she has a place to live after this article is discovered.

Of course, many of you are probably concerned about this impending deal. You’re probably thinking, “Gee, if the Scripting Guys take over Yahoo! they’ll be rich and powerful. When that happens they aren’t going to want to spend their time writing about system administration scripting.” Well you know what? You’re absolutely right about that; we’ll be on the beach in Tahiti before the ink dries on the contract. But seeing as how we don’t actually own Yahoo! (at least not yet) we’ll go ahead and answer JS’ question:

Const ForReading = 1
Const ForWriting = 2

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

Do Until objFile.AtEndOfStream
    strLine1 = objFile.ReadLine
    If InStr(strLine1, "Medical") Then
        strDuplicateLine = strLine1
        strDuplicateLine = Replace(strDuplicateLine, "Medical", "Dental")
   End If
    strLine2 = objFile.ReadLine
    strLine3 = objFile.ReadLine
    strText = strText & strLine1 & vbCrLf & strLine2 & vbCrLf & strLine3 & vbCrLf
    
    If strDuplicateLine <> "" Then
        strText = strText & strDuplicateLine & vbCrLf & strLine2 & vbCrLf & strLine3 & vbCrLf
    End If

    strDuplicateLine = ""
Loop

objFile.Close

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

Before we launch into a discussion of how this script works, let’s take a peek at the text we’re working with. JS has a text file that looks a little something like this:

Ken Myer, Medical, 1/1/1980
date, 1/1/2000
reference, 12345
Pilar Ackerman, None, 1/1/1980
date, 1/1/2000
reference, 12346

As you can see, every three lines in the file represent a record for a particular person: line 1 (which we’ll get back to in a second) includes the person’s name; line 2 includes a date; and line 3 includes a reference number. If line 1 also includes the word Medical we want to duplicate this record, but with one exception: in the duplicate record we want to substitute the word Dental for the word Medical. In other words, if our script does what it’s supposed to do then Ken Myer should end up with the following two records:

Ken Myer, Medical, 1/1/1980
date, 1/1/2000
reference, 12345
Ken Myer, Dental, 1/1/1980
date, 1/1/2000
reference, 12345

See how that works? And what happens to Pilar Ackerman? Nothing. Because her record does not contain the word Medical her information remains exactly as-is:

Pilar Ackerman, None, 1/1/1980
date, 1/1/2000
reference, 12346

Got all that? Good. Now let’s see if we can figure out how to get our script to accomplish this task.

To begin with, we define a pair of constants – ForReading and ForWriting – that we’ll need when we open the text file. Speaking of which, that’s the very next thing we do: we use these two lines of code to create an instance of the Scripting.FileSystemObject and open the file C:\Scripts\Test.txt for reading:

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

Once the text file is open we need to start examining the contents of that file line-by-line. To that end, we set up a Do Until loop designed to run until we’ve read every line in the file (or, to be a bit more technical, to run until the file’s AtEndOfStream property is True):

Do Until objFile.AtEndOfStream

Inside the loop, the first thing we do is use the ReadLine method to read the first line in the file and store it in a variable named strLine1:

strLine1 = objFile.ReadLine

From there we use the InStr function to determine whether or not the word Medical appears anywhere in the value of strLine1:

If InStr(strLine1, "Medical") Then

Now, what happens if the line doesn’t contain the word Medical? To be honest, nothing happens; because we didn’t find the word Medical there’s nothing special we need to do with this record. But it’s a different story if we do find the word Medical. In that case, we’re going to run these two lines of code:

strDuplicateLine = strLine1
        strDuplicateLine = Replace(strDuplicateLine, "Medical", "Dental")

What are we doing here? Well, as you can see, we’re really not doing all that much. In line 1 we assign the value of strLine1 to a variable named strDuplicateLine; in effect, we’re making a virtual copy of the first line in the text file. In line 2 we take the variable strDuplicateLine and replace the word Medical with the word Dental. Once we’ve done that the variable strLine will be equal to this:

Ken Myer, Medical, 1/1/1980

Meanwhile, the variable strDuplicateLine will be equal to this:

Ken Myer, Dental, 1/1/1980

And yes, that is good: we now have the first line for Ken Myer’s Medical record and the first line for his Dental record.

That brings us to these two lines of code:

strLine2 = objFile.ReadLine
strLine3 = objFile.ReadLine

Both of these lines of code should be self-explanatory: in line 1 we use the ReadLine method to read the next line in the text file, and in line 2 we use the same method to read the line after that (the third line in the text file). The net result? The variable strLine2 will be equal to this:

date, 1/1/2000

And the variable strLine3 will be equal to – well, that’s right, line 3 in the text file:

reference, 12345

Note. So did you really know that, or was that just a lucky guess?

We now have all the information we need to construct both records for Ken Myer. (Or to simply reconstruct his one record, if it turns out that we didn’t find the word Medical.) We can put together record 1 by using the following line of code:

strText = strText & strLine1 & vbCrLf & strLine2 & vbCrLf & strLine3 & vbCrLf

Here we’re assigning a new value to the variable strText. That value will consist of the existing value of strText plus the following items:

The value of strLine1 (Ken Myer, Medical, 1/1/1980), and a carriage return-linefeed (the VBScript constant vbCrLf).

The value of strLine2 (date, 1/1/2000) plus another carriage return-linefeed.

The value of strLine3 (reference, 12345) plus one last carriage return-linefeed.

In other words, strText will be equal to this:

Ken Myer, Medical, 1/1/1980
date, 1/1/2000
reference, 12345

Next we check to see if the variable strDuplicateLine has a value (that is, the value is not equal to an empty string):

If strDuplicateLine <> "" Then

If strDuplicateLine has a value that can mean only one thing: we found the word Medical in line 1 of this record, so we need to tack on the Dental record as well. That’s what this line of code is for:

strText = strText & strDuplicateLine & vbCrLf & strLine2 & vbCrLf & strLine3 & vbCrLf

See? In this case we use the variable strDuplicateLine, the variable in which we replaced the word Medical with the word Dental. That makes strText equal to this:

Ken Myer, Medical, 1/1/1980
date, 1/1/2000
reference, 12345
Ken Myer, Dental, 1/1/1980
date, 1/1/2000
reference, 12345

Well, what do you know? Ken Myer now has two records – one Medical and one Dental – just like we wanted him to have.

Once that’s done we reset the value of strDuplicateLine to an empty string, then zip back to the top of the loop to repeat the process with the next line in the text file. (Which, if you recall, will actually be line 4 in the text file; that’s because we’ve already read lines 1, 2, and 3.) As soon as we finish reading the entire file we use the Close method to close Test.txt, then use this line of code to reopen the file, this time for writing:

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

From there we use the Write method to replace the existing contents of the file with the value of the variable strText, close the file one last time, and then call it a day.

As for the Yahoo! deal, we have to admit that we’re beginning to have second thoughts. As it turns out, we underestimated the number of people who own Yahoo! stock; Yahoo! actually has hundreds of thousands of shareholders rather than the 5 or 6 we assumed they had. What does that mean? That means that we don’t actually have enough bobbleheads to go around. If you guys are willing to share, well, that would be great. Otherwise, what about this: what do you think about Yahoo! buying the Script Center? After all, what a great way to get back at Microsoft for making such a paltry offer for your company, eh? And just think about that for a moment: stealing Microsoft’s crown jewel – the Script Center – away from them? Priceless.

Well, not actually priceless, mind you; however, the Scripting Guys are willing to accept $42 billion for the Script Center. (Sure, the Script Center is probably worth more, but we’re not greedy.) In fact, we’ll even sweeten the deal a little: act before the end of the month, and we’ll give you a 5% discount on top of everything else. Just let us know.