China DOS Union

Extracted from Microsoft Script Guys

How to Remove All Duplicate Lines from a Text File?
Q:
Hello, Scripting Guy! How do I remove all duplicate lines from a text file?

-- SW

A:
Hello, SW. You know, being a scripting guy means starting an endless search for the ultimate solution to a given problem. (Or at least that's what we tell our manager when he asks why we never seem to actually finish anything: "Boss, the endless search takes time!") That's why we're happy to see your question. Not too long ago we answered a similar question about removing duplicate names from a text file. The solution we came up with was simple and worked well; we just weren't sure it was the best solution. Now, thanks to your question, we can try to solve this problem again. Whether this solution is better/faster/easier than the one we provided before is up to you to decide.

First, let's assume you have a text file where each line represents a separate record. It might not seem likely, but maybe your file looks something like this:

This is one of the lines in the text file.
This is one of the lines in the text file.
This is another line in the text file.
This is one of the lines in the text file.
This is yet another line in the text file.
This is another line in the text file.
This is another line in the text file.
This is one of the lines in the text file.

You need a script that can remove all duplicate lines and produce output like the following:

This is one of the lines in the text file.
This is another line in the text file.
This is yet another line in the text file.

SW, you've come to the right place:

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H0001

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

strPathToTextFile = "C:\Scripts\"
strFile = "Test.txt"

objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strPathtoTextFile & ";" & _
"Extended Properties=""text;HDR=NO;FMT=Delimited"""

objRecordSet.Open "Select DISTINCT * FROM " & strFile, _
objConnection, adOpenStatic, adLockOptimistic, adCmdText

Do Until objRecordSet.EOF
Wscript.Echo objRecordSet.Fields.Item(0).Value
objRecordSet.MoveNext
Loop

We find this script interesting because we're using ActiveX Data Objects (ADO) and treating this text file as a database. We won't spend a lot of time going into detail about how to treat a text file as a database; if you want to learn more about that, our Scripting Clinic column goes into great detail on the subject. For now, just know that we're going to use the text file C:\Scripts\Test.txt, which we represent by assigning values to the variables strPathToTextFile and strFile:

strPathToTextFile = "C:\Scripts\"
strFile = "Test.txt"

So, how does this remove duplicate lines? Well, there's something called a Select DISTINCT database query; using Select DISTINCT you can select all the distinct (or unique) records from a table. Suppose you have a simple database with the following records:


Red
Red
Blue
Red

If you use a Select DISTINCT query, you get a recordset that includes only the unique records:

Red
Blue

No doubt you're thinking, "Wow! Returning unique records is the same as removing duplicate records." We'll admit it is – well, hold on: You're absolutely right. Your text file is structured like a database table, where each line in the text file represents a field in a record. If we run a Select DISTINCT query against this text file, we'll get only the unique lines. In fact, we'll get a recordset that looks like this:

This is one of the lines in the text file.
This is another line in the text file.
This is yet another line in the text file.

Which is exactly what we want to return. Nice job pointing that out to us!

Once we've retrieved the recordset, we use the following code to echo the unique lines to the screen:

Do Until objRecordset.EOF
Wscript.Echo objRecordset.Fields.Item(0).Value
objRecordset.MoveNext
Loop

We could also use the FileSystemObject to open the text file and simply replace the existing contents with only the unique lines; that would have the same effect as removing all duplicate lines from the text file. (It would be nice if we could do this with an Update query, but ADO is read-only when working with text files.)

So, is this the final word on removing duplicates from a text file, whether it's names or entire lines? Well, who knows: After all, the endless search takes time! (Actually, we've found it only takes about 2 to 3 days. Then we get bored and move on to something else.)

I'm not very familiar with ADO yet