Hey, Scripting Guy! I have a text file where each line is a set of characters like this: 56A63, 78A41, etc. I need to put leading zeroes in front of these strings in order to make them all nine characters long: 000056A63, 000078A41, etc. Oh: and my text file has 20,000 lines that need to be modified like this. How can I do all that using a script?

— JC

Hey, JC. You know, at first we were going to ask another one of the Scripting Guys to answer this question. For one thing, this Scripting Guy happens to be a world-class expert on scripting, and does a far better job of explaining complicated scripting techniques than the rest of us could even dream of doing. More important, she’s playing in a Microsoft-sponsored softball league, and from what she’s told us her batting average is filled with zeroes; she should have more than enough to share with you. (Editor’s Note: Keep in mind these biting comments came from the Scripting Guy who insists he’s too good to play with the rest of us. Funny how none of us has ever actually seen him play though.)

But then we thought, “Gosh, that would hardly be like Hey, Scripting Guy! to make fun of a co-worker.” And so we decided to throw together this script instead:

Const ForReading = 1
Const ForWriting = 2

Set objFSO = CreateObject(“Scripting.FileSystemObject”)
Set objFile = objFSO.OpenTextFile(“C:\Scripts\Test.txt”, ForReading)
Do Until objFile.AtEndOfStream
    strText = objFile.ReadLine
    intLength = Len(strText)
    intZeroes = 9 – intLength
    For i = 1 to intZeroes
        strText = “0” & strText
    Next
    strContents = strContents & strText & vbCrlf
Loop
objFile.Close
Set objFile = objFSO.OpenTextFile(“C:\Scripts\Test.txt”, ForWriting)
objFile.WriteLine strContents
objFile.Close

Incidentally, we actually tested this on a text file with 20,000 lines in it. Not only did it work, but it worked pretty dang fast: in less than 30 seconds we were done. Which is just about the length of time it takes our fellow Scripting Guy to walk to the plate and then head back to the dugout, another at-bat over and done with.

Not that we would mention anything like that, mind you.

But what about the script? Well, we start out by defining a pair of constants, ForReading and ForWriting; we’ll use these constants to indicate how we want to open the text file in question. As you know, we can’t simultaneously read to and write from a text file; instead, we have to open the text file, read in the contents, modify those contents (by adding the leading zeroes), close the file, re-open the file, and then replace the existing lines with the modified lines. It’s a tad bit cumbersome, but we don’t have a lot of choice in the matter.

After defining out constants we create an instance of the Scripting.FileSystemObject, the scripting object used for working with text files. We then call the OpenTextFile method, passing two parameters: the path to the file we want to open (C:\Scripts\Test.txt) and the constant ForReading. (Why ForReading? Because, as we noted a moment ago, the first step in this process is to read in the contents of the file.)

With the file open we’re ready to roll up our sleeves and get to work. What we’re going to do next is read the file line-by-line, adding the leading zeroes as we go. To do that we set up a Do Until loop that runs from the beginning of the file to the end of the file. (How do we know that we’ve reached the end of the file? We’ve reached the end when the AtEndOfStream property is True.) Inside that loop we use this line of code to read the first line of the file and store the contents in a variable named strText:

strText = objFile.ReadLine

After we read in the line we’re ready to add the leading zeroes. However, there’s one complication here: as JC noted in her email, the number of characters in each line varies. That is, we might have 123 on one line, then have 12345 on another line. That means we can’t simply add the same number of leading zeroes to each line and call it good. (Remember, the goal is to end up with a string nine characters long.)

But that’s a minor complication. To begin with, we use the Len function to determine the number of characters in strText. For example, suppose the first line in the text file is 1234; in that case, Len will return a 4, because strText contains 4 characters. That’s what this line of code does:

intLength = Len(strText)

Next we subtract the variable intLength (the length of the string) from 9. Why? Well, that tells us how many leading zeroes we need to add. With a 4-character string we subtract 4 from 9, leaving 5. That means we need to add 5 leading zeroes. Here’s the line of code that carries out that calculation, with the required number of zeroes getting stored in a variable named intZeroes:

intZeroes = 9 – intLength

As far as adding the leading zeroes, we decided the easiest way to do that was to use a For Next loop that runs from 1 to the number of zeroes we need to add:

For i = 1 to intZeroes
    strText = “0” & strText
Next

As you can see, each time through the loop we assign a new value to the variable strText: that new value is simply a 0 (as a character, not as a number) followed by the existing value of strText. The first time through the loop we’ll have 0 followed by 1234, making strText equal to 01234. The second time through the loop we’ll have 0 followed by 01234, yielding 001234. This continues until we have 9 characters in the string.

The net effect: we’ve now taken the first line of the text file and added the necessary leading zeroes. Ideally, at this point we’d simply write the revised line back to the file. As we pointed out earlier, though, the FileSystemObject doesn’t let us read and write to a file at the same time. Therefore we need to temporarily store all the revised lines in memory, something we do here:

strContents = strContents & strText & vbCrlf

All we’re doing here is basically building our revised text file in memory. After the new file has been constructed we can then go ahead and write it to the actual file C:\Scripts\Test.txt.

Next we loop around and repeat the process with the second line in the text file, eventually adding the modified second line (plus a carriage-return linefeed, represented by the VBScript constant VbCrLf) to the variable strContents. That means that, after we’ve read through the entire file, strContents will contain 20,000 modified lines of text.

All we need to do at that point is replace the existing contents of the text file with the 20,000 modified lines stored in strContents. To do that we close the file, then immediately re-open it, this time for writing:

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

We then call the WriteLine method to write the revised contents to the text file (overwriting the existing contents), a process that looks like this:

objFile.WriteLine strContents

And that’s it. We close the text file, and we’re free to move on to something else. Something like, say, batting practice.

Well, for those of us who need it, that is ….