![\"Hey,](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/q-for-powertip.jpg\" "\\"Hey,")
\n \n
Hey, Scripting Guy! I saw your column on replacing text in a text file<\/B><\/A>, and that\u2019s almost<\/I> the solution I\u2019m looking for. However, the text I need to replace is the single quote mark, and I can\u2019t figure out how to do that. How do I replace single quote marks in a text file? 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\u2019s still one thing that remains first and foremost in the mind of the Scripting Guy who writes this column: what should<\/I> he have for dinner tonight?<\/P>\n Maybe chicken casserole; the Scripting Family hasn\u2019t had chicken casserole in awhile. And maybe \u201ccandied carrots,\u201d you know, the ones with the butter and the brown sugar glaze on them. Or maybe \u2026.<\/P>\n 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\u2019t 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<\/I> quote marks!<\/P>\n OK, sure, that sounds<\/I> impossible. But the proof, as they say, is in the pudding.<\/P>\n Hmmm, pudding might be good, too \u2026.<\/P>\n Here\u2019s the script:<\/P> Set objFSO = CreateObject(“Scripting.FileSystemObject”)\nSet objFile = objFSO.OpenTextFile(“C:\\Scripts\\Test.txt”, ForReading)<\/p>\n strText = objFile.ReadAll\nobjFile.Close<\/p>\n strOldText = Chr(39)\nstrNewText = Chr(34)<\/p>\n strNewText = Replace(strText, strOldText, strNewText)<\/p>\n Set objFile = objFSO.OpenTextFile(“C:\\Scripts\\Test.txt”, ForWriting)\nobjFile.WriteLine strNewText\nobjFile.Close\n<\/PRE>\n What we\u2019re assuming here is that we have a text file that looks something like this:<\/P> All we want to do is replace all the single quote marks with double quote marks. As JB has discovered, however, that\u2019s not quite as easy as it sounds. Why not? We\u2019ll explain that in a moment. Before we do that, however, let\u2019s talk about the first part of the script.<\/P>\n As you can see, we kick things off by defining a pair of constants (ForReading and ForWriting); we\u2019ll use these constants when we open our text file. (And yes, we do<\/I> 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<\/B>, then use the OpenTextFile<\/B> method to open the file C:\\Scripts\\Test.txt for reading:<\/P> What do we do with the file once it\u2019s open? For starters, we use the ReadAll<\/B> 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.<\/P>\n Note<\/B>. Yes, that does<\/I> seem sort of silly, doesn\u2019t it? However, the FileSystemObject will only let us open a file and read from it, or<\/I> open a file and write to it; we can\u2019t carry out both operations at the same time. (Strange but true.) Having read from the file we have no choice but to close the thing before we\u2019ll be allowed to write to it.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n In a typical search-and-replace script this would be the point where we would call the VBScript Replace<\/B> function and replace the single quote marks with double quote marks. We\u2019re guessing that JB has tried to do just that, using a line of code similar to this:<\/P> Now, syntactically, that looks OK; after all, we\u2019ve called the Replace function followed by three parameters: <\/P>\n strText<\/B>, the text we want to do the search-and-replace operation on.<\/P><\/TD><\/TR>\n “‘”<\/B>, the single quote mark (embedded between a pair of double quote marks), representing the text to be replaced.<\/P><\/TD><\/TR>\n “””<\/B>, the double quote mark (embedded between a pair of double quote marks), representing the replacement text.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n Like we said, that looks<\/I> OK. But here\u2019s what happens when we try to use this code:<\/P> Yikes! What\u2019s that<\/I> all about?<\/P>\n 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:<\/P> Meanwhile, the double quote mark is used \u2013 among other things \u2013 to enclose string values:<\/P> Because these characters have special meanings, all we\u2019ve managed to do so far is to confuse VBScript. For example, a human being might look at three consecutive double quote marks and think, \u201cOh, this is just a double quote mark surrounded by two other double quote marks.\u201d Unfortunately, though, VBScript doesn\u2019t 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.<\/P>\n Is that a problem? You bet it is: because of the syntax error the script won\u2019t even compile, let alone run.<\/P>\n This \u2013 or some variation of it \u2013 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:<\/P> What we\u2019re doing here is using the Chr<\/B> 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 \u2013 well, needless to say, you\u2019re way ahead of us: yes, Chr(34) is the double quote mark.<\/P>\n See what we\u2019re doing here? In the first line we\u2019ve 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 “””:<\/P> This statement is syntactically correct. Equally important, it will replace all the single quote marks with double quote marks.<\/P>\n
— JB<\/P>![\"Spacer\"](\"https:\/\/devblogs.microsoft.com\/scripting\/wp-content\/uploads\/sites\/29\/2019\/05\/spacer.gif\")
![\"Hey,](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/a-for-powertip.jpg\" "\\"Hey,")
![\"Script](\"http:\/\/img.microsoft.com\/library\/media\/1033\/technet\/images\/scriptcenter\/ad.jpg\" "\\"Script")
<\/A> \nConst ForReading = 1\nConst ForWriting = 2<\/p>\n
‘Ken Myer’,’Finance’,’Senior Analyst’\n‘Pilar Ackerman’,’Human Resources’,’Manager’\n‘Jonathan Haas’,’Transportation’,’Director’\n<\/PRE>\n
Set objFSO = CreateObject(“Scripting.FileSystemObject”)\nSet objFile = objFSO.OpenTextFile(“C:\\Scripts\\Test.txt”, ForReading)\n<\/PRE>\n
\n<\/THEAD>\n\n
\n \n <\/DIV>\nstrNewText = Replace(strText, “‘”, “””)\n<\/PRE>\n
\n\n
\n \u2022<\/TD>\n \n \n \u2022<\/TD>\n \n \n \u2022<\/TD>\n \n C:\\Scripts\\test.vbs(13, 40) Microsoft VBScript compilation error: Unterminated string constant\n<\/PRE>\n
‘ This is a comment\n<\/PRE>\n
x = “This is a string value.”\n<\/PRE>\n
strOldText = Chr(39)\nstrNewText = Chr(34)\n<\/PRE>\n
strNewText = Replace(strText, strOldText, strNewText)\n<\/PRE>\n