How Can I Sort the Contents of a Text File?

How Can I Sort the Contents of a Text File?

  • Comments 3
  • Likes
Hey, Scripting Guy! Question

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

-- LR

SpacerHey, Scripting Guy! AnswerScript Center

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.

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • can you please do this in C prog?

  • Hi there,

    I have a txt file that I would like to edit but instead of sorting its contents I would like to delete some of it from a certain point in the file.

    This is an exctract of the file. I woul like to be left with data similar to RSHPV21287 and so on, which is the first record from the file and delete all the rest. Just the request Nr colum interests me, but the file is all crumbled and difficult to read when opened in the notepad, not like it is presented here.

    HEADERF>>

    ÚÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄ

    ³HPVD2R on status 0                Printed on 03.12.10 by FDS   Page PaGe

    ÀÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄ

    Selection criteria: LID=RS, Period=HPV2, Nø=

    Request No.     Status    Site  Requestor       Reception date    Mat

    -------------------------------------------------------------------------------

    <<HEADERN>>

    ÚÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄ

    ³HPVD2R on status 0                Printed on 03.12.10 by FDS   Page PaGe

    ÀÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄ

    Request No.     Status    Site  Requestor       Reception date    Mat

    -------------------------------------------------------------------------------

    <<BODY>>

    RS HPV2 1287    0-----6   SHP   CSHR01          23.03.2010        HURI

    <<BREAK>>

    RS HPV2 1389    01----6   SHP   CSHR01          26.04.2010        HURI

    <<BREAK>>

    RS HPV2 1558    0-----6   SHP   SHPC1B          23.06.2010        HOTH

    <<BREAK>>

    RS HPV2 1559    0-----6   SHP   SHPC1B          23.06.2010        HOTH

    <<BREAK>>

    RS HPV2 1560    0-----6   SHP   SHPC1B          23.06.2010        HOTH

    <<BREAK>>

    RS HPV2 1699    01----6   SHP   CSHR01          22.09.2010        HURI

    <<BREAK>>

    RS HPV2 1700    01----6

  • I managed to update my file but instead of replacing it, is just adding the recordset to the old data. HELP