Sort text files by vbs

Q:

Hello, script expert! I have a text file that contains a list of computer names. How to sort the file in alphabetical order?

-- LR

A:

Hello, LR. If you want to be lazy, we will tell you: "Sorry, you cannot do this ." We can also get away from this because Microsoft's scripting technology does not have a way to sort files after opening text files. However, hi, when will the "script expert" Be Too lazy?

Oh, by the way, we have been a few times lazy, but I hope everyone will forget it. However, this time we will provide you with a solution.

Although there is no way to sort text files directly, we can achieve the same effect by performing the following operations: 1) Use FileSystemObject to read the file into the memory; 2) sort files alphabetically in the memory. 3) Replace existing files with sorted data in the memory. Although these operations are a bit difficult, you will finally get a text file sorted alphabetically, and this is the result you want.

To complete this task, we chose to use a disconnected record set. Other methods can achieve this purpose (for example, Bubble Sorting), but the disconnected record set provides more flexibility and is easier to explain, it is especially suitable for users who have experience in database programming. We will give you a brief introduction to the disconnected record set here. For details about how it works, you may need to access Scripting Week 2 to broadcast Things the Scripting Guys Never Told You (something the Scripting experts have Never Told You ). (This network broadcast will also show you how to sort individual file data using Bubble Sorting as an additional supplement .)

Before you begin, assume that you have a text file similar to the following, and each line of the file has a computer name:

Red-ws-02

Atl-ws-01

Sf-ws-02

Atl-ws-02

Atl-ws-03

Red-ws-02

Sf-ws-01

How do I read these computer names, sort them, and write the sorted list back to the text file? We can use a script similar to the following:

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

First, we define a series of constants required to create a disconnected record set. (The disconnected record set should be considered as a database that only exists in memory and is irrelevant to the physical database stored on the disk drive .) Then, we use the following code to create a disconnected record set consisting of a single field "ComputerName:

Set DataList = CreateObject ("ADOR. Recordset ")

DataList. Fields. Append "ComputerName", adVarChar, MaxCharacters

DataList. Open

Next, we use FileSystemObject to open the text file C: \ Scripts \ Computers.txt. At this point, we are ready to fill in the created record set. The operation we want to perform is to read the text file line by line. Every time a row is read, we will use the "AddNew" method to add a new record to the record set. We set the value of the ComputerName field to the row we just read in the text file (Remember, each row of the text file represents a computer name respectively ), then, use the "Update" method to save the record to the record set. Continue to execute this operation until we have read each row of the text file, and then we will close the file.

Yes, it sounds like a lot of work, but as you can see, all operations only need the following 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 the record set. This is one of the main advantages of using a disconnected record set without using Bubble sorting or other manual sorting algorithms. To sort records, you only need to execute one line of code:

DataList. Sort = "ComputerName"

Now we have a sorted record set, which needs to be used to write data back to a text file. The simplest way to achieve this is to traverse this record set, capture each record, and store the entire record set in a variable. This is the following operation:

DataList. MoveFirst

Do Until DataList. EOF

StrText = strText & DataList. Fields. Item ("ComputerName") & vbCrLf

DataList. MoveNext

Loop

Let's just take a look at each row of records and store the value of the ComputerName field in the variable strText. Note how we perform this operation: we set the value of strText to any value in strText plus the value of the current ComputerName field, add a line break (represented by the vbCrLf constant ). We will end this operation using a variable named strText in the memory, which contains the following data:

Atl-ws-01

Atl-ws-02

Atl-ws-03

Red-ws-02

Red-ws-02

Sf-ws-01

Sf-ws-02

Finally, re-open the text file (this time to write) and use the "WriteLine" method to replace the existing content with the strText value. Because the value of strText is the list of sorted computer names, we sorted the contents of C: \ Scripts \ Computers.txt alphabetically.