How Can I Insert Commas at Specified Locations in Each Line in a Text File?

ScriptingGuy1

Hey, Scripting Guy! How can I insert commas at specified locations in each line in a text file?

— MM

Hey, MM. Seeing as how Father’s Day has just come and gone we couldn’t talk about inserting commas into text files without first relating a heartwarming Father’s Day tale of our own. On Father’s Day the Scripting Guy who writes this column went to the gym to play basketball with the Scripting Son. At one point the ball took a funny bounce off the rim and skipped out towards half court. “Hey, Dad,” said the Scripting Son. “Watch this.” Without turning around he grabbed the ball and flung it back over his head, ostensibly towards the basket.

Now, had the Scripting Son made the basket that would have been pretty impressive; nevertheless, it wouldn’t have been anywhere near as cool as what happened instead. As it turns out, the gym the duo plays at has a small bathroom at one end, and the last person to use this bathroom neglected to close the door. When the Scripting Son flung the ball over his head, the ball zoomed through the bathroom door, rattled off the sink a few times, and then landed in the toilet.

You couldn’t have asked for a better Father’s Day present. Or at least not a funnier one.

Admittedly, it’s a bit difficult, if not downright impossible, to top a basketball landing in a toilet. But if anything can top a basketball in the toilet, it would have to be a script that inserts commas at specified locations in each line in a text file:

Const ForReading = 1
Const ForWriting = 2

arrCommas = Array(2,7,11,17,18,20)
Set objFSO = CreateObject(“Scripting.FileSystemObject”)
Set objFile = objFSO.OpenTextFile(“C:\Scripts\Test.txt”, ForReading)
Do Until objFile.AtEndOfStream
    strLine = objFile.ReadLine
    intLength = Len(strLine)
    For Each strComma in arrCommas
        strLine = Left(strLine, strComma – 1) + “,” + Mid(strLine, strComma, intLength)
    Next
    strText = strText & strLine & vbCrLf
Loop
objFile.Close
Set objFile = objFSO.OpenTextFile(“C:\Scripts\Test.txt”, ForWriting)
objFile.Write strText
objFile.Close

So how does this all work? That’s a good question. Let’s see if we can figure out what’s going on here.

We actually start out simply enough, defining a pair of constants (ForReading and ForWriting) that we’ll need when it comes time to open our text file. (And yes, we need two constants because we have to open the text file twice: once to read in the existing contents and once to write the revised contents back to the file.) We then use this line of code to create an array named arrCommas:

arrCommas = Array(2,7,11,17,18,20)

Why did we assign these particular numbers to the array? That’s easy: these values represent the character positions where we want to insert our commas. For example, suppose our text file looks like this:

ABBBBCCCDDDDDEFFFFFFFFFFFFF
GHHHHIIIJJJJJKLLLLLLLLLLLLL
MNNNNOOOPPPPPQRRRRRRRRRRRRR

We want to insert commas in character positions 2, 7, 11, 17, 18, and 20. That means that, when the script finishes, the text file will look like this:

A,BBBB,CCC,DDDDD,,E,FFFFFFFFFFFFF
G,HHHH,III,JJJJJ,,K,LLLLLLLLLLLLL
M,NNNN,OOO,PPPPP,,Q,RRRRRRRRRRRRR

The only way to achieve that result is to put commas in the proper spots:

You get the idea.

The next part is also easy; we create an instance of the Scripting.FileSystemObject object 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)

Now it starts to get a little tricky.

For starters, we set up a Do Until loop that runs until the file’s AtEndOfStream property is True (which simply means that we want to keep reading until there’s nothing left to read). Inside this loop we use the ReadLine method to read the first line from the file and store it in a variable named strLine:

strLine = objFile.ReadLine

OK, so maybe that wasn’t so tricky after all. But that’s about to change. Our next step is to use the Len function to determine the number of characters in the variable strLine:

intLength = Len(strLine)

Why do we do that? Just be patient; we’ll explain that in a second.

Once we know the length of the line we then set up a For Each loop, a loop that runs through all the items in the array arrCommas:

For Each strComma in arrCommas

And what do you suppose we do inside that loop? This is what we do inside that loop:

strLine = Left(strLine, strComma – 1) + “,” + Mid(strLine, strComma, intLength)

