Vista - Eliminating duplicate lines - with a twist

Hello all.

I've used this script before to eliminate duplicate lines in a text
file, or in an array:

http://blogs.technet.com/heyscriptin...text-file.aspx

Now I need to eliminate duplicate lines in a comma separated file. The
contents of the CSV file are the following:

Software Program One,3.0,9/21/2009
Software Program Two,1.0,9/21/2009
Software Program Two,
Software Program Three,2.0,9/21/2009
Software Program Four,1.0,9/21/2009
Software Program Five,1.0,9/21/2009
Software Program Five,,9/21/2009
Software Program Six,1.0,9/21/2009

I'm not comparing the entire line. Of the 3 fields in the CSV, I want
to find lines where the FIRST FIELD is a duplicate. So these lines
would qualify as duplicates:

Software Program Two,1.0,9/21/2009
Software Program Two,

Software Program Five,1.0,9/21/2009
Software Program Five,,9/21/2009

And then comparing the duplicates, I want to eliminate the line that
has the 2nd and/or the 3rd field empty; and retain the line which has
all 3 fields populated. So my end result would be the CSV looking like
the following:

Software Program One,3.0,9/21/2009
Software Program Two,1.0,9/21/2009
Software Program Three,2.0,9/21/2009
Software Program Four,1.0,9/21/2009
Software Program Five,1.0,9/21/2009
Software Program Six,1.0,9/21/2009

I've come up with a script that will parse a CSV and display the
values in the 3 fields, but I can't figure out the logic to add that
will compare and eliminate the duplicates in the manner I need:

Const ForReading = 1, ForWriting = 2, ForAppending = 8
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objDictionary = CreateObject("Scripting.Dictionary")
Dim sFile, sQueryResults
sFile = "File.txt"
Set objFile = objFSO.OpenTextFile (sFile,1)
sQueryResults = objFile.ReadAll
objFile.Close
GetArrayValues(sQueryResults)
Function GetArrayValues(sList)
Arr = Split(sList, vbCrlf)
For i = 0 To UBound(Arr)
sLine = Arr(i)
ArrFields = Split(sLine, ",")
count = -1
sFieldInfo = ""
For j = 0 To UBound(ArrFields)
count = count + 1
sField = ArrFields(j)
IF sField ="" Then sField = "EMPTY FIELD"
sFieldNum = "Array Field " & count
sFieldInfo = sFieldInfo & sFieldNum & ":" & _
vbTab & sField & vbCrlf
Next
Wscript.Echo vbCrlf & "Array:" & vbTab & vbTab & _
sLine & vbCrlf & "Array UBound:" & vbTab & _
UBound(ArrFields) & vbCrlf & sFieldInfo
count = -1
Next
End Function ' GetArrayValues
Set objFSO = nothing
Set objDictionary = nothing

How can accomplish what I need to?

Any help would be greatly appreciated. Thanks!

- Dave

"Highlander" <tron9901@newsgroup> wrote in message
news:09c67f4d-efdd-482c-88fc-9b26c49df255@newsgroupIt might help to use the Jet OLEDB driver to read the csv file. I would add
a header line so you can sort the fields by name. This will arrange all
lines with the same value for the first field together. Because blanks are
sorted before other values, lines with blanks are always first. After the
lines are sorted, you want to output the last line for each unique value in
the first field. There may be a better way to do this using SQL, but I could
only think of the way below. This works for your example lines. I added the
following header line to the start of the csv file:

Field1,Field2,Field3

Then I used the code below:
==========
Option Explicit

Dim adoCSVConnection, adoCSVRecordSet, strPathToTextfile
Dim strCSVFile, k, strLine, strPrevious, strFlag

' Specify path to CSV file.
strPathToTextFile = "c:\Scripts\"

' Specify CSV file name.
strCSVFile = "Example.csv"

' Open connection to the CSV file.
Set adoCSVConnection = CreateObject("ADODB.Connection")
Set adoCSVRecordSet = CreateObject("ADODB.Recordset")

' Open CSV file with header line.
adoCSVConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strPathtoTextFile & ";" & _
"Extended Properties=""text;HDR=YES;FMT=Delimited"""

