{"id":66683,"date":"2006-08-15T21:48:00","date_gmt":"2006-08-15T21:48:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2006\/08\/15\/how-can-i-remove-double-double-quotes-from-a-text-file\/"},"modified":"2006-08-15T21:48:00","modified_gmt":"2006-08-15T21:48:00","slug":"how-can-i-remove-double-double-quotes-from-a-text-file","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-remove-double-double-quotes-from-a-text-file\/","title":{"rendered":"How Can I Remove Double Double Quotes From a Text File?"},"content":{"rendered":"

\"Hey, \n

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<\/I> 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: \u201cdata2\u201d?

— RH<\/P>\"Spacer\"\"Hey,\"Script<\/A> \n

Hey, RH. Yes, it\u2019s true: Google\u2122 is busy sending cease-and-desist letters to various news organizations and the like, asking them to refrain from using \u201cgoogle\u201d as a verb. It\u2019s still OK to say, \u201cI did a Google Internet search for x.\u201d However, in their mind it\u2019s not<\/I> OK to say, \u201cI googled x.\u201d<\/P>\n

We don\u2019t know whether Google will succeed in their quest; after all, Xerox\u00ae, Kleenex\u00ae, and Jell-O\u00ae all tried \u2013 not very successfully \u2013 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\u2019s OK to use \u201cscripting guysed\u201d as a replacement verb for \u201cgoogled.\u201d If you don\u2019t like saying, \u201cI did a Google Internet search for x,\u201d then feel free to say \u201cI scripting guysed x.\u201d Consider it our little gift to the world.<\/P>\n\n<\/THEAD>\n\n\n
\n

Note<\/B>. We should hasten to add that we\u2019re not giving away everything<\/I>, however. For example, don\u2019t even think<\/I> about writing a daily column in which the answer never seems to have anything to do with the question. That\u2019s a Scripting Guys exclusive.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n

<\/DIV>\n

OK, now, where were we? Oh, that\u2019s right: RH has a CSV file with data similar to this:<\/P>

“data1″,””data2″”,”data3″,””data4″”\n<\/PRE>\n
What RH would like<\/I> to have is a CSV file with data similar to this, with just a single set of double quotes surrounding each item:<\/P>
“data1″,”data2″,”data3″,”data4”\n<\/PRE>\n
Can the Scripting Guys provide a solution to RH\u2019s problem? Let\u2019s put it this way: when your name is synonymous with searching the Internet, hey, you can do almost anything:<\/P>
Const ForReading = 1\nConst ForWriting = 2<\/p>\n
Set objFSo = CreateObject(“Scripting.FileSystemObject”)\nSet objFile = objFSO.OpenTextFile(“C:\\Scripts\\Test.txt”, ForReading)<\/p>\n
strContents = objFile.ReadAll<\/p>\n
objFile.Close<\/p>\n
strFindText = Chr(34) & Chr(34)\nstrReplaceText = Chr(34)<\/p>\n
strContents = Replace(strContents, strFindText, strReplaceText)<\/p>\n
Set objFile = objFSO.OpenTextFile(“C:\\Scripts\\Test.txt”, ForWriting)<\/p>\n
objFile.WriteLine strContents<\/p>\n
objFile.Close\n<\/PRE>\n
Now, admittedly, you could scripting guys the Internet and search for information about the various techniques we\u2019ve used in this script. But no need to do that; as long as we\u2019re here we\u2019ll go ahead and explain how the script works.<\/P>\n
To begin with, we define a pair of constants \u2013 ForReading and ForWriting \u2013 which we\u2019ll use when opening the text file. (And yes, we need two constants for this; that\u2019s because we\u2019ll need to open the file two different times, and in two different modes.) Next we create an instance of the Scripting.FileSystemObject<\/B>, then use this line of code to open the file C:\\Scripts\\Test.txt for reading:<\/P>
Set objFile = objFSO.OpenTextFile(“C:\\Scripts\\Test.txt”, ForReading)\n<\/PRE>\n
Once the file is open we then use the ReadAll<\/B> method to read in the entire file and store the contents in a variable named strContents:<\/P>
strContents = objFile.ReadAll\n<\/PRE>\n
And yes, there is<\/I> a good reason why we do that: the FileSystemObject doesn\u2019t 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\u2019ve made the required changes we\u2019ll reopen the file \u2013 this time for writing \u2013 and overwrite the existing contents with the new, modified contents currently in memory.<\/P>\n
Got that? Good. If that\u2019s the case, then you\u2019ll 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<\/I> kind of silly. But that\u2019s the way the FileSystemObject works.)<\/P>\n
Now we\u2019re ready to do a little searching and replacing; what we\u2019re going to do is replace any set of double quotes \u2013 “” \u2013 with a set of single quotes: “. To do that, however, we\u2019re going to use the Chr<\/B> function to indicate the ASCII value of the characters we\u2019re 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<\/I> pair of double double quotes. Something like this:<\/P>
“”””””\n<\/PRE>\n
That\u2019s 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):<\/P>
strFindText = Chr(34) & Chr(34)\n<\/PRE>\n
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\u2019re searching for: “”.<\/P>\n
Of course, we don\u2019t just want to find<\/I> 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):<\/P>
strReplaceText = Chr(34)\n<\/PRE>\n
Now, at long last, we can call the Replace<\/B> function, passing this function \u2013 in order \u2013 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:<\/P>
strContents = Replace(strContents, strFindText, strReplaceText)\n<\/PRE>\n
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:<\/P>
Set objFile = objFSO.OpenTextFile(“C:\\Scripts\\Test.txt”, ForWriting)\n<\/PRE>\n
The rest is easy: we call the WriteLine<\/B> method to replace the existing contents of the file with the value stored in strContents, and then close the file. <\/P>\n
That\u2019s really all there is to it. We\u2019re 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<\/I> with whipped cream on it). See you tomorrow.<\/P><\/p>\n","protected":false},"excerpt":{"rendered":"
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: \u201cdata2\u201d?— RH […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[40,3,4,14,5],"class_list":["post-66683","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-filesystemobject","tag-scripting-guy","tag-scripting-techniques","tag-text-files","tag-vbscript"],"acf":[],"blog_post_summary":"
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: \u201cdata2\u201d?— RH […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/66683","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=66683"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/66683\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=66683"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=66683"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=66683"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}