Hey, Scripting Guy! Question

Hey, Scripting Guy! How can I make sure that no row in a text file exceeds 261 characters?

-- RK

SpacerHey, Scripting Guy! AnswerScript Center

Hey, RK. You know, this was a good day to ask a question; for once the Scripting Guy who writes this column is in an OK mood. (No, he’s never in a good mood, at least not when he’s at work.) Yesterday the Scripting Son absolutely had to have a sub sandwich for dinner. With that in mind the Scripting Dad dutifully stopped to pick up sandwiches on his way home, and had the opportunity to witness greatness in action: the sandwiches he ordered were made – no, were crafted – by the finest sandwich maker in the history of the world.

Typically when you order sandwiches from a fast food place you walk out thinking one of two things: 1) either the sandwich makers must have to pay for each ingredient themselves (why else would they put just two measly pickle slices on a foot-long sandwich?), or 2) these fast food employees are under doctor orders to never lift more than one green pepper strip at a time. Needless to say, the Scripting Dad usually asks the sandwich makers if they could tie a string around each sandwich: they have so little substance he’s afraid that, like a balloon, they might float away if they don’t include a string he can tie around his wrist.

Note to the Sandwich Makers of the world. If a patron asks for mustard it’s a pretty safe bet that they’d like to see mustard spread along the entire sandwich. One tiny squirt of mustard at the end of one piece of bread probably isn’t what they had in mind.

Yesterday was different, though. “You want olives?” said the sandwich maker as he proceeded to grab an entire handful of olives and scatter them across the bread. “Tomatoes? No problem,” he said, laying wall-to-wall tomatoes across the depth and breadth of the sandwich. “Vinegar?” he asked, and then promptly sprayed vinegar all over his creation (unlike some of his peers, who squirt a little bit of air out of the bottle and assume, for some reason, that that was what you wanted). To be honest, it was all so beautiful that the Scripting Guy who writes this column had to grab his food and hurry out of the restaurant; he was too embarrassed to have the sandwich maker see him cry.

Not that he should have been embarrassed, mind you. After all, few things in life are as beautiful and as stirring as a well-made sandwich.

As you might expect, this sandwich maker immediately became an inspiration to the Scripting Guy who writes this column. “No more shortcuts,” vowed the Scripting Guy. “No more slapping together scripts and calling them good, no more writing about baseball or basketball or sandwiches and passing that off as a scripting column. From this moment on I solemnly vow to make every script I create and every column I write a true work of art.”

Of course, that vow only lasted until he had to sit down to create his next script and write his next column; at that point he reverted back to his usual lazy self. But at least he did manage to come up with the following script:

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
    strLine = Left(strLine, 9)
    strContents = strContents & strLine & vbCrLf
Loop

objFile.Close

Set objFile = objFSO.OpenTextFile("c:\scripts\test.txt", ForWriting)
objFile.Write strContents

objFile.Close

A couple of quick notes before we begin. For one, RK, we got the impression from your email that what you wanted to do was discard any excess characters in a line. For example, suppose a line of code has 300 characters; as near as we could tell, in a case like that you want to grab just the first 261 characters and then get rid of characters 262 through 300. Consequently, that’s exactly what this sample script does. If that’s not what you had in mind, just let us know.

Note. Well, no, we won’t readdress the question. We’re just assuming that the sandwich maker will do it for us.

Second, it’s difficult for us to display lines with 261 characters on our Web pages; in turn, that makes it difficult for us to show you before and after pictures of the text file. Therefore, we cheated a little bit and created a script that ensures that no line in the text file has more than 9 characters. But that shouldn’t be a problem; to change that just look for the number 9 in the script and replace it with any number you want, including 261.

Now, let’s get down to business. Let’s assume that you have a text file that looks like this, a text file with lines of varying lengths:

123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ
123456789ABCDEFGH
12345
12345678
123456789ABC
123456789ABCDEFGHIJKLMNOPQRS

What we need to do is ensure that none of these lines have more than 9 characters (they can have fewer than 9 characters; just not more). Here’s how we do that.

To begin with, we define a pair of constants – ForReading and ForWriting – that we’ll use when opening the text file. (As usual, we’ll need to open the file on two separate occasions: once to read what’s currently there, and once to replace the existing contents with the modified contents.) We create an instance of the Scripting.FileSystemObject, then use this line of code to open the file C:\Scripts\Test.txt for reading:

Set objFile = objFSO.OpenTextFile("c:\scripts\test.txt", ForReading)

So far, so good.

Our next chore is to examine the file line-by-line, truncating any lines that have more than 9 characters. To do that we set up a Do Until loop that runs until we reach the end of the file (that is, until the AtEndOfStream property is True). Here’s what we do inside that loop:

strLine = objFile.ReadLine
strLine = Left(strLine, 9)
strContents = strContents & strLine & vbCrLf

To begin with, we use the ReadLine method to read the first line in the file and store that value in a variable named strLine. We then use the following line of code (and VBScript’s Left function) to grab just the first 9 characters in the string and assign those characters to the variable strLine:

strLine = Left(strLine, 9)

We know what you’re thinking: what if we only have, say, 5 characters in the line? No problem; in that case VBScript will simply use whatever characters there are and leave it at that. No error will occur if you ask for the first 9 characters and the line only has 5 characters.

At this point it’s safe to assume that the first line in the text file has, at most, 9 characters. In an ideal world, we would now go ahead and replace line 1 in the text file with our modified line. Unfortunately, albeit with the possible exception of one local sub sandwich place, this is not an ideal world. As it turns out, the FileSystemObject doesn’t allow us to replace individual lines in a text file. Because of that we need to construct a new text file in memory and then, when the time comes, replace the entire contents of Test.txt with this new text file.

All of which explains the purpose of this line of code:

strContents = strContents & strLine & vbCrLf

What we’re doing here is assigning our first modified line to a variable named strContents. (Technically, strContents gets assigned the existing value of strContents plus the modified line plus a carriage return-linefeed.) Each time we run through the loop we’ll modify a different line in the text file and then append that line to strContents. When we’re done, strContents will contain the modified contents of our text file, with no individual line having more than 9 characters.

Note. You’re right: in some cases we won’t actually have to modify a line; because some lines have 9 or fewer characters they’re fine just the way they are. In those cases, then, we’ll just append the existing line to strContents. But it’s the same basic idea.

After we’ve read through the entire text file we next use the Close method to close Test.txt. We have to do that because the file is currently open for reading; before we can write to the file we have to reopen it for writing. Which is exactly what we do here:

Set objFile = objFSO.OpenTextFile("c:\scripts\test.txt", ForWriting)

With the file reopened, this time for writing, we can now use the Write method to replace the existing contents with the value of the variable strContents. After that all we have to do is close the file and we’re done:

objFile.Write strContents

objFile.Close

Assuming that everything went off without a hitch Test.txt should now look like this:

123456789
123456789
12345
12345678
123456789
123456789

Which is pretty much how we wanted it to look.

So there you have it, RK. It might not be a work of art (or maybe it is, if you’ve seen what passes for art these days) but it should do what you need it to. Besides, you know what they say: some people are really good at their jobs; the rest become Scripting Guys.