How Can I Insert Text Into a Specific Column of a Text File?

ScriptingGuy1

Hey, Scripting Guy! How can I insert text into a specific column of a text file? I don’t want the text to be inserted at the beginning of a line or at the end of a line, but at a specific spot in the line.

— D

Hey, D. You know, over the past two years we’ve answered a lot of questions about text files, and if there’s one thing we’ve learned in that time it’s this: we should never answer any questions about text files. Yes, we know that a lot of people are interested in text files, and we know that the scripts to read and write these files are generally short and easy to create. Unfortunately, though, those scripts are usually anything but elegant. Instead of doing something cool, like calling the InsertTextInThisSpot method, we always have to resort to crazy little workarounds. Sure, we end up with a script that works, but, like we said, that script is never very elegant. And we Scripting Guys want to be elegant!

Really? So you’re saying that the baseball hat and the Seattle Mariners 2005 Spring Training shirt we wear each day isn’t elegant? Interesting; that never occurred to us. And the old black sweatpants we wear on weekends? Well, what do you know?

OK, D, maybe we’re not quite as elegant as we thought we were. With that mind, we might as well go ahead and show you a script that can insert text into a specific spot in a line:

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
    intRight = Len(strLine) – 10
    strRight = Right(strLine, intRight)
    strLeft = Left(strLine, 10)
    strInsert = “This is inserted text”
    strText = strLeft & strInsert & strRight
    strContents = strContents & strText & vbCrLf
Loop
objFile.Close
Set objFile = objFSO.OpenTextFile(“C:\Scripts\Test.txt”, ForWriting)
objFile.WriteLine strContents
objFile.Close

Let’s start out by giving you a quick overview of what this script does. For the sake of argument, let’s assume your text file looks like this:

000000000  111111111
222222222  333333333
444444444  555555555
666666666  777777777
888888888  999999999

In other words, we have nine characters, two blank spaces, and then nine more characters. (Granted, that’s an odd-looking text file, but setting it up this way will make it easier for us determine whether or not the script worked.) What we want to do is insert some text in between the two blank spaces. Because the first blank space is character 10, we want the inserted text to start at character (or column) 11. If everything goes well that will give us a line that looks like this:

000000000 This is inserted text 111111111

Can we do that? We’re about to find out.

Our script starts out by defining a pair of constants, ForReading and ForWriting; we’ll use these constants to tell the script which mode (reading or writing) we want to use when opening the text file. This, by the way, is one of those less-than-elegant things we were talking about. For better or worse, we can’t read and write to a text file at the same time; instead we’re limited to reading from or writing to it. Therefore, we actually need to open this text file twice: once to read the existing contents and once to write the revised contents back to the file.

After defining our constants we use these two lines of code to create an instance of the Scripting.FileSystemObject and then use the OpenTextFile method to open the file C:\Scripts\Test.txt for reading:

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

Seeing as how we opened the file for reading we figured we might as well start by reading in the contents. To do that we set up a Do loop that runs until we’ve reached the end of the file (that is, until the AtEndOfStream property is True). Inside that loop we use the ReadLine method to read the first line from the file and store that value in a variable named strLine:

strLine = objFile.ReadLine

What that means, of course, is that strLine is currently equal to this:

000000000  111111111

Cute, but not exactly what we want for the revised text file. But how are we going to insert our text into the middle of that line?

Here’s how. We start out by using this line of code:

intRight = Len(strLine) – 10

Why? Well, we need to construct a new line for the text file. That new line will consist of the first 10 characters in the existing line, the new text we want to insert, and then the remaining characters from the existing line. To get at these remaining characters we need to start at the end of the string and count backwards until we get to character 10. That’s fine, except for one thing: how far back are we supposed to count?

That’s what Len(strLine) – 10 tells us. The Len function tells us the total number of characters in the variable strLine; in this case that’s equal to 20. We then subtract 10 from that amount. Why? Because we don’t want to include the first 10 characters. Seeing as how 20 minus 10 equals 10, we now know that if we start at the end of the string and count backwards 10 characters we’ll end up exactly where we need to end up.

That’s exactly what we do here:

strRight = Right(strLine, intRight)

In this line of code we use the Right function to take the last 10 characters (10 being the value stored in the variabled intRight) from the end of the string and store them in the variable strRight. In this sample script, that means strRight will equal this:

111111111

We then use the Left function to take the first 10 characters in strLine and store them in the variable strLeft. That means, for the first line in the text file, strLeft will equal this:

000000000

Note that there’s a blank space at the end of strLeft and a blank space at the beginning of strRight.

And now, at long last, we can construct our new line. To do that, we store the text to be inserted into a variable named strInsert, then simply combine the values of strLeft, strInsert, and strRight (plus a carriage return-linefeed tacked on to the end). That’s what we do here:

strInsert = “This is inserted text”
strText = strLeft & strInsert & strRight

After all that, guess what the value of strText is? Good guess:

000000000 This is inserted text 111111111

Yes, we know: that’s a lot of work just to insert some text into a single line in a text file. Sadly, we’re not quite finished, though. Because we can’t write this line directly back to the text file (remember, it’s currently open for reading only) we need to construct a new text file in memory, and then, later on, write the complete contents of that virtual text file to C:\Scripts\Test.txt. Therefore, we use this line of code to add the value of our revised text line to a variable named strContents:

strContents = strContents & strText & vbCrLf

As we loop through the text file, we’ll continue appending values (lines of text) to strContents. When we’re through, strContents will be a virtual representation of what we want Test.txt to look like.

So what does happen when we’re through with the text file? Well, first we close it, then we immediately re-open it, this time for writing:

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

Once the file is open we call the WriteLine method to write the value of strContents to the text file, and then close C:\Scripts\Test.txt for good. And what does that text file look like now? We’re glad you asked:

000000000 This is inserted text 111111111
222222222 This is inserted text 333333333
444444444 This is inserted text 555555555
666666666 This is inserted text 777777777
888888888 This is inserted text 999999999

Now that’s elegant.

And so’s the baseball hat, right?

Right?

ScriptingGuy1

Follow