Array Input Filtering 2

[Brycspain]

I'm reading a CSV file into an array however, some of the values in the csv file can be either NULL or - values and they can show up randomly in any one of the positions. I don't want these values populated within the properties of my user's accounts. Is there a way to filter these values out during the process of reading them into the array itself besides using if then statements? Any help would be greatly appreciated...thank you in advance.

sInputFile = "c:\Userupdatetest.csv" 
Set oFile = oFSO.OpenTextFile(sInputFile, ForReading )
Do While Not oFile.AtEndOfStream
aStr = split(oFile.ReadLine,",")    
sLogon = aStr(0) 
sCompany = aStr(1)    
sLocation = aStr(2)
sTeam = aStr(3)
sDepartment = aStr(4)
sTitle = aStr(5)
do something
Loop

 

[Brycspain]

Think I'm on the right track here. I found the filter function and have been playing around with it some. The problem I'm having now is, when there is a NULL value in the sLogon portion of the array, it fills in with the sCompany and on down the line. I then receive an error message saying subscript out of range: '[number: 5]'

So my input values are:
sLogon = NULL
sCompany = company
sLocation = Location
sTeam = Team
sDepartment = department
sTitle = Title

I need sLogon to be blank, then sCompany equal to sCompany, etc. If sDepartment is equal to NULL, I need this to be blank and sTitle to equal sTitle.

Make sense?

Thank you in advance.

Const ForReading = 1
Set oFSO = CreateObject("Scripting.FileSystemObject")
sInputFile = "c:\Userupdatetest.csv" 
Set oFile = oFSO.OpenTextFile(sInputFile, ForReading )
Do While Not oFile.AtEndOfStream
aStr = split(oFile.ReadLine,",")    
sLogon = aStr(0) 
sCompany = aStr(1)    
sLocation = aStr(2)
sTeam = aStr(3)
sDepartment = aStr(4)
sTitle = aStr(5)
bStr=Filter(aStr,"NULL",false)

WScript.Echo bStr(0) & "        ---------------sLogon"
WScript.Echo bStr(1)& "         ---------------sCompany"
WScript.Echo bStr(2) & "           ----------------sLocation"
WScript.Echo bStr(3) & "           ----------------sTeam"
WScript.Echo bStr(4) & "           --------------sDepartment"
WScript.Echo bStr(5) & "          ----------------sTitle" & vbCrLf
WScript.Sleep 1000

Loop

 

[PHV]

Replace this:
aStr = split(oFile.ReadLine,",")
with this:
aStr = Split(Replace(oFile.ReadLine,"NULL",""),",")

And get rid of the Filter function call.

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

 

[Brycspain]

Thanks PHV, that worked perfectly. Is it possible to remove comma strings after the 6th comma? For example, my last column (title)contains comma's and this screws with split function. I looked into doing this in excel however, I don't want to load excel on my batch server.

Thank you in advance.

 

[mrmovie]

aStr = Split(Replace(oFile.ReadLine,"NULL",""),",")
For i = 0 To 5 '?
strX = strX & "," & aStr(i)
Next
or
Redim Preserve aStr(5)?

just guessing

 

[PHV]

aStr = Split(Replace(oFile.ReadLine,"NULL",""),",",6)

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

 

[Brycspain]

Thanks PHV, is there a way to get rid of the quotes this places on the last array string?

Thanks in advance.

 

[Brycspain]

I tried this and I received an error message. Is there a way to go from 6 elements to 7 and back to 6 again? Will redimming the array work in this instance?

Const ForReading = 1
Set oFSO = CreateObject("Scripting.FileSystemObject")sInputFile = "c:\Userupdatetest.csv" 
Set oFile = oFSO.OpenTextFile(sInputFile, ForReading )
Do While Not oFile.AtEndOfStreama
aStr = Split(Replace(oFile.ReadLine,"NULL",""),",")    
sLogon = aStr(0) 
sCompany = aStr(1)    
sLocation = aStr(2)
sTeam = aStr(3)
sDepartment = aStr(4)
sTitle1 = aStr(5)
sTitle2 = aStr(6)
if sTitle2 = "" then
sTitle = sTitle1
Else
sTitle = sTitle1 & ", " & sTitle2
End If
[\code]