How Can I Sort the Contents of a Text File?

ScriptingGuy1

Hey, Scripting Guy! I have a text file that contains a list of computer names. How can I sort that file alphabetically?

— LR

Hey, LR. If we wanted to take the easy way out we’d just tell you, “Sorry, you can’t do that.” And we could get away with that because none of Microsoft’s scripting technologies feature a way to open up and then sort a text file. But, hey, when have the Scripting Guys ever taken the easy way out?

Oh, right: we were hoping everyone had forgotten about those times. But this time we’re going to give you a workaround.

Although there’s no straightforward way to sort a text file, we can achieve the same net result by doing the following: 1) Use the FileSystemObject to read the file into memory; 2) Sort the file alphabetically in memory; 3) Replace the existing contents of the file with the sorted data we have in memory. A slightly roundabout way of doing things, but you’ll end up with a text file sorted alphabetically, which is all you really care about.

To carry out this task we’ve chosen to use a disconnected recordset. There are other ways we could do this (say, a bubble sort), but a disconnected recordset gives us more flexibility and is much easier to explain, especially for those of you who have some experience with database programming. We’ll give you a quick overview of disconnected recordsets here; if you want a more detailed explanation of how they work, you might want to check out the Scripting Week 2 webcast Things the Scripting Guys Never Told You. (As an added bonus, that webcast will also show you how you can use a bubble sort to sort single-field data.)

Before we begin, we assume you have a text file that looks like this, with one computer name per line:

red-ws-02
atl-ws-01
sf-ws-02
atl-ws-02
atl-ws-03
red-ws-02
sf-ws-01

How can we read in these computer names, sort them, and then write the sorted list back to the text file? Why, we can use a script that looks just like this one:

Const adVarChar = 200
Const MaxCharacters = 255
Const ForReading = 1
Const ForWriting = 2

Set DataList = CreateObject(“ADOR.Recordset”)
DataList.Fields.Append “ComputerName”, adVarChar, MaxCharacters
DataList.Open
Set objFSO = CreateObject(“Scripting.FileSystemObject”)
Set objFile = objFSO.OpenTextFile(“C:\Scripts\Computers.txt”, ForReading)
Do Until objFile.AtEndOfStream
    strLine = objFile.ReadLine
    DataList.AddNew
    DataList(“ComputerName”) = strLine
    DataList.Update
Loop
objFile.Close
DataList.Sort = “ComputerName”
DataList.MoveFirst
Do Until DataList.EOF
    strText = strText & DataList.Fields.Item(“ComputerName”) & vbCrLf
    DataList.MoveNext
Loop
Set objFile = objFSO.OpenTextFile(“C:\Scripts\Computers.txt”, ForWriting)
objFile.WriteLine strText
objFile.Close

We begin by defining a series of constants needed to create our disconnected recordset. (Think of a disconnected recordset as a database that exists only in memory, and is not tied to a physical database stored on a disk drive.) We then use this chunk of code to create a disconnected recordset that consists of a single field (ComputerName):

Set DataList = CreateObject(“ADOR.Recordset”)
DataList.Fields.Append “ComputerName”, adVarChar, MaxCharacters
DataList.Open

Next we use the FileSystemObject to open the text file C:\Scripts\Computers.txt. At this point we’re ready to start populating our recordset. What we’re going to do is read the text file line-by-line. Each time we read in a line, we’ll use the AddNew method to add a new record to the recordset. We’ll set the value of the ComputerName field to the line we just read in from the text file (remember, each line in the text file represents a single computer name), and then use the Update method to save the record to the recordset. We’ll continue doing that until we’ve read in each line of the text file, at which point we’ll close the file.

Yes, that sounds like a lot but, as you can see, all that requires only a few lines of code:

Do Until objFile.AtEndOfStream
    strLine = objFile.ReadLine
    DataList.AddNew
    DataList(“ComputerName”) = strLine
    DataList.Update
Loop

objFile.Close

Next we need to sort our recordset. This is one of the great benefits of using a disconnected recordset rather than a bubble sort or some other manual sorting algorithm. Sorting our recordset takes only a single line of code:

DataList.Sort = “ComputerName”

Now that we have a sorted recordset we need to write the data back to our text file. The easiest way to do this is to walk through the recordset, grab each record, and stash the entire recordset in a variable. That’s what we do here:

DataList.MoveFirst
Do Until DataList.EOF
    strText = strText & DataList.Fields.Item(“ComputerName”) & vbCrLf
    DataList.MoveNext
Loop

We’re simply taking a look at each record and then storing the value of the ComputerName field in the variable strText. Note how we do this: we set the value of strText to whatever happens to be in strText plus the value of the current ComputerName field plus a carriage return line feed (that’s what the vbCrLf constant represents). What we’ll end up with is, in memory, a variable named strText that holds this data:

atl-ws-01
atl-ws-02
atl-ws-03
red-ws-02
red-ws-02
sf-ws-01
sf-ws-02

Finally, we reopen our text file (for writing this time) and use the WriteLine method to replace the existing contents with the value of strText. And because the value of strText just happens to be our sorted list of computer names, we’ve managed to alphabetically sort the contents of C:\Scripts\Computers.txt.

ScriptingGuy1

Follow