{"id":56353,"date":"2008-01-24T22:06:00","date_gmt":"2008-01-24T22:06:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2008\/01\/24\/hey-scripting-guy-how-can-i-sort-the-contents-of-a-text-file-in-numerical-order\/"},"modified":"2008-01-24T22:06:00","modified_gmt":"2008-01-24T22:06:00","slug":"hey-scripting-guy-how-can-i-sort-the-contents-of-a-text-file-in-numerical-order","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/hey-scripting-guy-how-can-i-sort-the-contents-of-a-text-file-in-numerical-order\/","title":{"rendered":"Hey, Scripting Guy! How Can I Sort the Contents of a Text File in Numerical Order?"},"content":{"rendered":"

\"Hey,<\/p>\n

Hey, Scripting Guy! I have a text file in which each line begins with a number. How can I sort the contents of that file by those numbers?<\/p>\n

— KT<\/p>\n

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

Hey, KT. Tell you what: we’ll go ahead and answer your question. However, in return, you have to promise to forget everything we tell you. (Which is usually not a problem for most people.) What do you say: do we have a deal?<\/p>\n

Admittedly, that’s kind of an odd bargain we’re trying to strike: we agree to answer someone’s question on the condition that they immediately forget the answer. Why would we try to make a deal like that? Well, to tell you the truth, our first thought upon reading this question was, “Wow. That would make a good Scripting Games<\/strong><\/a> event. In fact, we should use this in the 2009 Games.” The only problem, of course, is that we can’t provide the answer today, then try and use this question as a Scripting Games event tomorrow. Talk about a quandary. On the one hand, we need everyone to read today’s column; after all, if nobody reads the column then there’s not much use for the Scripting Guy who writes this column, is there? <\/p>\n\n<\/thead>\n\n\n
\n

Note.<\/strong> Yes, there are plenty of people – including his fellow Scripting Guys – who wonder if there’s ever<\/i> any need for the Scripting Guy who writes this column.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

<\/div>\n

On the other hand, we need you to forget both the question and the answer; that way we can surprise everyone by using this query as an event in the 2009 Scripting Games. (The events have already been finalized for the 2008 Games, February 15th<\/sup> through March 3rd<\/sup> right here in the Script Center<\/strong><\/a>.)<\/p>\n

To make a long story short, that’s why we need everyone to – you know what? Never mind; go ahead and remember both the question and the answer if you want to. After all, the odds are pretty good that even if you upheld your<\/i> end of the bargain the Scripting Guys would forget all about it and wouldn’t use it as an event in the 2009 Scripting Games anyway.<\/p>\n

Sadly, forgetfulness is a part of growing old. For some reason, that’s something we can never remember to tell the other Scripting Guys.<\/p>\n

Fortunately, though, we did<\/i> remember to wear pants this morning (we’d rather not talk about that) and to write a script that can sort the lines in a text file based on a number that appears at the beginning of each line. As a matter of fact, we remembered to write this<\/i> script:<\/p>\n

Const ForReading = 1\nConst ForWriting =2\n\nConst adVarChar = 200\nConst MaxCharacters = 255\nConst adFldIsNullable = 32\nConst adInteger = 3\n\nSet DataList = CreateObject(\"ADOR.Recordset\")\nDataList.Fields.Append \"TextLine\", adVarChar, MaxCharacters, adFldIsNullable\nDataList.Fields.Append \"NumberLine\", adInteger, , adFldIsNullable\nDataList.Open\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    strValue = \"\"\n    For i = 1 to intLength\n        If IsNumeric(Mid(strLine, i, 1)) Then\n            strValue = strValue & Mid(strLine, i, 1)\n        Else\n            Exit For\n        End If\n    Next\n    DataList.AddNew\n    DataList(\"TextLine\") = strLine\n    DataList(\"NumberLine\") = strValue\n    DataList.Update\nLoop\n\nobjFile.Close\n\nSet objFile = objFSO.OpenTextFile(\"C:\\Scripts\\Test.txt\", ForWriting)\n\nDataList.Sort = \"NumberLine ASC\"\n\nDataList.MoveFirst\n\nDo Until DataList.EOF\n    objFile.WriteLine DataList.Fields.Item(\"TextLine\") \n    DataList.MoveNext\nLoop\n\nobjFile.Close<\/pre>\n
Before we go much further we should note that this is actually a slightly-more complicated script than KT originally asked for. Why did we give him more than he asked for? As most of 
you know, sorting data in VBScript is never much fun; that’s because VBScript has no built-in mechanism for sorting data. On top of that, even when you can<\/i> sort data you run into the 
problem of whether VBScript sees your data as being composed of numbers or letters. For example, suppose we have the following values:<\/p>\n
2 BBB\n1 AAA\n10 DDD\n3 CCC<\/pre>\n
If VBScript sees these values as being numbers, then it will sort the values like this:<\/p>\n
1 AAA\n2 BBB\n3 CCC\n10 DDD<\/pre>\n
However, if VBScript thinks it’s dealing with string values, then we’re going to get back a list like this one:<\/p>\n
1 AAA\n10 DDD\n2 BBB\n3 CCC<\/pre>\n
Yuck. <\/p>\n
In KT’s case this isn’t a problem; that’s because the numbers in his text file only go up to 9. As he noted, he needs to sort the lines in the file “based on 
the value of the first character in the line, which is always a number.” If you’re only sorting on the first character, well, there aren’t many single-digit numbers 
greater than 9.<\/p>\n
Well, OK, math fans: there aren’t many base-10<\/i> single-digit numbers greater than 9.<\/p>\n
We have a feeling, however, that many of you have text files that include line numbers greater <\/i>than 9; in that case, sorting on just the first digit of a number 
like 147 doesn’t do you a lot of good. Therefore, we came up with a script that’s a tiny bit more complicated, but way more flexible: it should work in any 
situation and with any numbers.<\/p>\n
But don’t worry; if do<\/i> you want a script that only works with values up to 9, well, we’ve tacked that on to the end of today’s column.<\/p>\n
As for the fancier and slightly-more complicated script, we start out by defining a bunch of constants. The first two – ForReading and ForWriting – are needed 
when we read from and write to the text file. The other four fill the following roles: <\/p>\n