{"id":65983,"date":"2006-11-22T09:38:00","date_gmt":"2006-11-22T09:38:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2006\/11\/22\/how-can-i-locate-a-value-in-a-text-file-and-then-parse-the-line-of-text-where-the-value-was-found\/"},"modified":"2006-11-22T09:38:00","modified_gmt":"2006-11-22T09:38:00","slug":"how-can-i-locate-a-value-in-a-text-file-and-then-parse-the-line-of-text-where-the-value-was-found","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-locate-a-value-in-a-text-file-and-then-parse-the-line-of-text-where-the-value-was-found\/","title":{"rendered":"How Can I Locate a Value in a Text File and Then Parse the Line of Text Where the Value Was Found?"},"content":{"rendered":"

\"Hey,<\/p>\n

Hey, Scripting Guy! How can I open a CSV file, locate a value in column 1, then store the value in column 2 in a variable?<\/p>\n

— THvdH<\/p>\n

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

Hey, HvdH. We have to be honest with you, it\u2019s a little difficult for the Scripting Guy who writes this column to stay focused on his work today. After all, tomorrow is one of the biggest and best of all holidays in the US: the annual Terducken Bowl.<\/p>\n

Thanksgiving? Sure, tomorrow is also Thanksgiving in the US \u2026 we think. To tell you the truth, we feel a little sorry for Thanksgiving: it\u2019s a shame it has to fall on the same day as the Terducken Bowl. But those are the breaks, right?<\/p>\n

On the off-chance that there\u2019s someone out there who isn\u2019t familiar with the Terducken Bowl, the Bowl is an annual touch football game played between two teams: the Scripting Guy who writes this column and his Scripting Nephew (Jordan) versus the Scripting Son and yet another Scripting Nephew (Blake). To say that this year\u2019s game is the most important event in the history of the universe would be an understatement. The Scripting Guy who writes this column (and his faithful sidekick, Jordan) won the first two Terducken Bowls; that was mainly because, way back then, the Scripting Guy who writes this column was bigger and faster than the Scripting Kids. In year 3, however, the Scripting Guy who writes this column somehow became the smallest and the slowest of all the players on the field. As a result, even though each game in years 3-through-5 were hard-fought, Dylan the Scripting Son and his<\/i> faithful sidekick won the all 3 games (7 points per touchdown, first one to 100 wins).<\/p>\n

Ah, but then came the 2005 Terducken Bowl. In that<\/i> game, the Scripting Guy and Jordan stunned the crowd (and, interestingly enough, there actually was<\/i> a crowd, consisting of neighborhood kids who came out to watch) by grabbing a 21-0 lead and then coasting in from there. That tied the series at 3 wins apiece, making this year\u2019s game all the more important.<\/p>\n\n<\/thead>\n\n\n
\n

Note<\/b>. So what do you get<\/i> if you actually win the Terducken Bowl? Well, nothing really. In theory you get bragging rights for the year, but by the time we\u2019ve finished running up and down the field everyone\u2019s usually too tired and out-of-breath to do much bragging. But you know how it is: it doesn\u2019t matter whether you get<\/i> anything, it just matters whether or not you win. Especially when your son is playing for the other team.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

<\/div>\n

At any rate, the Scripting Guy who writes this column is a bit preoccupied today, what with having to plan his strategy for tomorrow\u2019s game. (of course, most years that strategy consists solely of him saying, \u201cOK, Jordan, run out as far as you can and I\u2019ll try to hit you with pass.\u201d) However, in the spirit of the holidays, the Scripting Guy who writes this column has agreed to to take a brief timeout and show you how to write a script that can open a CSV file, locate a value in column 1, then store the value in column 2 in a variable.<\/p>\n

You know, a script like this one:<\/p>\n

Const ForReading = 1\nSet objFSo = CreateObject(\"Scripting.FileSystemObject\")\nSet objFile = objFSO.OpenTextFile(\"C:\\Scripts\\Test.txt\")\nDo Until objFile.AtEndOfStream\n    strText = objFile.ReadLine\n    If InStr(strText, \"HE123-ASD-FRE-R4\") Then\n        arrText = Split(strText, \",\")\n        strVariable = arrText(1)\n        Exit Do\n    End If\nLoop\nobjFile.Close\nWscript.Echo strVariable\n<\/pre>\n
Based on your email, HvdH, it sounds like your text file looks something like this, with a bunch of computer names followed by the IP address of that computer:<\/p>\n
HE123-ASD-FRE-R01,192.168.1.5\nHE123-ASD-FRE-R02,192.168.1.6\nHE123-ASD-FRE-R03,192.168.1.33\nHE123-ASD-FRE-R04,192.168.1.9\nHE123-ASD-FRE-R05,192.168.1.44\n<\/pre>\n
Our job \u2013 or at least the job of our script \u2013 is to locate a specific computer (in this case, HE123-ASD-FRE-R04) and return the IP address: 192.168.1.9. Let\u2019s see if our proposed solution will actually do that.<\/p>\n
As you can see, we begin by defining a constant named ForReading and setting the value to 1; we\u2019ll use this constant when we open our text file. After defining the constant we create an instance of the Scripting.FileSystemObject<\/b>, then use the OpenTextFile<\/b> method to open the file C:\\Scripts\\Test.txt. That\u2019s what we do here:<\/p>\n
Set objFSo = CreateObject(\"Scripting.FileSystemObject\")\nSet objFile = objFSO.OpenTextFile(\"C:\\Scripts\\Test.txt\")\n<\/pre>\n
Our next step is to create a Do Until loop that will read through the file, line-by-line, until each and every line has been read. (How do we know when each and every line has been read? When the AtEndOfStream<\/b> property is True.) Admittedly, checking the file line-by-line is sort of a brute force method; there are probably more elegant ways to approach this problem. So then why did we settle for the brute force method? Two reasons. First, we thought this approach was far easier than, say, trying to write a regular expression to ferret out the target value. Second, when it comes to reading text files the FileSystemObject is pretty dang fast: even if your file has thousands of computer names the FileSystemObject can process that file in a matter of seconds. Because of that there\u2019s no appreciable performance penalty in taking the brute force approach over some other method. Given the choice between quick and easy and slightly-quicker but also-more-complicated, well, we\u2019ll take quick and easy every time.<\/p>\n
Inside the Do loop we use the ReadLine<\/b> method to read the first line from the text file and store that value in a variable named strText. We then use the InStr<\/b> function and the following line of code to determine whether or not the name of our target computer (HE123-ASD-FRE-R4) can be found in that line:<\/p>\n
If InStr(strText, \"HE123-ASD-FRE-R4\") Then\n<\/pre>\n
If InStr returns a 0 that means the target computer name doesn\u2019t appear anywhere in the line of text. Therefore, we simply loop around and repeat the process using the next line from the text file.<\/p>\n
Ah, but suppose InStr returns something other<\/i> than 0. (Technically, InStr returns the starting position of the target string if that string can be found). In that case, we execute the following block of code:<\/p>\n
arrText = Split(strText, \",\")\nstrVariable = arrText(1)\n<\/pre>\n
In the first line we use the Split<\/b> function to split the line of text into an array named arrText. Because we\u2019re using the comma as the delimiter that means we end up with an array consisting of these two items:<\/p>\n\n\n\n\n
\u2022<\/td>\n\n
HE123-ASD-FRE-R04<\/p>\n<\/td>\n<\/tr>\n
\u2022<\/td>\n\n
192.168.1.9<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
The item we\u2019re interested in, of course, is item 2, the IP address. As you know, in VBScript each item in an array is automatically assigned an index number: the first item is given the index number 0, the second item is given the index number 1, and so on. Because we\u2019re interested in the second item (which has an index number of 1) we can use this line of code to grab the value of that item and store it in a variable named strVariable:<\/p>\n
strVariable = arrText(1)\n<\/pre>\n
At this point we\u2019re pretty much done: we\u2019ve located the target computer, grabbed the IP address, and stored that address in a variable named strVariable. Among other things, that means that we don\u2019t need to read through the rest of file; we\u2019ve already found what we were looking for. Therefore, we call the Exit Do<\/b> statement to break out of the loop. Once we\u2019re out of the loop we use the Close<\/b> method to close the file, then echo back the value of strVariable:<\/p>\n
192.168.1.9\n<\/pre>\n
And now we truly are<\/i> done. And ready to get back to the real task at hand: tomorrow\u2019s Terducken Bowl.<\/p>\n\n<\/thead>\n\n\n
\n
Note<\/b>. In case you\u2019re wondering, the name Terducken Bowl<\/i> is derived from the terducken, a somewhat-mythical Thanksgiving dish in which you take a chicken, stuff it inside a duck, stuff the duck in side a turkey, and then cook the whole thing. To tell you the truth, we don\u2019t know of anyone who has ever actually eaten a terducken, but the Scripting Kids liked the name enough to christen the annual football game the Terducken Bowl.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
<\/div>\n
Incidentally, this is the last Hey, Scripting Guy! <\/i>column until Monday, November 27th<\/sup>; that\u2019s because we Microsoft employees get the next four days off in honor of the Terducken Bowl. And yes, that also means it will be awhile before the results of this year\u2019s game are publicly available. In fact, depending on who wins, it might be a long<\/i> while before the results of this year\u2019s game are publicly available.<\/p>\n\n<\/thead>\n\n\n
\n
Note<\/b>. Actually, the Scripting Guy who writes this column is confident of victory. This year, he\u2019s going to tell Jordan to run out as far as he can, then the Scripting Guy will try to hit him with a pass. Dylan and Blake will never know what hit them.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! How can I open a CSV file, locate a value in column 1, then store the value in column 2 in a variable? — THvdH Hey, HvdH. We have to be honest with you, it\u2019s a little difficult for the Scripting Guy who writes this column to stay focused on his work […]<\/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,21,14,5],"class_list":["post-65983","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-scripting-guy","tag-scripting-techniques","tag-string-manipulation","tag-text-files","tag-vbscript"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! How can I open a CSV file, locate a value in column 1, then store the value in column 2 in a variable? — THvdH Hey, HvdH. We have to be honest with you, it\u2019s a little difficult for the Scripting Guy who writes this column to stay focused on his work […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/65983","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=65983"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/65983\/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=65983"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=65983"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=65983"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}