\n| \u2022<\/TD>\n | \n adFldIsNullable<\/B> tells the script that it\u2019s OK to have Null values in the field. We won\u2019t actually have<\/I> Null values in either of our fields, but this covers us in case we run into a line of text where column 5 is blank.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n And then we have one of our old favorites, the constant ForReading, which we\u2019ll use when we open the text file C:\\Scripts\\Test.txt. Good to see you, ForReading; glad you could make it.<\/P>\n After we define all the constants we create an instance of the ADOR.Recordset<\/B> object, the object that enables us to construct a disconnected recordset. We then use these two lines of code to add two fields (LineText and SortCharacter) to the recordset:<\/P>DataList.Fields.Append “LineText”, adVarChar, MaxCharacters, adFldIsNullable\nDataList.Fields.Append “SortCharacter”, adVarChar, MaxCharacters, adFldIsNullable\n<\/PRE>\nOnce we\u2019ve done that we use the Open<\/B> method to open the recordset and prepare for data entry:<\/P>DataList.Open\n<\/PRE>\nNext we create an instance of the Scripting.FileSystemObject<\/B> object, then use the OpenTextFile<\/B> method to open the file Test.txt for reading:<\/P>Set objFile = objFSO.OpenTextFile(“C:\\Scripts\\Test.txt”, ForReading)\n<\/PRE>\nAnd then we\u2019re done. See you all tomorrow!<\/P>\n Oh, wait; apparently we aren\u2019t<\/I> done, at least not yet. With that in mind, we next set up a Do Until loop designed to run until we\u2019ve read all the lines in the text file (that is, until the file\u2019s AtEndOfStream<\/B> property is True):<\/P>Do Until objFile.AtEndOfStream\n<\/PRE>\nWhat do we do inside this loop? Well, for starters, we use the ReadLine<\/B> method to read the first line in the text file and store that information in a variable named strLine:<\/P>strLine = objFile.ReadLine\n<\/PRE>\nAt that point we execute these two lines of code:<\/P> arrFields = Split(strLine, vbTab)\nstrCharacter = arrFields(4)\n<\/PRE>\nWhat are we doing here? Well, as you might recall, in addition to the production of coin and currency, the disbursement of payments to the American public, revenue collection, and the borrowing of funds necessary to run the federal government, our script also needs to sort the lines in the text file based on the value of column 5. That\u2019s fine, except for this: how in the world are we supposed to know what the value of column 5 is? <\/P>\n Well, one easy way to get at that information is to use VBScript\u2019s Split<\/B> method to split the line of text into an array (splitting, of course, on the tab character, which is represented by the VBScript constant vbTab<\/B>). When we do that we\u2019ll get an array that looks like this:<\/P>wer \nred \nring \ncic \nMswf\n<\/PRE>\nAs you can see, the last item in the array, item 4 (remember, the first item in an array is always item 0), just happens to be column 5. That\u2019s<\/I> how we can determine the value of column 5, a value we stash in a variable named strCharacter.<\/P>\n We now have the complete text of the line stored in the variable strLine, and the value of column 5 stored in the variable strCharacter. And because we do<\/I> have both those pieces of information we can use this block of code to add the text and the value of column 5 to our database:<\/P>DataList.AddNew\nDataList(“LineText”) = strLine\nDataList(“SortCharacter”) = strCharacter\nDataList.Update\n<\/PRE>\nAs you can see, we store the complete text of the line in the LineText field and the value of column 5 in the SortCharacter field. By storing these two values in separate fields it\u2019s going to be very easy to sort the lines in the text file by the value in column 5.<\/P>\n As you\u2019ll find out in just a second.<\/P>\n After we add the information from line 1 to the disconnected recordset we zip back to the top of the loop and repeat the process with the next line in the text file. Once we\u2019ve processed each line in the file we close Test.txt, then use the Sort<\/B> method to sort these lines of text, first by column 5 (SortCharacter) and then by the actual line of text itself:<\/P>DataList.Sort = “SortCharacter, LineText”\n<\/PRE>\nAfter that we set up a Do Until loop designed to loop through all the records in the recordset (that is, until the recordset\u2019s EOF<\/B> \u2013 end-of-file \u2013 property is True):<\/P>Do Until DataList.EOF\n<\/PRE>\nInside that loop we simply echo back the value of the LineText property, then use the MoveNext<\/B> method to move on to the next record in the recordset:<\/P>Wscript.Echo DataList.Fields.Item(“LineText”)\nDataList.MoveNext\n<\/PRE>\nAnd what will that give us? That will give us output that looks like this:<\/P> dde uyt jhg der Cabc\nswe ffo ccc aqs Ihte\nwin der jhg ppp Iwol\nwer red ring cic Mswf\n<\/PRE>\nIn case you\u2019re wondering, that\u2019s the text from Test.txt, sorted by the value in column 5. Mission accomplished. And now we are<\/I> done.<\/P>\n Well, we\u2019re done with IR\u2019s problem, that is. As for the problem with the Scripting Guy\u2019s account at the Treasury Department, well, we\u2019re still working on that (must be a lot of people trying to log onto the server and make sure that they are in compliance with the database). In the meantime, the Scripting Guy who writes this column did<\/I> stumble upon an interesting Web site<\/B><\/A> that lists the current national debt for the USA. In case you\u2019re wondering, as of this writing the US owes a total of $ 9,334,965,822,890.37. <\/P>\nBut don\u2019t worry; turns out that you can write a check (made out to the Bureau of Public Debt) and help pay down that balance of $9 trillion. Just send your check here:<\/P>\n Attn Dept G
Bureau Of the Public Debt
P. O. Box 2188
Parkersburg, WV 26106-2188<\/P>\n And that\u2019s a good question: wouldn\u2019t the Scripting Guys be doing everyone a public service by just going ahead and paying off the national debt? Yes, we would, and that\u2019s something we\u2019ll seriously consider. Granted, what with the Scripting Son starting college this fall it\u2019s not going to be easy to come up with an additional $9 trillion. But we\u2019ll see what we can do.<\/P><\/p>\n","protected":false},"excerpt":{"rendered":" Hey, Scripting Guy! I have a text file composed of many different lines; each line has a number of fields, with the fields separated from one another by a tab character. I need to sort this text file based on the value of one of these fields. How can I do that?— IR Hey, IR. […]<\/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-55663","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 have a text file composed of many different lines; each line has a number of fields, with the fields separated from one another by a tab character. I need to sort this text file based on the value of one of these fields. How can I do that?— IR Hey, IR. […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/55663","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=55663"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/55663\/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=55663"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=55663"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=55663"}],"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,")
\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> \n