Hey, Scripting Guy! How Can I Replace Text That Includes Double Quote Marks and a Tab Character?

ScriptingGuy1

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

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.

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.

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:

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:

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>.

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.

ScriptingGuy1

Follow