Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Array Input Filtering 2

Status
Not open for further replies.

Brycspain

IS-IT--Management
Mar 9, 2006
150
US
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.

Code:
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
 
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.

Code:
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
 
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
 
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.
 
aStr = Split(Replace(oFile.ReadLine,"NULL",""),",")
For i = 0 To 5 '?
strX = strX & "," & aStr(i)
Next
or
Redim Preserve aStr(5)?

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

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Thanks PHV, is there a way to get rid of the quotes this places on the last array string?

Thanks in advance.
 
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?

Code:
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]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top