th<\/sup>, for those of you who believe in signs and omens). Did the Scripting Guy who writes this column feel a sense of pride on Friday as he watched his son graduate? Did the Scripting Guy who writes this column feel a little wistful on Friday, knowing that his son was no longer a little boy? Maybe it was a little of both: maybe he felt both proud and<\/i> wistful?<\/p>\nActually, for the most part the Scripting Guy who writes this column felt bored on Friday. If you haven\u2019t been to a high school graduation, the ceremonies last for about 2 hours, and consist primarily of people you don\u2019t know taking turns monopolizing the microphone and going on and on and on about something no one else even cares about.<\/p>\n
Heck, if that\u2019s how the Scripting Guy who writes this column wanted to spend 2 hours he\u2019d have just stayed at work and attended a typical Microsoft meeting.<\/p>\n
At any rate, the Scripting Guy who writes this column is<\/i> proud of his son; high school is hardly the most exciting thing he\u2019ll ever get to do, but he not only persevered but he did quite well at it, too. In fact, the Scripting Guy who writes this column was so proud that he said, \u201cSon, I\u2019d like to reward you for all the hard work you\u2019ve put in over the past 3 years. Name the one thing you\u2019d like more than anything else in the world, and I\u2019ll get it for you.\u201d<\/p>\n
\u201cYou know what, Dad?\u201d said the Scripting Son. \u201cThe one thing I\u2019d like more than anything else in the world is a script that can remove all the commas that are enclosed inside double quote marks, leaving all the other commas in the file alone.\u201d<\/p>\n
\u201cLet me make sure I understand you completely, son,\u201d said the Scripting Guy who writes this column. \u201cYou\u2019re saying that you have a text file with lines similar to this, right?\u201d<\/p>\n
\"Toshiba Laptop, AC Adapter, 50CT, 70CT\",\"1,A\",\"$1.82\",0,\"$0.00\",0,\"$0.00\"\n\"Hewlett-Packard Laptop, AC Adapter, 50CT, 70CT\",\"2,B\",\"$1.82\",0,\"$0.00\",0,\"$0.00\"\n<\/pre>\n\u201cThat\u2019s right.\u201d<\/p>\n
\u201cAnd you\u2019d like to remove any commas found in between double quote marks; for example, you\u2019d like to remove the commas after Laptop<\/i>, Adapter<\/i>, and 50CT<\/i> in the first line. Is that correct?\u201d<\/p>\n
\u201cThat\u2019s correct.\u201d<\/p>\n
Happy graduation, son:<\/p>\n
Const ForReading = 1\nConst ForWriting = 2\n\nSet objFSO = CreateObject(\"Scripting.FileSystemObject\")\nSet objFile = objFSO.OpenTextFile(\"C:\\Scripts\\Test.txt\", ForReading)\n\nDo Until objFile.AtEndOfStream\n strLine = objFile.ReadLine\n intLength = Len(strLine)\n\n blnStart = False\n\n For i = 1 to intLength\n strCharacter = Mid(strLine, i, 1)\n If strCharacter = Chr(34) Then\n If blnStart = True Then\n blnStart = False\n Else\n blnStart = True\n End If\n End If\n\n If strCharacter = \",\" Then\n If blnStart = \"False\" Then\n strNewCharacters = strNewCharacters & strCharacter\n End If\n Else\n strNewCharacters = strNewCharacters & strCharacter\n End If\n Next\n\n strNewContents = strNewContents & strNewCharacters & vbCrLf\n strNewCharacters = \"\"\nLoop\n \nobjFile.Close\n\nSet objFile = objFSO.OpenTextFile(\"C:\\Scripts\\Test.txt\", ForWriting)\nobjFile.Write strNewContents\nobjFile.Close\n<\/pre>\nAs you can probably tell by looking at this code, removing certain commas from a text file (while leaving other commas in place) isn\u2019t the easiest thing in the world; that\u2019s because a seemingly-simple command like \u201cRemove any commas that are found embedded between double quote marks\u201d is nowhere near as simple as it might sound. Why not? Well, consider this simple example:<\/p>\n
\"A,B\",C,\"D,E\"<\/pre>\nSo which commas do we need to remove from the string? That\u2019s easy (for us): we just need to remove the comma between A and B and the comma between D and E. After all, those are the only two commas that are embedded within double quote marks, right? Right.<\/p>\n
Well, except for these two, of course:<\/p>\n
\",C,\"<\/pre>\nYes, we<\/i> know that these two commas aren\u2019t supposed to count as embedded commas. Sure, technically they do<\/i> come between two double quote marks, but \u2026. Like we said, this is something human beings can deal with quite easily; the problem we have is getting the computer<\/i> to understand that these two commas don\u2019t count as embedded commas. That\u2019s much tougher. <\/p>\n
So how did we tackle this issue? Well, our first thought was to use a regular expression of some kind. We soon changed our mind about that, however: although this problem can<\/i> be solved using regular expressions, well, the resulting regular expression is a bit complicated, to say the least. The truth is, in cases like this, regular expressions also have difficulty determining which commas should be deleted and which ones shouldn\u2019t<\/i> be deleted.<\/p>\n
That\u2019s why we took a completely different approach: rather than searching and replacing, we\u2019re methodically looking at each character in the text file and making decisions on a comma-by-comma basis. What does that mean, and is it really going to work? Hey, we can\u2019t tell you that, at least not right now; after all, that\u2019s what the rest of today\u2019s column is for.<\/p>\n
As you can see, our script starts off simply enough: all we do in the first two lines is define a pair of constants (ForReading and ForWriting) that we\u2019ll need in order to open our text file. After defining the constants we create an instance of the Scripting.FileSystemObject<\/b>, then use the following command to open the file C:\\Scripts\\Test.txt for reading:<\/p>\nSet objFile = objFSO.OpenTextFile(\"C:\\Scripts\\Test.txt\", ForReading)<\/pre>\nSo what do we do with this file once it\u2019s open? Well, for starters, we set up a Do Until loop that continues reading through the file until there\u2019s nothing left to read. (Or, if you\u2019re a stickler for technical accuracy, until the file\u2019s AtEndOfStream<\/b> property is True.) Inside this loop we use the ReadLine<\/b> method to read the first line in the file and store that value in a variable named strLine. That means that, the first time through the loop, strLine will be equal to this:<\/p>\n\"Toshiba Laptop, AC Adapter, 50CT, 70CT\",\"1,A\",\"$1.82\",0,\"$0.00\",0,\"$0.00\"<\/pre>\nOur next step is to use the Len<\/b> function to determine the number of characters in strLine:<\/p>\nintLength = Len(strLine)<\/pre>\n
\n<\/thead>\n\n\n| \n Note<\/b>. Why do we need to know the number of characters in strLine? Well, as you might recall, we said that we were going to look at each individual character in each line in the text file; in order to do that we need to know how many characters are in each line.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n After executing the Len function we set the value of a variable named blnStart to False (we\u2019ll have more on blnStart \u2013 and what it\u2019s for \u2013 in just a minute). That brings us to this line of code:<\/p>\n For i = 1 to intLength<\/pre>\nAll we\u2019re doing here is setting up a For Next loop that starts at 1 and continues looping until we\u2019ve looked at each character in strLine. Inside this<\/i> loop we use the Mid<\/b> function to grab the very first character in strLine and store it in a variable named strCharacter:<\/p>\nstrCharacter = Mid(strLine, i, 1)<\/pre>\nBelieve it or not we\u2019re actually beginning to make some progress here. Once we\u2019ve grabbed the first character we can check to see if this character happens to be a double quote mark (that is, does it have an ASCII value of 34). That\u2019s what this line of code is for:<\/p>\n If strCharacter = Chr(34) Then<\/pre>\nIf it turns out that we don\u2019t<\/i> have a double quote mark then we\u2019re simply going to ignore this character for now. If we do<\/i> have a double quote mark then we\u2019re going to execute this block of code:<\/p>\nIf strCharacter = Chr(34) Then\n If blnStart = True Then\n blnStart = False\n Else\n blnStart = True\n End If\nEnd If\n<\/pre>\nWhat\u2019s going on here<\/i>? Oh, darn; we were going to ask you<\/i> that! Well, as you might recall, a little while ago we created a variable named blnStart and assigned it the value False. We\u2019re going to use this variable to tell us whether or not we\u2019re inside a set of double quote marks. If blnStart is False that means we are not<\/i> inside a set of double quote marks; that also means that we don\u2019t want to delete any commas that we might find. If blnStart is True that means that we are<\/i> inside a set of double quote marks; in turn, that means that any commas we find should<\/i> be deleted.<\/p>\n Make sense? Oh. OK, then maybe we should explain it a little better. Here\u2019s a little more detail on what is happening, and why.<\/p>\n If the first character in the first line of our text file is a double quote mark (which it is), we \u201cflip\u201d the value of blnStart from False to True. We then execute this<\/i> block of block of code:<\/p>\nIf strCharacter = \",\" Then\n If blnStart = \"False\" Then\n strNewCharacters = strNewCharacters & strCharacter\n End If\nElse\n strNewCharacters = strNewCharacters & strCharacter\nEnd If\n<\/pre>\nWhat we\u2019re doing here is checking to see if the current character is a comma. If it\u2019s not<\/i> a comma then we simply append this value to a variable named strNewCharacters, a variable we\u2019ll use to build a \u201cnew\u201d text file (that is, a text file in which the embedded commas have been deleted):<\/p>\nstrNewCharacters = strNewCharacters & strCharacter<\/pre>\nThe first time through the loop strCharacter will not<\/i> be a comma; therefore, the variable strNewCharacters will end up being assigned the following value:<\/p>\n\"<\/pre>\nAnd then it\u2019s back to the top of the loop, where we repeat the process with the next character in strLine.<\/p>\n After we\u2019ve run through the loop a few times strLine will be equal to this:<\/p>\n \"Toshiba Laptop<\/pre>\nAnd then \u2013 oh, dear: the next character in the line is<\/i> a comma! Should we be panicking about now?<\/p>\n Yes, we should. But not because the next character in the line is a comma; our script can handle that just fine. We just figured that, the state of the world being what it is, any<\/i> time seems like a good time to panic.<\/p>\n Our script, however, will remain perfectly calm and in control. If strCharacter happens to be a comma, we check to see if blnStart is equal to False. If it is, that means we\u2019re not inside a set of double quote marks; that also means that we want to keep this comma. (Why? Because it\u2019s a separator in our comma-separated values file.) If blnStart is False we append this comma to strNewCharacters; if blnStart is True then we discard this comma altogether. Why do we throw it out? You got it: because we\u2019re getting rid of any commas that are enclosed in double quote marks.<\/p>\n Eventually, strNewCharacters will be equal to this (note that all the embedded commas have been deleted):<\/p>\n \"Toshiba Laptop AC Adapter 50CT 70CT<\/pre>\nAt this point, the next character in the text file is a double quote mark. Because this is the closing quote mark in our set of double quotes we end up flipping blnStart back to False; that\u2019s because we\u2019re no longer in a set of double quotes. (The first quote mark turns the set on; the second quote mark turns the set off.) That also means we will not<\/i> discard the comma that immediately follows this second double quote mark. Why not? That\u2019s right: because this comma is not embedded in a set of double quotes. As it turns out, distinguishing between commas that should be deleted and commas that shouldn\u2019t be deleted is no more difficult than that. <\/p>\n After we\u2019ve run through all the characters in line 1 we then add the modified value of strNewCharacters to a variable named strNewContents. We reset the value of strNewCharacters to an empty string (“”), then pop back to the top of our Do Until loop and repeat the entire process with the next line in the text file.<\/p>\n And then once we\u2019ve read all the lines in the text file we close the file, reopen it for writing, then use the Write<\/b> method to replace the existing contents with the value of strNewContents:<\/p>\nobjFile.Write strNewContents<\/pre>\nAll that\u2019s left now is to close the file a final time, and watch the script glide gently to a stop.<\/p>\n And what will Test.txt look like now? It should look like this, with nary an embedded comma to be found:<\/p>\n \"Toshiba Laptop AC Adapter 50CT 70CT\",\"1A\",\"$1.82\",0,\"$0.00\",0,\"$0.00\"\n\"Hewlett-Packard Laptop AC Adapter 50CT 70CT\",\"2B\",\"$1.82\",0,\"$0.00\",0,\"$0.00\"\n<\/pre>\nSuccess!<\/p>\n That should do it, RF; let us know if you run into any problems with this. As for those of you still curious about the high school graduation ceremonies, we might point out that, over the course of the evening, literally hundreds of people were not only thanked for their efforts, but received awards recognizing their achievements. Needless to say, the Scripting Guy who writes this column was neither thanked nor rewarded.<\/p>\n Hmmm; maybe he did<\/i> stay at work after all!<\/p>\n","protected":false},"excerpt":{"rendered":" Hey, Scripting Guy! I have a comma-separated values file in which several fields have a comma embedded in the field value; that\u2019s causing problems when I try and import the information into another application. I was wondering if there is a way to remove all the commas that are enclosed inside double quote marks, leaving […]<\/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":[169,3,4,5],"class_list":["post-55343","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-csv-and-other-delimited-files","tag-scripting-guy","tag-scripting-techniques","tag-vbscript"],"acf":[],"blog_post_summary":" Hey, Scripting Guy! I have a comma-separated values file in which several fields have a comma embedded in the field value; that\u2019s causing problems when I try and import the information into another application. I was wondering if there is a way to remove all the commas that are enclosed inside double quote marks, leaving […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/55343","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=55343"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/55343\/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=55343"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=55343"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=55343"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}} |
![\"Hey,](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/q-for-powertip.jpg\" "\\"Hey,")
<\/p>\n![\"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> <\/p>\n