th<\/sup> column in each line, eh?<\/p>\nBefore we take a look at the script let’s take a look at AM’s text file:<\/p>\n
RD|I|42|101|0047|42603|1|1|008|099|20060123|11:00|24.00000|||||||||||||||\nRD|I|42|101|0047|42603|1|1|008|099|20060123|12:00|9993||||||||||||||\nRD|I|42|101|0047|42603|1|1|008|099|20060123|13:00|17.80000|||||||||||||||\nRD|I|42|101|0047|42603|1|1|008|099|20060123|14:00|-17.30000|||||||||||||||\nRD|I|42|101|0047|42603|1|1|008|099|20060123|15:00|-28.90000|||||||||||||||\nRD|I|42|101|0047|42603|1|1|008|099|20060123|16:00|-30.80000|||||||||||||||\nRD|I|42|101|0047|42603|1|1|008|099|20060123|17:00|-.40000|||||||||||||||<\/pre>\nYes, it is<\/i> a very nice little text file, isn’t it? However, we’re interested in only one small portion of the text file: we’re interested in only the 13th<\/sup> field (column) in each line. For example, in line 1 that’s the value 24.00000; in line 2, that’s the value 9993. What we need to do is look at this value for each line in the file and take action if:<\/p>\n\n\n\n| •<\/td>\n | \n The value begins with the characters 999.<\/p>\n<\/td>\n<\/tr>\n |
\n| •<\/td>\n | \n The value is greater than -.51 but less than 0.<\/p>\n<\/td>\n<\/tr>\n |
\n| •<\/td>\n | \n The value is less than -.51.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n How are we going to do all that? Why, by running this script, of course: <\/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 arrItems = Split(strLine, \"|\")\n dblValue = arrItems(12)\n dblValue = CDbl(dblValue)\n\n i = 0\n\n If Left(dblValue, 3) = \"999\" Then\n i = 1\n End If\n\n If dblValue > -.51 AND dblValue < 0 Then\n i = 1\n End If\n\n If dblValue < -.51 Then\n arrItems(12) = 0\n End If\n\n If i = 0 Then\n strLine = Join(arrItems, \"|\")\n strNewContents = strNewContents & strLine & vbCrLf\n End If\nLoop\n\nobjFile.Close\n\nSet objFile = objFSO.OpenTextFile(\"C:\\Scripts\\Test.txt\", ForWriting)\nobjFile.Write strNewContents\nobjFile.Close<\/pre>\nAs you can see, we kick things off in the same way we kick off many of our scripts: by defining a pair of constants (ForReading and ForWriting) that we’ll need in order to open our text file in the correct mode. (That is, do we want to open the text file in order to read from it or write to it?) After defining the two constants we create an instance of the Scripting.FileSystemObject<\/b>, then use the following line of code to open the file C:\\Scripts\\Test.txt for reading:<\/p>\nSet objFile = objFSO.OpenTextFile(\"C:\\Scripts\\Test.txt\", ForReading)<\/pre>\nAnd yes, in and of itself that is<\/i> pretty exciting, isn’t it? But trust us: we can do more with this text file than just open it for reading.<\/p>\n Like what, you ask? Well, how about setting up a Do Until loop that loops until the file’s AtEndOfStream<\/b> property is True? (That is, until we’ve read every last bit and byte in the file.) Setting up a loop like this one enables us to use the ReadLine<\/b> method to read in the entire contents of the file, line-by-line. In fact, this bit of code uses ReadLine to read the first line in the file and store that information in a variable named strLine:<\/p>\nstrLine = objFile.ReadLine<\/pre>\nAnd that<\/i> turns out to be everything that we can do with a text file, and everything we can do with this script.<\/p>\n Oh, wait, never mind; now that we think about it, it turns out there are a few other things we can do, such as modify the text file to meet AM’s needs. Sorry for the misunderstanding.<\/p>\n In order to modify the text file to meet AM’s needs the first thing we do is use the Split<\/b> function to split the file into an array named arrItems; by splitting on the pipe separator (the |<\/b> character) we end up with an array with items similar to these (plus a bunch of empty items at the end of the array):<\/p>\n\n- RD<\/li>\n
- I <\/li>\n
- 42<\/li>\n
- 101<\/li>\n
- 0047<\/li>\n
- 42603<\/li>\n
- 1<\/li>\n
- 1<\/li>\n
- 008<\/li>\n
- 099<\/li>\n
- 20060123<\/li>\n
- 11:00<\/li>\n
- 24.00000<\/li>\n<\/ul>\n
The item of most interest to us is the last item in the list, item 12. (Remember, the first item in an array always has an index number of 0. That means that the 13th<\/sup> item in the array – the item we care about – has an index number of 12.) In order to isolate that value, and make it easy for us to work with it, our next step is to execute these two lines of code:<\/p>\ndblValue = arrItems(12)\ndblValue = CDbl(dblValue)\n<\/pre>\nIn the first line we’re simply assigning the value of array item 12 to a variable named dblValue. In the second line we use the CDbl<\/b> function to “cast” the value of dblValue to a numeric (double) data type; that ensures that VBScript will treat this value as a number and not as a string. <\/p>\n\n<\/thead>\n\n\n| \n Note<\/b>. Is that really necessary? Well, it was for us: we got some very strange results before we explicitly cast dblValue as a numeric variable.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n After setting the value of a counter variable named i to 0 we’re ready to begin applying AM’s criteria to the current line of text. For starters, AM wants to delete any lines where the 13th<\/sup> column in the text file starts with the characters 999<\/i>. That’s what this block of code is for:<\/p>\nIf Left(dblValue, 3) = \"999\" Then\n i = 1\nEnd If\n<\/pre>\nAll we’re doing here is using the Left<\/b> function to determine whether or not the first three characters in dblValue are 999<\/i>. What if they are? Well, in that case we set the value of our counter variable to 1; that’s how we’ll know that this is a line that needs to be deleted from the text file.<\/p>\n That brings us to this chunk of code:<\/p>\n If dblValue > -.51 AND dblValue < 0 Then\n i = 1\nEnd If\n<\/pre>\nIn addition to deleting any lines where the 13th<\/sup> column begins with 999 AM also wants to delete any lines where that 13th<\/sup> column has a value greater than -.51 but less than 0. (For example, a value like -.40000.) That’s what we’re checking for here: if we have a value less than 0 but greater than -.51 (that is, a negative number larger than -.51) then set the value of the counter variable i to 1. Why? You got it: because this, too is a line that we need to delete from the text file.<\/p>\nLast, but surely not least, we encounter this test:<\/p>\n If dblValue < -.51 Then\n arrItems(12) = 0\nEnd If<\/pre>\nAs you probably noticed in our previous check we don’t delete just any<\/i> line that contains a negative value in column 13; instead, we delete only those lines that have a negative value greater than -.51. So what happens if we encounter a negative value that’s less<\/i> than -.51 (for example, -17.30000)? In that case, we want to set the value of the 13th<\/sup> column to 0; that’s something we can do by assigning 0 to array item 12:<\/p>\narrItems(12) = 0<\/pre>\nAnd no, in this case we don’t set the value of the counter variable i to 1. Why not? Right again: because this is not<\/i> a line that we want to discard. This one’s a keeper.<\/p>\n Our next step is to check and see if our counter variable is equal to 0:<\/p>\n If i = 0 Then<\/pre>\nIf it is, then arrItems represents a line of text that we want to keep. With that in mind we use the Join<\/b> function to magically turn our array back into a single line of text, separating the individual columns with a pipe separator:<\/p>\nstrLine = Join(arrItems, \"|\")<\/pre>\nAnd once we’ve done that we append this line of text (and a carriage return-linefeed character) to a variable named strNewContents:<\/p>\n strNewContents = strNewContents & strLine & vbCrLf<\/pre>\nAnd then it’s back to the top of the loop, where we repeat the process with the next line in the text file.<\/p>\n So what happens when the file’s AtEndOfStream property is<\/i> True? Well, at that point we automatically exit the loop, then use the Close<\/b> method to close the file Test.txt. No sooner do we close the file then we immediately reopen it, this time for writing:<\/p>\nSet objFile = objFSO.OpenTextFile(\"C:\\Scripts\\Test.txt\", ForWriting)<\/pre>\nFrom there we call the Write<\/b> method to replace the existing contents of the file with the value of strNewContents; we call the Close method to close the file once again; and then we call it a day. If we were to go ahead now and echo back the contents of Test.txt we should see this:<\/p>\nRD|I|42|101|0047|42603|1|1|008|099|20060123|11:00|24.00000|||||||||||||||\nRD|I|42|101|0047|42603|1|1|008|099|20060123|13:00|17.80000|||||||||||||||\nRD|I|42|101|0047|42603|1|1|008|099|20060123|14:00|0|||||||||||||||\nRD|I|42|101|0047|42603|1|1|008|099|20060123|15:00|0|||||||||||||||\nRD|I|42|101|0047|42603|1|1|008|099|20060123|16:00|0|||||||||||||||<\/pre>\nAs you can see, lines where the 13th<\/sup> column started with the characters 999 have been deleted; so have any lines where the 13th<\/sup> column had a value less than 0 but greater than -.51. Oh, and any lines that had a 13th<\/sup> column less than -.51? In those cases, we’ve changed the value of column 13 to 0:<\/p>\nRD|I|42|101|0047|42603|1|1|008|099|20060123|14:00|0|||||||||||||||\nRD|I|42|101|0047|42603|1|1|008|099|20060123|15:00|0|||||||||||||||\nRD|I|42|101|0047|42603|1|1|008|099|20060123|16:00|0|||||||||||||||<\/pre>\nWhich is just exactly what AM was hoping we would do.<\/p>\n That should do it for now; we’ll be back again tomorrow with yet another Hey, Scripting Guy!<\/i> Are we concerned that tomorrow will be another bad day? Nah. What we neglected to tell you is that the Scripting Guy who writes this column got another crack at Hanford High School later in the year. In that game he had two singles and a double, scoring two runs and driving in two more; in addition, he made a dramatic running catch with two outs in the bottom of the seventh that saved the game and sent Hanford High to their first loss of the season.<\/p>\n Not only that, but one week after his 2-for-14 softball disaster he played in another tournament, this time getting 14 hits in 19 at-bats. So, see: nothing to worry about. Although, just in case, he’ll probably go out tonight and buy himself a new baseball bat, which is what he did the other two times in his life that he’s had a bad day.<\/p>\n","protected":false},"excerpt":{"rendered":" Hey, Scripting Guy! I need some help in cleaning up a text file. I have a file that contains multiple columns of data, and I need to either delete or modify lines in the file based on the value of the 13th column. Can you help me? — AM Hey, AM. You know, usually we […]<\/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-55313","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 some help in cleaning up a text file. I have a file that contains multiple columns of data, and I need to either delete or modify lines in the file based on the value of the 13th column. Can you help me? — AM Hey, AM. You know, usually we […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/55313","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=55313"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/55313\/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=55313"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=55313"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=55313"}],"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