{"id":55743,"date":"2008-04-19T01:12:00","date_gmt":"2008-04-19T01:12:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2008\/04\/19\/hey-scripting-guy-how-can-i-parse-a-text-file-and-retrieve-all-the-information-contained-within-square-brackets\/"},"modified":"2008-04-19T01:12:00","modified_gmt":"2008-04-19T01:12:00","slug":"hey-scripting-guy-how-can-i-parse-a-text-file-and-retrieve-all-the-information-contained-within-square-brackets","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/hey-scripting-guy-how-can-i-parse-a-text-file-and-retrieve-all-the-information-contained-within-square-brackets\/","title":{"rendered":"Hey, Scripting Guy! How Can I Parse a Text File and Retrieve All the Information Contained Within Square Brackets?"},"content":{"rendered":"

\"Hey, <\/p>\n

Hey, Scripting Guy! I need to parse through a text file and extract the information contained within square brackets. So far, however, I haven\u2019t been able to figure out how to do this; that\u2019s because the data isn\u2019t stored on separate lines in the file. Can you help?
— EI<\/p>\n

\"Spacer\"\"Hey,\"Script<\/a> <\/p>\n

Hey, EI. Well, today is Friday, April 18th<\/sup>, the day the National Basketball Association is supposed to vote on whether the league will allow the Seattle Sonics to be moved to Oklahoma City. To be honest, the Scripting Guy who writes this column couldn\u2019t care less; after all, he doesn\u2019t like the NBA (he likes basketball<\/i>) and he hasn\u2019t watched a Sonic game in years, not even on TV. Nevertheless, he has<\/i> been fascinated by the soap opera playing out between the city of Seattle and the Sonics owners, Oklahoma natives who promised to do everything possible to keep the team in the state of Washington and then \u2013 as recently-released emails have shown \u2013 pretty much did everything possible not<\/i> to keep the team in the state of Washington. (In fact, former owner Howard Schultz \u2013 of Starbucks fame \u2013 found their conduct so egregious that he\u2019s announced plans to sue in order to regain control of the franchise.)<\/p>\n

Admittedly, the emails in which the Sonics owners discussed plans to move the team \u2013 even while they were publicly assuring everyone that they had no plans to move the team \u2013 are interesting. But the Scripting Guy who writes this column found the following email to be the most fascinating of all; this unedited excerpt is from an email that Sonics owner Clay Bennett sent to NBA commissioner David Stern:<\/p>\n\n<\/thead>\n\n\n
\n

You are among the very few, people notwithstanding our relatively brief actual physical time together that have significantly affected my life. I view you as a role model as an extraordinarily gifted executive, a deep and compassionate thinker, and a person with a rare and unique charisma that brings out the best in everyone you touch. You are just one of my favorite people on earth and I so cherish our relationship \u2026.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

<\/div>\n

Wait a second: was that the email Clay Bennett sent to David Stern, or was that the email Ken Myer sent to the Scripting Guys asking that his script, submitted as part of the 2008 Winter Scripting Games<\/b><\/a>, be awarded full points even though the script didn\u2019t actually do what it was supposed to do?<\/p>\n

Of course, unlike David Stern, the Scripting Guys turn a deaf ear toward insincere flattery and fawning. Oklahoma City will likely get their basketball team, but Ken Myer did not<\/i> get his Scripting Games points.<\/p>\n

Well, actually he did. But that\u2019s beside the point.<\/p>\n

At any rate, we\u2019ll keep you posted as to the Sonics whereabouts. In the meantime, there are far more important matters to attend to. Matters like what, you ask? Well, consider this. EI has a text file that includes lines like these:<\/p>\n

Location1[apples][bananas]\nLocation2[bananas][cherries]\nLocation3[apples][oranges]\nLocation4[plums][strawberries]\n<\/pre>\n
EI needs to extract the text found between the square brackets; that is, the fruit names. If he can do that he can then compile a list similar to this one:<\/p>\n
apples\nbananas\nbananas\ncherries\napples\noranges\nplums\nstrawberries\n<\/pre>\n
Unfortunately, though, he doesn\u2019t know how to extract that information, in part because there might be more than one fruit per line in the text file.<\/p>\n
So do the Scripting Guys have an answer for him? Well, we thought<\/i> we did; without fully thinking the matter through we assumed we could use a simple regular expression to retrieve that data. That almost<\/i> worked, but not quite:<\/p>\n
[apples][bananas]\n[bananas][cherries]\n[apples][oranges]\n[plums][strawberries]\n<\/pre>\n
How did we end up with that<\/i>? Well, we initially tried searching for text found between a beginning bracket ([<\/b>) and a closing bracket (]<\/b>). And, if you look closely enough, you\u2019ll see that\u2019s what we got:<\/p>\n
[<\/b>apples][bananas<\/i>]<\/b><\/p>\n
We have a beginning bracket and a closing bracket, and all the text in between. Which \u2013 alas \u2013 happens to include another set of brackets.<\/p>\n
Faced with this kind of adversity, did the Scripting Guys give up? You bet we did. But a few days later we returned to the problem, and started poking around the Internet to see if anyone had solved a similar problem. Our search managed to uncover a few regular expressions that removed HTML tags from a file, but we weren\u2019t satisfied with that approach; as far as we were concerned those regular expression patterns were about as meaningful as this:<\/p>\n
^*@%@&@(!(U&@Y^T^&R@%@R^&(I@)(@()(@)I)@I(W!@!H\n<\/pre>\n
Which really isn\u2019t all that meaningful.<\/p>\n
In other words, a super-complicated regular expression search might have worked, but we decided against it. After all, we thought, there must be an easier way to get the information EI is after.<\/p>\n
And, as it turns out, there is:<\/p>\n
Const ForReading = 1\n\nSet objFSO = CreateObject(\"Scripting.FileSystemObject\")\nSet objFile = objFSO.OpenTextFile(\"C:\\Scripts\\Test.txt\", ForReading)\n\nstrContents = objFile.ReadAll\nobjFile.Close\n\nSet objRegEx = CreateObject(\"VBScript.RegExp\")\n\nobjRegEx.Global = True   \nobjRegEx.Pattern = \"\\[.{0,}\\]\"\n\nSet colMatches = objRegEx.Execute(strContents)  \n\nIf colMatches.Count > 0 Then\n   For Each strMatch in colMatches   \n       strMatches = strMatches & strMatch.Value \n   Next\nEnd If\n\nstrMatches = Replace(strMatches, \"]\", vbCrlf)\nstrMatches = Replace(strMatches, \"[\", \"\")\n\nWscript.Echo strMatches\n<\/pre>\n
Before we go any further we should point out that our solution is designed to work with EI\u2019s text file, which means it won\u2019t necessarily work with a similar file. For example, suppose EI had a file that looks like this, with additional text between the fruit names:<\/p>\n
Location1[apples]ABC[bananas]\nLocation2[bananas]DEF[cherries]\nLocation3[apples]GHI[oranges]\nLocation4[plums]JKL[strawberries]\n<\/pre>\n
Our solution won\u2019t work with this particular file, although we could easily modify it to do so. But that\u2019s another column for another day. <\/p>\n
As for today\u2019s column, we start things out by defining a constant named ForReading and setting the value to 1; that\u2019s the constant that tells the script which file mode (for reading) we want to use when opening the text file. And it\u2019s not long before we put that constant to use; as soon as the constant is defined we create an instance of the Scripting.FileSystemObject<\/b> object, then use this line of code to open the file C:\\Scripts\\Test.txt, for reading:<\/p>\n
Set objFile = objFSO.OpenTextFile(\"C:\\Scripts\\Test.txt\", ForReading)\n<\/pre>\n
Once the file is open we use the ReadAll<\/b> method to read in the entire contents of the file, storing that information in a variable named strContents. At that point we no longer need the file, so we call the Close<\/b> method and dismiss Test.txt.<\/p>\n
Our next step is to create an instance of the VBScript.RegExp<\/b> object, the object that enables us to use regular expressions in a VBScript script. (And yes, we are<\/i> going to use a regular expression here. But it\u2019s a reasonably simple regular expression.) After we create the RegExp object we set the Global<\/b> property to True, something that ensures that the script will find each instance of the target text. That brings us to this line of code:<\/p>\n
objRegEx.Pattern = \"\\[.{0,}\\]\"\n<\/pre>\n
As you probably know by now, the Pattern<\/b> property is where we define our target text. So what exactly is<\/i> our target text? Well, for starters, we\u2019re looking for a beginning bracket; that\u2019s what the \\[<\/b> is for. (We need to \u201cescape\u201d the [ character by prefacing it with a \\; that\u2019s because the [ is a reserved character in a regular expression.) We don\u2019t really care too much what comes after the beginning bracket; thus we add the construction .{0,}<\/b> which finds any character (and any number of characters) other than the newline character. That gives us a beginning bracket, a series of characters, and \u2013 last but not least \u2013 the ending bracket, which must also be escaped: \\]<\/b>.<\/p>\n
After we define the Pattern we call the Execute<\/b> method to carry out our regular expression search:<\/p>\n
Set colMatches = objRegEx.Execute(strContents)\n<\/pre>\n
When we call the Execute method, any instances of the target text that are discovered will be stored in a collection we named colMatches. If we were to echo back the value of colMatches right now we\u2019d get this:<\/p>\n
[apples][bananas]\n[bananas][cherries]\n[apples][oranges]\n[plums][strawberries]\n<\/pre>\n
As we already determined, that\u2019s not what we want<\/i> to get back.<\/p>\n
Therefore, assuming that we even have<\/i> some matches (that is, assuming the value of the collection\u2019s Count<\/b> property is greater than 0) we execute the following For Each block:<\/p>\n
For Each strMatch in colMatches   \n    strMatches = strMatches & strMatch.Value \nNext\n<\/pre>\n
All we\u2019re doing here is looping through the collection of matches, and appending each match to a variable named strMatches. When we\u2019ve finished looping through the collection strMatches will look like this:<\/p>\n
[apples][bananas][bananas][cherries][apples][oranges][plums][strawberries]\n<\/pre>\n
Does that do us any good? You bet it does. In our very next line of code, we use VBScript\u2019s Replace<\/b> function to replace any instance of the closing bracket with a carriage return-linefeed (vbCrLf<\/b>):<\/p>\n
strMatches = Replace(strMatches, \"]\", vbCrlf)\n<\/pre>\n
As soon as we do that strMatches will equal this:<\/p>\n
[apples\n[bananas\n[bananas\n[cherries\n[apples\n[oranges\n[plums\n[strawberries\n<\/pre>\n
And you\u2019re right: that is<\/i> very close to the list we were hoping to retrieve, isn\u2019t it? In fact, all we have to do now is remove all instances of the [ character and we\u2019re home free:<\/p>\n
strMatches = Replace(strMatches, \"[\", \"\")\n<\/pre>\n
So now<\/i> what does strMatches equal? Let\u2019s echo back the value and see for ourselves:<\/p>\n
apples\nbananas\nbananas\ncherries\napples\noranges\nplums\nstrawberries\n<\/pre>\n
Tah-dah! At this point EI can do whatever he wants with this list of fruits.<\/p>\n
That\u2019s all the time we have for today. Which is just as well; we\u2019re too nervous about the upcoming NBA vote to do much work anyway. Just for the heck of it, we decided to check out the Oklahoma City home page<\/b><\/a>, in order to see what might be in store for our Sonics. Here are a few of the articles featured on the home page:<\/p>\n\n\n\n\n\n\n\n\n
\u2022<\/td>\n\n
City to collect ammunition, computers and tires at State Fairgrounds<\/p>\n<\/td>\n<\/tr>\n
\u2022<\/td>\n\n
What if my sewer backs up?<\/p>\n<\/td>\n<\/tr>\n
\u2022<\/td>\n\n
Be prepared this tornado season<\/p>\n<\/td>\n<\/tr>\n
\u2022<\/td>\n\n
Storm debris pickup complete<\/p>\n<\/td>\n<\/tr>\n
\u2022<\/td>\n\n
Report potholes by phone<\/p>\n<\/td>\n<\/tr>\n
\u2022<\/td>\n\n
“This City is Going on a Diet”<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
By comparison, here are a few of the articles we found on the home page for the city of Seattle:<\/p>\n\n\n\n\n\n\n\n
\u2022<\/td>\n\n
City encourages everyone to go out for a drive: \u201cAfter all, there\u2019s no traffic anywhere\u201d<\/p>\n<\/td>\n<\/tr>\n
\u2022<\/td>\n\n
Police force considers disbanding; no crimes reported since 1997<\/p>\n<\/td>\n<\/tr>\n
\u2022<\/td>\n\n
City Council to award all King County residents a $5,000 bonus<\/p>\n<\/td>\n<\/tr>\n
\u2022<\/td>\n\n
NBA teams begging for chance to move to Seattle<\/p>\n<\/td>\n<\/tr>\n
\u2022<\/td>\n\n
Seattle to host tickertape parade for the Scripting Guys<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Sorry we can\u2019t provide a link to those articles; we seem to have misplaced the URL for the city of Seattle, and for all these articles. But that\u2019s OK; you can just take our word for it.<\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! I need to parse through a text file and extract the information contained within square brackets. So far, however, I haven\u2019t been able to figure out how to do this; that\u2019s because the data isn\u2019t stored on separate lines in the file. Can you help?— EI Hey, EI. Well, today is Friday, […]<\/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":[3,4,14,5],"class_list":["post-55743","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-scripting-guy","tag-scripting-techniques","tag-text-files","tag-vbscript"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! I need to parse through a text file and extract the information contained within square brackets. So far, however, I haven\u2019t been able to figure out how to do this; that\u2019s because the data isn\u2019t stored on separate lines in the file. Can you help?— EI Hey, EI. Well, today is Friday, […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/55743","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=55743"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/55743\/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=55743"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=55743"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=55743"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}