As it turns out, this is the tricky part. What we’re doing here is inserting all the required commas into the required places. Why is that so tricky? Mainly due to the fact that there’s no command we can call that will simply insert commas in the designated spaces. Instead, we have to do this comma-by-comma, character-by-character. For example, the very first thing we need to do is insert a comma in character position 2. How are we going to do that? Well, first we need to grab all the characters up to character position 2 (in other words, we need to grab the first character). That’s what this piece of code is for:

Left(strLine, strComma – 1)

All we’re doing here is using the Left function to grab the first x number of characters from the variable strLine. What is x equal to? x is equal to the first value in the array (2) minus 1. And why minus 1? If you think about it for a second, you’ll see why we need to specify the minus 1. If we want our comma to hold down position 2 that means that we can only grab one character; if we took the first 2 characters then the comma would actually be in position 3. And who wants a comma in position 3? Maybe someone else does, but not us.

Therefore, we take the first 2 – 1 characters and stash them (or, rather it: that’s simply going to bet the letter A) in the variable strLine. We then use this bit of code to tack a comma onto the end of strLine:

+ “,”

In turn, that makes the variable strLine equal to this:

A,

That’s nice; we now have a comma in character position 2. But what about the rest of the line? Well, that’s what this piece of code is for:

+ Mid(strLine, strComma, intLength)

Here we’re using the Mid function to grab the remaining characters in the line. When you use the Mid function you need to specify three things: the string value you are working with (the variable strLine); the starting position in the string; and the ending position in the string. In this case we want to start with character position 2, the second character in the string. Fortunately, that’s easy to do, because strComma just happens to equal 2. (Remember, we’re not dealing with the revised string, we’re dealing with the actual line of text read in from the text file, a line of text that doesn’t have a comma in character position2). As for the ending position, we want to grab all the remaining characters. How many characters is that? To tell you the truth, we don’t know. But that’s fine: after all, we’ve stored that value in the variable intLength. (That’s why we needed to determine the length of the string.)

What does all that mean? That means we’re going to grab the following set of characters:

BBBBCCCDDDDDEFFFFFFFFFFFFF

And when everything gets added together, that means that strLine will be assigned the following value:

A,BBBBCCCDDDDDEFFFFFFFFFFFFF

That’s really nothing special; it’s just the first line of text, albeit with a comma inserted in character position 2. Now we’re going to loop around and repeat this process with the next item in the array arrCommas. Because that item happens to be the number 7, we’ll end up inserting a comma in character position 7, making our string equal to this:

A,BBBB,CCCDDDDDEFFFFFFFFFFFFF

And then we’ll loop around and repeat this process with all the remaining items in the array, slowly but surely inserting all the commas in their appointed palces.

After all the commas have been inserted we then execute this line of code:

strText = strText & strLine & vbCrLf

That line simply takes the variable strText and assigns it the existing value of strText plus the recently-revised line (strLine) plus a carriage return-linefeed. From there we loop around, read in the second line of text, and start inserting commas in that line, eventually appending that value to the variable strText.

Sooner or later, we’ll finish reading all the lines in the text file. At that point we close the file and then reopen it, this time for writing:

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

Once the file is open we use the Write method to overwrite the existing contents of Test.txt with the value of strText, then call the Close method and close the file once and for all:

objFile.Write strText
objFile.Close

The net result? A text file that looks like this:

A,BBBB,CCC,DDDDD,,E,FFFFFFFFFFFFF
G,HHHH,III,JJJJJ,,K,LLLLLLLLLLLLL
M,NNNN,OOO,PPPPP,,Q,RRRRRRRRRRRRR

A little tricky, perhaps, but still a task we can accomplish with just a few lines of code.

Incidentally, to the best of his recollection the Father’s Day game marked the first time the Scripting Guy who writes this column has ever seen a basketball land in a toilet. (Somewhat surprising to those who’ve heard about the weird competitions the Scripting Dad and Scripting Son have engaged in over the years.) As unusual as that might have been, however, the Scripting Guy who writes this column wasn’t really fazed by seeing a basketball land in the toilet; after all, his Microsoft career landed there a long, long time ago.

ScriptingGuy1

Follow