Hey, Scripting Guy! We have a custom in-house application that writes data to a log file. The problem is that the data often has a bunch of blank spaces before and after it. How can I remove all the blank spaces that come before and after my data?

— KS

Hey, KS. You might find this hard to believe, but the Scripting Guy who writes this column occasionally has problems fitting into the modern world. For example, everyone in the world has a cell phone … everyone except you-know-who. Everyone in the world takes one look at a high-definition TV set and immediately plops down $5,000 to buy one … well, almost everyone. Everyone in the world has seen at least one episode of The Sporanos, Lost, or Survivor, right? Um, next question please. You get the idea: the poor guy is so stuck in the past that he even wears his baseball hat forwards instead of backwards!

That’s a good idea: the next time a traveling freak show comes to Redmond we’ll give them a call and let them know about this guy.

Needless to say, it’s easy to make fun of this poor little guy (as well as wonder how someone who doesn’t have a GPS navigation system manages to get through the day). But now thanks to your question, KS, this out-of-place Scripting Guy suddenly has a purpose in life. As it turns out, hundreds of years ago, back when dinosaurs ruled the earth and phones all had cords, this Scripting Guy was introduced to computers the hard way: by having to learn dBase III. (Yes, we know. But people were cruel back in those days.) And, for some reason, a common problem with the dBase III applications he had to deal with was this: invariably data would end up looking like your log file (for illustration purposes, pretend the asterisk marks the end of the data):

Ken Myer          *
                    Pilar Ackerman     *
     Gail Erickson     *

The important point is that, back in the good old days, our Scripting Guy actually served a useful purpose: he knew how to write code that could remove extraneous spaces from the beginning and the end of a string. And now, for one brief moment at least, his life once more has meaning. Here’s a script that will open a text file, remove excess blank spaces from the beginning and end of each line, and then save the modified lines back to the file:

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 = Trim(strLine) & vbCrLf
    strText = strText & strLine
Loop
objFile.Close
Set objFile = objFSO.OpenTextFile(“c:\scripts\test.txt”, ForWriting)
objFile.Write strText
objFile.Close

This script starts out by defining a pair of constants: ForReading and ForWriting. Because of the quirks of the FileSystemObject (the scripting object used to work with text files) we have to open the log file twice to accomplish our task: once to read from the file and once to write the modified values back to the file. These constants simply tell the script which operation – reading or writing – we want to perform. (That’s one of the quirks we were talking about: you can open a file for reading or for writing, but you can’t do both at the same time.)

After defining the constants we create an instance of the FileSystemObject and then use the OpenTextFile method to open the file C:\Scripts\Test.txt for reading. That brings us to this block of code:

Do Until objFile.AtEndOfStream
    strLine = objFile.ReadLine
    strLine = Trim(strLine) & vbCrLf
    strText = strText & strLine
Loop

What we’re doing here is using the ReadLine method to read the file line-by-line. When we first enter the Do Until loop we read the first line in the text file and store that value in a variable named strLine. That means strLine will equal this:

Ken Myer          *

Now comes that vintage programming magic you’ve been waiting for. In the next line of code we use the Trim function to remove all the blank spaces before and after our string (we also tack on a carriage return-linefeed to make sure that this value will be written as a separate line in the text file):

strLine = Trim(strLine) & vbCrLf

As you probably already guessed, the Trim function automatically removes extraneous blank spaces before and after a string. That means that strLine is now equal to this (remember, the asterisk isn’t really there, we’re just using it to indicate the end of the data):

Ken Myer*

Cool, huh? We then add the value of strLine to a variable named strText, a variable we use to keep track of all the text file lines we trim:

strText = strText & strLine

And then we loop around and repeat the process with the next line in the text file, a cycle which continues until we’ve run out of lines to read (or, in more technical terms, until the AtEndOfStream property is true).

The rest is easy. We close the file Test.txt and then immediately reopen it, this time for writing. With the file open for writing we call the Write method to write the value of strText to the file, close the file and, not coincidentally, call it a day. If you open the file Test.txt now, you’ll see the data has been trimmed nice and neat:

Ken Myer*
Pilar Ackerman*
Gail Erickson*

We’d like to see your fancy-schmancy Blackberry device or cappuccino machine do that!

Incidentally, there are two related functions you might find useful. LTrim trims excess spaces only from the beginning (the left side) of a string. In other words, you end up with data like this:

Ken Myer          *
Pilar Ackerman     *
Gail Erickson     *

Conversely, RTrim trims excess black spaces from only the end (right side) of a string:

Ken Myer*
                    Pilar Ackerman*
     Gail Erickson*

There you have it, KS. And remember, if you ever have a question about something no modern human being is likely to know how to do (like, say, change the channel without using the remote), well, now you know where to go for help.