How Can I Replace Single Quote Marks in a Text File?

ScriptingGuy1

Hey, Scripting Guy! I saw your column on replacing text in a text file, and that’s almost the solution I’m looking for. However, the text I need to replace is the single quote mark, and I can’t figure out how to do that. How do I replace single quote marks in a text file?

— JB

Hey, JB. You know, this is shaping up to be another very hectic day for the Scripting Guys; after all, we have meetings in the morning as well as meetings in the afternoon. That would be bad enough, but these meetings also involve such heavy-duty issues as our budget for the coming year and the new system that we will eventually have to use in order to manage the Script Center. And yet, as busy as he is, there’s still one thing that remains first and foremost in the mind of the Scripting Guy who writes this column: what should he have for dinner tonight?

Maybe chicken casserole; the Scripting Family hasn’t had chicken casserole in awhile. And maybe “candied carrots,” you know, the ones with the butter and the brown sugar glaze on them. Or maybe ….

To be honest, JB, this could take awhile; after all, important decisions like this should never be made lightly. While you wait, feel free to read a magazine. If you don’t happen to have a magazine handy, then feel free to read the following script, one that replaces all the single quote marks in a text file. In fact, not only does it replace the single quote marks, but it replaces them with double quote marks!

OK, sure, that sounds impossible. But the proof, as they say, is in the pudding.

Hmmm, pudding might be good, too ….

Here’s the script:

Const ForReading = 1
Const ForWriting = 2

Set objFSO = CreateObject(“Scripting.FileSystemObject”)
Set objFile = objFSO.OpenTextFile(“C:\Scripts\Test.txt”, ForReading)
strText = objFile.ReadAll
objFile.Close
strOldText = Chr(39)
strNewText = Chr(34)
strNewText = Replace(strText, strOldText, strNewText)
Set objFile = objFSO.OpenTextFile(“C:\Scripts\Test.txt”, ForWriting)
objFile.WriteLine strNewText
objFile.Close

What we’re assuming here is that we have a text file that looks something like this:

‘Ken Myer’,’Finance’,’Senior Analyst’
‘Pilar Ackerman’,’Human Resources’,’Manager’
‘Jonathan Haas’,’Transportation’,’Director’

All we want to do is replace all the single quote marks with double quote marks. As JB has discovered, however, that’s not quite as easy as it sounds. Why not? We’ll explain that in a moment. Before we do that, however, let’s talk about the first part of the script.

As you can see, we kick things off by defining a pair of constants (ForReading and ForWriting); we’ll use these constants when we open our text file. (And yes, we do have to open the file twice: once to read in the existing contents, then once more to write out the new, revised contents.) After defining the constants we create an instance of the Scripting.FileSystemObject, 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)

What do we do with the file once it’s open? For starters, we use the ReadAll method to read the entire file and store the contents in a variable named strText. And then we immediately turn around and close the file.

In a typical search-and-replace script this would be the point where we would call the VBScript Replace function and replace the single quote marks with double quote marks. We’re guessing that JB has tried to do just that, using a line of code similar to this:

strNewText = Replace(strText, “‘”, “””)

Now, syntactically, that looks OK; after all, we’ve called the Replace function followed by three parameters:

Like we said, that looks OK. But here’s what happens when we try to use this code:

C:\Scripts\test.vbs(13, 40) Microsoft VBScript compilation error: Unterminated string constant

Yikes! What’s that all about?

As you might have guessed, the problem is due to the fact that both the single quote mark and the double quote mark have special meanings in VBScript. The single quote mark, to name one use, indicates a comment added to a script:

‘ This is a comment

Meanwhile, the double quote mark is used – among other things – to enclose string values:

x = “This is a string value.”

Because these characters have special meanings, all we’ve managed to do so far is to confuse VBScript. For example, a human being might look at three consecutive double quote marks and think, “Oh, this is just a double quote mark surrounded by two other double quote marks.” Unfortunately, though, VBScript doesn’t see it that way; instead, VBScript desperately tries to interpret that statement using its built-in parsing rules. One of those rules says that, for every opening double quote mark, there must be a corresponding ending quote; in other words, double quote marks must travel in pairs. When VBScript sees an odd number (three) of double quote marks it assumes that one of the pairs is missing a quote mark.

Is that a problem? You bet it is: because of the syntax error the script won’t even compile, let alone run.

This – or some variation of it – is the situation JB has run into. So how do we work around the issue? There are actually a couple different ways to do that, but we chose this approach:

strOldText = Chr(39)
strNewText = Chr(34)

What we’re doing here is using the Chr function to assign values to a couple of variables (strOldText and strNewText). If you provide Chr with an integer value (such as 39) it will report back the character that corresponds to that value. Guess what character has an ASCII value of 39? You got it: the single quote mark. As for ASCII value 34, that – well, needless to say, you’re way ahead of us: yes, Chr(34) is the double quote mark.

See what we’re doing here? In the first line we’ve assigned the single quote mark to the variable strOldText; this enables us to use a reference to the single quote mark without using the character itself. We then do the same thing with the double quote mark. Why? Because now we can call the Replace function and use variable names rather than problematic statements like “””:

strNewText = Replace(strText, strOldText, strNewText)

This statement is syntactically correct. Equally important, it will replace all the single quote marks with double quote marks.

The rest of the script practically writes itself. After reopening Test.txt (this time for writing) we call the Write method and replace the existing contents of the file with our newly-revised contents (the ones stored in the variable strNewText). We then close the text file and go back to planning tonight’s dinner. Does garlic bread sound good to anyone else?

Incidentally, when all is said and done our text file will look like this:

“Ken Myer”,”Finance”,”Senior Analyst”
“Pilar Ackerman”,”Human Resources”,”Manager”
“Jonathan Haas”,”Transportation”,”Director”

Which is exactly the way we want it to look.

And now that today’s column is done, and dinner is planned, it’s time to head off to our first meeting, the one about budgets. Last year, for the first time ever, the Scripting Guys actually got a budget; we took that money and immediately spent the whole thing on Dr. Scripto Bobblehead dolls. That should make this year’s budget meeting an interesting one, to say the least.

ScriptingGuy1

Follow