Hey, Scripting Guy! Question

Hey, Scripting Guy! I read one of your columns on replacing text in a file and I found it very useful. However, I have a somewhat more-complicated need: I want to replace text that includes both double quote marks and a tab character. How can I do that?

-- FC

SpacerHey, Scripting Guy! AnswerScript Center

Hey, FC. Your know, as anyone who’s ever read Hey, Scripting Guy! could tell you, the Scripting Guy who writes this column tends to have a number of opinions that don’t seem to be shared by anyone else in the world.

Take grocery store discount cards, for example. In the US, most grocery store chains now have some sort of discount card: after they ring up your purchase, you swipe your card (or enter your phone number), and, in return, you get a discount of some kind. The upside to this is that you save money on your groceries. The downside? The grocery store chain now has a record of everything you’ve ever bought, and when you bought it. The Scripting Guy who writes this column doesn’t like that; he believes that anything he buys at the grocery store is his own business.

Note. Admittedly, he’s also concerned about the fact that, if someone keeps track of something then that something will end up being posted on the Web sooner or later. The Scripting Guy who writes this column has no interest in having every detail of his life made publicly available.

Of course, if he led a more interesting life he might think differently about that.

At any rate, that’s his moral and philosophical reason for disliking grocery store discount cards. He also has a more practical objection to these cards. For example, this morning the Scripting Guy who writes this column stopped at the store to pick up a couple of doughnuts. The guy in line in front of him (in the only checkout line that was open at the time) had his purchase rung up, then tried swiping his discount card. It didn’t work. He tried swiping it again; it still didn’t work. The grocery store clerk took the card and tried entering the card number by hand. No go. She tried again. Nothing. They then tried entering the man’s phone number. Nothing. At that point the clerk said, “Something must be wrong with your account. Why don’t you fill out this application and I’ll issue you a brand-new card?” The man grabbed the application and began filling it out. Eventually he completed the application, received a new card, got his discount, and then finally left.

So how long did all that actually take? Let’s put it this way: by the time the Scripting Guy who writes this column had his turn at the checkout counter he’d already eaten both the doughnuts.

Although, come to think of it, he has a tendency to do that even when there isn’t a delay at the check stand.

Oh, well; easy come, easy go. Fortunately, the Scripting Guys don’t make you sit around for the longest time before they finally get around to taking care of business.

Speaking of which, let’s talk scripting for awhile. As we understand it, FC, you have a file (or maybe a bunch of files) that include this text, where the underscore represents the tab character:

rowsep="1">_

What you’d like to do is replace each of those values with this:

rowsep="1"><para>

Unfortunately, though, you’ve run into two problems: the double quotes within the target string, and the tab character at the end of the target string. How can you search for text that includes double quotes and a tab character? Well, here’s one way to do that:

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

strTargetText = "rowsep=" & Chr(34) & "1" & Chr(34) & ">" & vbTab
strReplacementText = "rowsep=" & Chr(34) & "1" & Chr(34) & "><para>"

strNewText = Replace(strText, strTargetText, strReplacementText)

Set objFile = objFSO.OpenTextFile("C:\Scripts\Test.txt", ForWriting)
objFile.WriteLine strNewText
objFile.Close

Let’s see if we can explain what we’re doing here. (Well, as far as the script goes anyway. Explaining what we’re doing here, in a metaphysical sense, goes a bit beyond what we can do in a daily scripting column.) As you can see, we start out in pretty straightforward fashion, defining a pair of constants named ForReading and ForWriting. We’ll use these two constants when we go to open our text file: we’ll use ForReading when we open the file to read in the original contents, then use ForWriting when we reopen the file to overwrite the existing contents with our modified contents.

Note. Yes, it might seem a little silly, but we can’t modify the contents of the file in a single operation. Instead, we need to open the file, read the contents into memory, then close the file. After we make our changes to this virtual copy of the file, we then reopen the file and replace the original contents with our virtual copy.

After defining our two constants we create an instance of the Scripting.FileSystemObject, then use this line of code to open the file C:\Scripts\Test.txt for reading:

Set objFile = objFSO.OpenTextFile("C:\Scripts\Test.txt", ForReading)

Once the file is open we use the ReadAll method to read in the entire contents of the file, storing those contents in a variable named strText. And then once the file is read we call the Close method. (But don’t worry; like we said, we’ll be reopening the file again in due time.)

That brings us to these two lines of code:

strTargetText = "rowsep=" & Chr(34) & "1" & Chr(34) & ">" & vbTab
strReplacementText = "rowsep=" & Chr(34) & "1" & Chr(34) & "><para>"

