remove Double from csv file and create unique list 2

[DvZ73]

I have the following lines in a csv file

server1,server2,6,0,0,100
server1,server2,6,0,0,0
server1,server4,8,9,5,98
server1,server5,8,9,5,100
server1,server6,1,29,8,0
server1,server4,1,29,8,56
etc..

now i only need the unique data of column 2 where column 6 is the numner 0
in a seperate file.
so server 2 and server 4 are double but has a 0, and a 100 or a 56, as column 6 so that shouldn't be in the new file
however server6 should be noted in the new file, only column 2 needs te be in te new file
i'm puzzling all day with all kind of examples with arrays and sorting scripts found in google and combining them
to get what i need, my last resort is here, does anyone have an idea ?
Thx for the effort

 

[jkspeed]

This should work...

Dim arr(6,5) 'The first dimension is the number of lines in your input file and the second is the number of fields - 1.
InFile = "<PATH TO YOUR INPUT CSV FILE"
OutFile = "<PATH TO YOUR RESULT FILE>"

Set oFSO = CreateObject("Scripting.FileSystemObject")

Set InF = oFSO.OpenTextFile(InFile,1)
Set OutF = oFSO.OpenTextFile(OutFile,2,True)

i = 0
Do Until InF.AtEndOfStream 
	field = Split(InF.ReadLine,",")	
	arr(i,0) = field(0)
	arr(i,1) = field(1)
	arr(i,2) = field(2)
	arr(i,3) = field(3)
	arr(i,4) = field(4)
	arr(i,5) = field(5)
	i = i + 1
Loop

For n = 0 To UBound(arr)
	y = 0
	For x = 0 To UBound(arr)
		If n <> x Then
			If arr(n,1) = arr(x,1) Then
				y = y + 1
			End If
		End If 
	Next	
	If y = 0 And arr(n,5) = "0" Then
		OutF.Write arr(n,0) & "," & arr(n,1) & "," & arr(n,2) & "," & arr(n,3) & "," & arr(n,4) &_
			 "," & arr(n,5) & vbNewLine 
	End If
Next

 

[jkspeed]

Correction on the line 1's comment. The first dimension is the number of lines in your input file -1.

 

[DvZ73]

Great that works thx a million jkspeed
one issue still
the number of lines in the inputfile are variable, but i made the number of lines much higer then it has lines and that works.
but it fails on the first 3 lines and the last line of the inputfile, as those lines are text only and not devided by comma.
Is there a way to skip the first 3 and the last line

 

[PHV]

Is there a way to skip the first 3 and the last line
You may try this:

InFile = "InputFile.csv"
OutFile = "OutputFile.txt"
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set InF = oFSO.OpenTextFile(InFile, 1)
Set OutF = oFSO.OpenTextFile(OutFile, 2, True)
myAry = Split(InF.ReadAll, vbCrLf)
nbLines = UBound(myAry) - 4
InF.Close
ReDim arr(2, nbLines)
For i = 0 To nbLines
    Field = Split(myAry(i + 3), ",")
    arr(0, i) = Field(0)
    arr(1, i) = Field(1)
    arr(2, i) = Field(5)
Next
For N = 0 To UBound(arr, 2)
    y = 0
    For x = 0 To UBound(arr, 2)
        If N <> x Then
            If arr(1, N) = arr(1, x) Then
                y = y + 1
                Exit For
            End If
        End If
    Next
    If y = 0 And arr(2, N) = "0" Then
        OutF.WriteLine arr(1, N)
    End If
Next
OutF.Close

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

Even simpler, as you need only columns 2 and 6:

InFile = "InputFile.csv"
OutFile = "OutputFile.txt"
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set InF = oFSO.OpenTextFile(InFile, 1)
Set OutF = oFSO.OpenTextFile(OutFile, 2, True)
myAry = Split(InF.ReadAll, vbCrLf)
nbLines = UBound(myAry) - 4
InF.Close
ReDim arr(1, nbLines)
For i = 0 To nbLines
    Field = Split(myAry(i + 3), ",")
    arr(0, i) = Field(1)
    arr(1, i) = Field(5)
Next
For N = 0 To UBound(arr, 2)
    y = 0
    For x = 0 To UBound(arr, 2)
        If N <> x Then
            If arr(0, N) = arr(0, x) Then
                y = y + 1
                Exit For
            End If
        End If
    Next
    If y = 0 And arr(1, N) = "0" Then
        OutF.WriteLine arr(0, N)
    End If
Next
OutF.Close

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DvZ73]

Thx PHV,

Your script works if i delete the last line form the sourcefile
the last line out the source file is: "Report generated on Mar 25, 2014 9:09:53 AM"
and needs to be skipped aswell
is this possible in your current example
Thank you for your time

 

[jkspeed]

Update line 7 from -4 to -5.

nbLines = UBound(myAry) - 5

This accounts for the 3 lines at the beginning and the 2 at the end.