' Sort by all fields, so lines with blanks are first.
' If there is any line with no blanks, it will be the last
' for each unique value of the first field.
adoCSVRecordset.Open "SELECT * FROM " & strCSVFile _
& " ORDER BY Field1, Field2, Field3", adoCSVConnection

' Read the CSV file.
strPrevious = ""
Do Until adoCSVRecordset.EOF
' Display the last line for each unique value of the
' first field.
strFlag = adoCSVRecordset.Fields("Field1").Value
If (strPrevious <> "") And (strFlag <> strPrevious) Then
Wscript.Echo strLine
End If
strLine = adoCSVRecordset.Fields("Field1").Value _
& "," & adoCSVRecordset.Fields("Field2").Value _
& "," & adoCSVRecordset.Fields("Field3").Value
strPrevious = adoCSVRecordset.Fields("Field1").Value
adoCSVRecordset.MoveNext
Loop
Wscript.Echo strLine

' Clean up.
adoCSVRecordset.Close
adoCSVConnection.Close

--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.rlmueller.net
--

On Sep 21, 5:48*pm, "Richard Mueller [MVP]" <rlmueller-
nos...@newsgroup> wrote:Thanks Richard your script does the job! Except for one thing - it's
changing the value of the second field. Looks to be chopping it off
after a certain number of decimal points.

This is more clearly illustrated if you change the contents of the CSV
file to the following, and then run your script:

Field1,Field2,Field3
Software Program One,1.2.3.456789,12/31/2009
Software Program Two,2.1.234.56789,12/31/2009
Software Program Two,
Software Program Three,3.123.456.789,12/31/2009
Software Program Four,4.12.34.56.789,12/31/2009
Software Program Five,5.1234567.89,9,12/31/2009
Software Program Five,,12/31/2009
Software Program Six,6.123456.78.9,12/31/2009

- Dave

"HL0105" <tron9901@newsgroup> wrote in message
news:97f6384c-e7ac-44c2-9b43-64fd17f4aff4@newsgroup
On Sep 21, 5:48 pm, "Richard Mueller [MVP]" <rlmueller-
nos...@newsgroup> wrote:Thanks Richard your script does the job! Except for one thing - it's
changing the value of the second field. Looks to be chopping it off
after a certain number of decimal points.

This is more clearly illustrated if you change the contents of the CSV
file to the following, and then run your script:

Field1,Field2,Field3
Software Program One,1.2.3.456789,12/31/2009
Software Program Two,2.1.234.56789,12/31/2009
Software Program Two,
Software Program Three,3.123.456.789,12/31/2009
Software Program Four,4.12.34.56.789,12/31/2009
Software Program Five,5.1234567.89,9,12/31/2009
Software Program Five,,12/31/2009
Software Program Six,6.123456.78.9,12/31/2009

- Dave
-----------

The driver assumes the value is numeric. The solution is to create a
schema.ini file in the folder with the csv file. I used the following
schema.ini file:
===========
[Example.csv]
Format=CSVDelimited

Col1="Field1" Text
Col2="Field2" Text
Col3="Field3" Text
========
The first line is the name of the file in square brackets. This file told
the driver that all fields are text. There are many other things you can do
in such a file. I found this link:

http://msdn.microsoft.com/en-us/libr...53(VS.85).aspx

The driver knows to magically look for the schema.ini file and use it if the
specified file name matches.

--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.rlmueller.net
--

On Sep 22, 9:47*am, "Richard Mueller [MVP]" <rlmueller-
nos...@newsgroup> wrote:The schema.ini file solved the problem. Thanks Richard!

Two more questions.

1. I'm using this code within an HTA, running it from a mapped network
drive. So I keep getting the message "This website uses a data
provider that may be unsafe. If you trust the website, click OK,
otherwise click Cancel."
Is there a way to prevent this annoying message?

2. I actually have the data in an array; I'm writing the array to a
CSV file in order to use your code. Is it possible to use this ADO
code to parse the array directly, instead of parsing the CSV file?

Thanks again!

