Hey, Scripting Guy! Question

Hey, Scripting Guy! How can I delete the first line and the last line in a text file?

-- TF

SpacerHey, Scripting Guy! AnswerScript Center

Hey, TF. We don’t know if anyone has ever noticed, but Hey, Scripting Guy! tends to meander from time-to-time; there are those occasions when it seemingly takes forever for us to get around to answering the question of the day. Up until now we never really thought too much about that; so what if we don’t get right to the point and take care of business? However, after what happened Sunday morning, we’re beginning to re-think that approach.

So what did happen Sunday morning? Well, Sunday morning the Scripting Guy who writes this column ran to the store to buy doughnuts. Now, the Scripting Guy who writes this column appreciates the importance of picking out the correct doughnuts; after all, that’s likely the most important decision he’ll make this week. (Maybe the only decision he’ll make this week.) However, the man who reached the doughnut case seconds before the Scripting Guy who writes this column might have taken this careful consideration to the extreme.

After getting his doughnut box and picking up one of the blue doughnut-grabbing tissues, the man stood in front of the case for what had to be several minutes, carefully pondering his choices. He grabbed a single glazed doughnut, placed it in the box, then resumed pondering. After several minutes he grabbed a maple bar, put that in his box, then pondered some more. The Scripting Guy who writes this column was too polite to do what he wanted to do, which was grab the man, shake him a couple times, and tell him, “These are doughnuts, for crying out loud! It doesn’t matter; you can’t go wrong with any kind of doughnut! Grab 12 somethings and get out of my way!”

Ay-yi-yi.

This pattern continued for hours. Even worse, at one point the man changed his mind and put the glazed doughnut back! After taking several hours to pick out 5 doughnuts he was now back to having just 4! Awhile later he decided to rearrange the doughnuts in his box, moving the maple bar from the left side to the right and putting the two jelly doughnuts next to each other.

That’s right: ay-yi-yi.

Under any other circumstances the Scripting Guy who writes this column would have gotten tired of waiting and just given up. But these are doughnuts we’re talking about! So he waited. And waited.

Note. Of course, he could have done what these two women did, which was simply reach over and under the man and grab a few doughnuts of their own, at one point almost knocking the poor guy down in their quest to get a cinnamon twist. The Scripting Guy who writes this column decided he didn’t want doughnuts that bad.

At any rate, we realized that sometimes people appreciate it more if you just get to the point. And so we vowed that, from now on, Hey, Scripting Guy! would always get right to the point. No more meandering and messing around.

Although, considering the fact that we’re now 8 or 9 paragraphs into this column and all we’ve talked about are doughnuts, well ….

But, hey, you know what they say: good things come to those who wait. Sometimes that means doughnuts, and sometimes that means scripts that can remove the first line and the last line from a text file:

Const ForReading = 1
Const ForWriting = 2

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

strText = objTextFile.ReadAll
objTextFile.Close

arrLines = Split(strText, vbCrLf)

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

For i = 1 to (Ubound(arrLines) - 1)
    objFile.WriteLine arrLines(i)
Next

objFile.Close

OK, let’s see if we can figure out how this thing works. We start out by defining a pair of constants, ForReading and ForWriting; we’ll need one of these constants each time we open the text file. And before you ask, yes, we’re going to have to open the text file twice. Because we can’t directly edit a text file using a script we need to open the file once in order to read from it. We’ll read the file, store the contents in memory, then “remove” the first and last lines from this virtual copy of the file. We’ll then open the file a second time (this time for writing) and replace the original contents with the virtual copy.

With that in mind, our next step is to create an instance of the Scripting.FileSystemObject and then open the file C:\Scripts\Test.txt for reading:

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

From there we use the ReadAll method to read the file and store the contents in a variable named strText. Once the file is read we then call the Close method and close the text file.

Let’s assume that this text file (and the value of the variable strText) looks like this:

This is line 1.
This is line 2.
This is line 3.
This is line 4.
This is line 5.

Our task now is to delete the first line and the last line. How are we going to do that? Well, for starters, we’re going to use the Split function and turn our virtual text file into an array:

arrLines = Split(strText, vbCrLf)

As you can see, we’re splitting the file on the carriage return-linefeed character (vbCrLf); that’s going to result in an array where each item in the array corresponds to a line in the text file. In turn, that makes this the first item in the array:

This is line 1.

It also makes this the last item in the array:

This is line 5.

Believe it or not, we’re now ready to write the revised contents back to the file. What’s that? You say we didn’t remove the first and last lines yet? That’s true, and, to tell you the truth, we have no intention of removing the first and last lines. (That’s why we said we would “remove” these lines.) But don’t worry; the first and last lines will not appear in the text file. Guaranteed.

Yes, it is kind of exciting, isn’t it: how will the Scripting Guys escape from this trap they seem to have set for themselves? Well, to begin with, we re-open the file C:\Scripts\Test.txt. But this time, we open the file for writing:

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

So what are we going to write to the file? We’re going to write the contents we read in from the text file – except, of course, for the first and last lines. That’s the reason we have this block of code:

For i = 1 to (Ubound(arrLines) - 1)
    objFile.WriteLine arrLines(i)
Next

What we’re doing here is setting up a For Next loop to loop through the items in the array arrLines. Wait, check that: to loop through most of the lines in the array arrLines. As you know, the first item in an array is always given the index number 0; that means that the second item in the array is given the index number 1. Well, notice where our For Next loop begins: it begins with item 1, the second item in the array. In other words, we’re skipping item 1 (and thus line 1) altogether.

You also know that the Ubound function identifies the last item in an array. OK, well, look at the endpoint of our For Next loop: Ubound(arrLines) – 1. See? The last item in our loop is the upper bound of the array (Ubound) minus 1. Or, to put it another way, the next-to-last item in the array. That means we’re skipping the very last item in the array as well. And that also means that we’re skipping the very last line in the text file.

So much for the For Next statement. Inside the loop we use the WriteLine method to write the value of each item in the array (except for the first item and the last item) back to the file C:\Scripts\Test.txt. What does that mean? That means that, when we’re all done, Test.txt is going to look like this:

This is line 2.
This is line 3.
This is line 4.

It’s our original file … minus the first and last lines. Pretty easy, wasn’t it?

As you know, we typically try to close each column with a punchline. Unfortunately, you’ve probably already guessed the punchline to today’s column. Sure enough, after selecting his doughnuts, the Scripting Guy who writes this column headed for the checkstand. Guess who was in line in front of him? And guess who couldn’t find his club card? And guess who started to hand the clerk a $10 bill and then decided he’d pay with exact change instead? And – well, you know the rest. Fortunately, the Scripting Guy who writes this column had picked up a dozen doughnuts; that ensured that he wouldn’t starve to death while he waited in line.