To tell you the truth, that was our first thought, too. But these lines of code are nowhere near as scary (or as complicated) as they might first look. In line 1, we’re simply defining the text that we want to search for. As you might recall, that happens to be the following, with the underscore representing the tab character:

rowsep="1">_

As you know by now, FC, working with text like this can be quite … interesting …. Until you’ve tried it, you might believe that you could assign this value to a variable by using code similar to this:

strTargetText = "rowsep="1">     "

Once you have tried it, however, you know better. That’s not going to work, and for two reasons:

The double quotes in the middle of the string create problems. That’s because VBScript uses double quotes to indicate where a string value begins and ends. In this case, and thanks to the double quotes, VBScript thinks our string is this: "rowsep=". Because of that, a syntax error occurs when VBScript discovers additional characters beyond the closing quote mark. (Or at least what it thinks is a closing quote mark.)

Simply pressing the TAB key when assigning a value to a string doesn’t actually assign the tab character to that string; instead, it simply assigns it five blank spaces. Which, though it might look the same, is definitely not the same.

In other words, we have to come up with a workaround method for assigning the target text to the variable strTargetText. Our solution involves combining the following string elements:

Beginning double quote marks (").

The string value rowsep=.

The function Chr(34). Chr(34) is actually the ASCII representation of the double quote marks. That means we can simply add Chr(34) to our string without having to type actual double quote marks. In turn, that means we can avoid all the problems inherent in having quote marks within quote marks. (And yes, there are other ways we can embed quote marks within quote marks. We just think that this approach is the easiest, especially for beginning script writers.)

The string value 1.

Another instance of Chr(34). In other words, another set of double quote marks.

The string value >.

A tab character, indicated by the VBScript constant vbTab. Need to indicate that your string includes a tab character? Don’t do that by pressing the TAB key on the keyboard; do that by using vbTab.

Closing double quote marks (").

Or, to put this a little more visually, with the underscore once again representing the tab character, and substituting actual quote marks for Chr(34):

"
+     rowsep=
+            "
+             1
+              "
+               >
+                _
+                 "
_____________________

     "rowsep="1">_"

What do you know? We got exactly what we were looking for, thanks to Chr(34) and vbTab.

After we construct our string we assign the value to a variable named strTargetText. We then use a similar approach to constructing the replacement text:

strReplacementText = "rowsep=" & Chr(34) & "1" & Chr(34) & "><para>"

The only difference here? Instead of having a tab character at the end we have the string value <para>.

Note. Wouldn’t it have been easier to search for all the tab characters and replace them with <para>? Yes, but only if we wanted to replace all the tab characters. From FC’s email, it sounded like only specific tab characters (i.e., those following rowsep="1") are supposed to be replaced. That’s why we took this somewhat more-complicated (but also more-foolproof) approach.

Once we’ve defined our target text and our replacement text, doing the actual replacement is no harder than calling the Replace function:

strNewText = Replace(strText, strTargetText, strReplacementText)

That command is going to look for instances of the target text within the value of strText, then replace any found instances with the replacement text. After that’s done this modified text file (or at least our virtual text file) will then be assigned to a variable named strNewText.

Pretty easy when you get right down to it, isn’t it?

Of course, as we keep pointing out, all we’ve done so far is modify our virtual copy of the text file. To modify the actual text file itself, we need to execute this block of code:

Set objFile = objFSO.OpenTextFile("C:\Scripts\Test.txt", ForWriting)
objFile.WriteLine strNewText
objFile.Close

In line 1, we reopen the file C:\Scripts\Test.txt, this time for writing. In line 2 we actually do that writing, using the WriteLine method to write the value of the variable strNewText (our modified virtual file) to Test.txt. And then, in line 3 we call the Close method to close the file. At that point we are officially done.

And, not incidentally, the target text within Test.txt will have been replaced as well.

Now, where we were? Oh, right: grocery store discount cards. As it turns out, the man who had trouble with his discount card bought $2.50 worth of bagels. Once they finally got his account straightened out the clerk rang up his savings: 10 cents. We all stood in line for a good 10 minutes just so this guy could save 10 cents.

Note. At first that hardly seemed worth all the time and trouble. On the other hand, suppose you could save 10 cents each day. At the end of the year, you’d have saved $36.50. At the end of a thousand years, you’d save $36,500.

Gee, now that we put it that way, maybe we need to rethink our opposition to grocery store discount cards. After all, how long could a thousand years take?