- Dave

"HL0105" <tron9901@newsgroup> wrote in message
news:17741e26-bc67-4bf6-a2f9-88307d4e5291@newsgroup
On Sep 22, 9:47 am, "Richard Mueller [MVP]" <rlmueller-
nos...@newsgroup> wrote:The schema.ini file solved the problem. Thanks Richard!

Two more questions.

1. I'm using this code within an HTA, running it from a mapped network
drive. So I keep getting the message "This website uses a data
provider that may be unsafe. If you trust the website, click OK,
otherwise click Cancel."
Is there a way to prevent this annoying message?

2. I actually have the data in an array; I'm writing the array to a
CSV file in order to use your code. Is it possible to use this ADO
code to parse the array directly, instead of parsing the CSV file?

Thanks again!

- Dave
--------------

I don't know of any way to convert an array into a recordset, except to
enumerate all elements and add them to the recordset. I wonder how the data
go into an array. It must have been read from an original source into the
array. Perhaps it should be read into a disconnected recordset instead of an
array. Or perhaps the data can be copied from the array into a disconnected
recordset. The disconnected recordset can be sorted, then enumerated as
before and output as desired.

In the example below the hard part is populating the array. It would be good
if that step could be skipped. Note below that the date is formated as a
date, the other two fields as strings. You can also format numbers.
===========
Option Explicit
Dim arrValues(7, 2), j, adoDataList, strLine, strFlag, strPrevious

Const adVarChar = 200
Const adDBTimeStamp = 135
Const MaxCharacters = 255

' Populate the array.
arrValues(0, 0) = "Software Program One"
arrValues(0, 1) = "1.2.3.456789"
arrValues(0, 2) = #12/31/2009#
arrValues(1, 0) = "Software Program Two"
arrValues(1, 1) = "2.1.234.56789"
arrValues(1, 2) = #12/31/2009#
arrValues(2, 0) = "Software Program Two"
arrValues(3, 0) = "Software Program Three"
arrValues(3, 1) = "3.123.456.789"
arrValues(3, 2) = #12/31/2009#
arrValues(4, 0) = "Software Program Four"
arrValues(4, 1) = "4.12.34.56.789"
arrValues(4, 2) = #12/31/2009#
arrValues(5, 0) = "Software Program Five"
arrValues(5, 1) = "5.1234567.89"
arrValues(5, 2) = #12/31/2009#
arrValues(6, 0) = "Software Program Five"
arrValues(6, 2) = #12/31/2009#
arrValues(7, 0) = "Software Program Six"
arrValues(7, 1) = "6.123456.78.9"
arrValues(7, 2) = #12/31/2009#

' Setup disconnected recordset. Define fields.
Set adoDataList = CreateObject("ADODB.Recordset")
adoDataList.Fields.Append "Field1", adVarChar, MaxCharacters
adoDataList.Fields.Append "Field2", adVarChar, MaxCharacters
adoDataList.Fields.Append "Field3", adDBTimeStamp, MaxCharacters
adoDataList.Open

' Read the array into the disconnected recordset.
For j = 0 To UBound(arrValues, 1)
adoDataList.AddNew
adoDataList("Field1") = arrValues(j, 0)
adoDataList("Field2") = arrValues(j, 1)
adoDataList("Field3") = arrValues(j, 2)
adoDataList.Update
Next

' Sort the disconnected recordset.
adoDataList.Sort = "Field1,Field2,Field3"

' Display sorted values.
adoDataList.MoveFirst
strPrevious = ""
Do Until adoDataList.EOF
strFlag = adoDataList.Fields("Field1").Value
' Output one line for each unique value of Field1.
If (strPrevious <> "") And (strPrevious <> strFlag) Then
Wscript.Echo strLine
End If
' Only the first field will be enclosed in quotes.
strLine = """" & strFlag & """," _
& adoDataList.Fields.Item("Field2").Value _
& "," & adoDataList.Fields.Item("Field3").Value
strPrevious = strFlag
adoDataList.MoveNext
Loop
Wscript.Echo strLine

adoDataList.Close
--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.rlmueller.net
--