Hey, Scripting Guy! I have a comma-separated values file that has data similar to this: data1″,””data2″”,”data3″,””data4″”. As you can see, some of the items are enclosed by two sets of double quote marks (like “”data2″”). How can I remove one of those sets of double quote marks, leaving the item looking like this: “data2”?

— RH

Hey, RH. Yes, it’s true: Google™ is busy sending cease-and-desist letters to various news organizations and the like, asking them to refrain from using “google” as a verb. It’s still OK to say, “I did a Google Internet search for x.” However, in their mind it’s not OK to say, “I googled x.”

We don’t know whether Google will succeed in their quest; after all, Xerox®, Kleenex®, and Jell-O® all tried – not very successfully – to keep their brand names from becoming generic terms for photocopying, tissue, and gelatin, respectively. After giving this considerable thought, however, the Scripting Guys have decided that we would like to help Google out as best we can. Therefore, we are officially declaring that it’s OK to use “scripting guysed” as a replacement verb for “googled.” If you don’t like saying, “I did a Google Internet search for x,” then feel free to say “I scripting guysed x.” Consider it our little gift to the world.

OK, now, where were we? Oh, that’s right: RH has a CSV file with data similar to this:

“data1″,””data2″”,”data3″,””data4″”

What RH would like to have is a CSV file with data similar to this, with just a single set of double quotes surrounding each item:

“data1″,”data2″,”data3″,”data4”

Can the Scripting Guys provide a solution to RH’s problem? Let’s put it this way: when your name is synonymous with searching the Internet, hey, you can do almost anything:

Const ForReading = 1
Const ForWriting = 2

Set objFSo = CreateObject(“Scripting.FileSystemObject”)
Set objFile = objFSO.OpenTextFile(“C:\Scripts\Test.txt”, ForReading)
strContents = objFile.ReadAll
objFile.Close
strFindText = Chr(34) & Chr(34)
strReplaceText = Chr(34)
strContents = Replace(strContents, strFindText, strReplaceText)
Set objFile = objFSO.OpenTextFile(“C:\Scripts\Test.txt”, ForWriting)
objFile.WriteLine strContents
objFile.Close

Now, admittedly, you could scripting guys the Internet and search for information about the various techniques we’ve used in this script. But no need to do that; as long as we’re here we’ll go ahead and explain how the script works.

To begin with, we define a pair of constants – ForReading and ForWriting – which we’ll use when opening the text file. (And yes, we need two constants for this; that’s because we’ll need to open the file two different times, and in two different modes.) Next 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 then use the ReadAll method to read in the entire file and store the contents in a variable named strContents:

strContents = objFile.ReadAll

And yes, there is a good reason why we do that: the FileSystemObject doesn’t allow us to directly manipulate the text file itself. Because of that, we need to read the file and store the contents in memory; we can then do a search-and-replace operation on that copy of the file. After we’ve made the required changes we’ll reopen the file – this time for writing – and overwrite the existing contents with the new, modified contents currently in memory.

Got that? Good. If that’s the case, then you’ll understand why, after reading from the file, we immediately close it: with the file open for reading, we can no longer do anything with it. Instead, we have to close it, then reopen it for writing. (Yes, that is kind of silly. But that’s the way the FileSystemObject works.)

Now we’re ready to do a little searching and replacing; what we’re going to do is replace any set of double quotes – “” – with a set of single quotes: “. To do that, however, we’re going to use the Chr function to indicate the ASCII value of the characters we’re searching for. We do that to help prevent anyone who reads our script from getting a headache; because double quotes are a reserved character in VBScript the only way to search for double double quotes is to store those double double quotes inside another pair of double double quotes. Something like this:

“”””””

That’s way too many double quotes for the Scripting Guys to deal with. Therefore, we sidestep the problem of embedding quotes within quotes within quotes by, instead, searching for consecutive instances of Chr(34):

strFindText = Chr(34) & Chr(34)

As you might have guessed, Chr(34) is equivalent to the double quote mark: “. Consequently, Chr(34) & Chr(34) will be equal to the very thing we’re searching for: “”.

Of course, we don’t just want to find a pair of double quote marks; we want to replace each of these pairs with a single set of double quote marks. Therefore, in addition to defining the target text, we also need to define the replacement text. Needless to say, that happens to be a single instance of Chr(34):

strReplaceText = Chr(34)

Now, at long last, we can call the Replace function, passing this function – in order – the string value we want to search (the variable strContents); the target text we want to search for (strFindText); and the text we want to use to replace the target text (strReplaceText). That all sounds very complicated, but it really requires just a single line of code:

strContents = Replace(strContents, strFindText, strReplaceText)

Execute this line of code, and the variable strContents will contain a corrected copy of the text file, a copy that has all the doubled-up double quotes removed. Once we have that we can reopen the file Test.txt, this time for writing:

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

The rest is easy: we call the WriteLine method to replace the existing contents of the file with the value stored in strContents, and then close the file.

That’s really all there is to it. We’re just going to walk over to the photo copy machine and scripting guys this column, then maybe have a dish of strawberry Scripting Guys (of course with whipped cream on it). See you tomorrow.