Creating a dynamic, two-dimensional array

[kokser]

I have been spending the last couple of days trying to create this script, but am having some difficulties getting it to work.

The scenario:
I have a program that exports a csv file with information about several customers. This program cannot split the information into several csv files for each customer. This is what I need my script to do.

Sample output from csv:
Domain,Computer,IP Address,Scheduled Scan Exclusion List (Directories),Connection Status,OPP,Update Agent,Architecture,Client Program,Smart Scan Agent Pattern,Virus Pattern,IntelliTrap Pattern,IntelliTrap Exception Pattern,Virus Scan Engine,Spyware Pattern,Spyware Active-monitoring Pattern,Spyware Scan Engine,Virus Cleanup
Customer1,PC01,192.168.1.1,34567,Online,Disabled,Disabled,x86,10.0,,7.251.00,0.133.00,0.551.00,9.120.1004,9.43,0.943.00,6.2.3015,1082,6.3.1015,2.8.1084,2.8.1080,1.171.00,1.252.00,1.184.00,1.185.00,08-06-2010 11:35:47,
Customer1,PC02,10.70.10.1,34567,Online,Disabled,Disabled,x86,10.0,,7.251.00,0.133.00,0.551.00,9.120.1004,9.43,0.943.00,6.2.3015,1082,6.3.1015,2.8.1084,2.8.1080,1.171.00,1.252.00,1.184.00,1.185.00,08-06-2010 11:35:47,
Customer2,PC01,192.168.1.1,34567,Online,Disabled,Disabled,x86,10.0,,7.251.00,0.133.00,0.551.00,9.120.1004,9.43,0.943.00,6.2.3015,1082,6.3.1015,2.8.1084,2.8.1080,1.171.00,1.252.00,1.184.00,1.185.00,08-06-2010 11:35:47,
Customer2,PC02,10.70.10.1,34567,Online,Disabled,Disabled,x86,10.0,,7.251.00,0.133.00,0.551.00,9.120.1004,9.43,0.943.00,6.2.3015,1082,6.3.1015,2.8.1084,2.8.1080,1.171.00,1.252.00,1.184.00,1.185.00,08-06-2010 11:35:47,

Now I need this script to split Customer 1 and 2 into 2 seperate csv files, and in this process remove several of the attributes.

My idea is to create a two-dimensional array, where I can choose what attributes I want in the new csv files (Domain, Computer, IP, Pattern).

It seems to me that this is a lot more complicated than I initially thought it would be.

Any suggestions, changes, or other ideas are appreciated.

 

[mrmovie]

hmm, the code posted by me works fine,,,for me.
header line added to each customer%.csv

 

[kokser]

That's quite odd. I will look further into this.

 

[kokser]

Doing a very short test, this seemed to work.

Set dicOverall = CreateObject("Scripting.Dictionary")
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFSO.OpenTextFile("c:\vbs\csv.csv", 1, False)

a = 1

Do Until objTextFile.AtEndOfStream
  aLine = objTextFile.ReadLine
if a < 2 then
    strFirstLine = aLine
end if
a = 3
    aArray = Split(aLine, ",")
    strRest = Right(aLine, Len(aLine) - InStr(aLine, ","))
    strCustomer = aArray(0)
    If dicOverall.Exists(strCustomer) Then
      dicOverall.Item(strCustomer).Add  dicOverall.Item(strCustomer).Count + 1, strRest
    Else
      Set dicTemp = CreateObject("Scripting.Dictionary")
      dicTemp.Add dicTemp.Count + 1, strRest
      dicOverall.Add strCustomer, dicTemp
      Set dicTemp = Nothing
    End If  
Loop
For Each aCustomer In dicOverall
   Set objTS = objFSO.OpenTextFile("c:\vbs\" & aCustomer & ".csv", 8, True)
   objTS.WriteLine strFirstLine
   For Each aItem In dicOverall.Item(aCustomer)
     objTS.WriteLine aCustomer & "," & dicOverall.Item(aCustomer).Item(aItem)
   Next
   objTS.Close
   Set objTS = Nothing
Next

objTextFile.Close
Set objTextFile = Nothing
Set objFSO = Nothing
Set dicOverall = Nothing

if I remove the if statements around strFirstLine, the first line will of course be the last line of the csv when the script is at the end of the stream. Seems logical.

 

[JoCoSupport]

I know this thread is a few months old, but for future readers, i have created two functions to create a dynamic array from a delimited file. These may be a bit clunky, but they work quickly for small files and can be easily cut and pasted into any code. Currently i have them in an external function_library.vbs that i load into an HTA.

Example of how to call these functions.

myTestCount = Fn_File_Count_Rows_And_Columns ("test.csv", ",")
myTestArray = Fn_File_Read_to_Array ("test.csv", CInt(myTestCount(0)-1), CInt(myTestCount(1)-1), ",")


Function Fn_File_Count_Rows_And_Columns(MyFile, MyDelimiter)
'Reads MyFile, splitting it with the delimter MyDelimiter, and writes the row and column
'count to and array. Array(0,0) is the row count, while Array (0,1) is the column count.
'Because this returns an array, be sure to call it to a variable, then read the array out
'of the variable. Even though arrays start at 0, this will return results starting with 1
'as returning a 0 would confuse people into thinking there were no rows int he file.
'----------------------------------------------------------------------------------------
Dim tTempArray(1)
On Error Resume Next
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFileCount = objFSO.OpenTextFile(MyFile, 1, False)
Do While Not objFileCount.AtEndOfStream
tRowCount = tRowCount + 1
tLineData = objFileCount.ReadLine
If tRowCount = 1 Then tColSplit = Split(tLineData,MyDelimiter)
If tRowCount = 1 Then tColCount = UBound(tColSplit) + 1
Loop
tTempArray(0) = tRowCount
tTempArray(1) = tColCount
objFileCount.Close
Set objFileCount = Nothing
Set objFSO = Nothing
Err.Clear
On Error Goto 0
Fn_File_Count_Rows_And_Columns = tTempArray
End Function

Function Fn_File_Read_to_Array(File_Name, x, y, MyDelimiter)
'Function to read a delimited file into a 2 dimentional array (x,y). To avoid errors, be
'sure you use the exact amount of rows and columns desired (starting at 0, not 1). For
'best results use the function Fn_File_Count_Rows_And_Columns to count the results, then
'feed those results -1 to this function. Also, be sure you call the function into a
'variable, or you you can not read the results out of the array. Example:
'MyArray = Fn_File_Read_to_Array ("mylist.csv", 14, 4, ",")
'This will read 15 rows and 5 columns into the array (starts at 0). Any delimiter will
'work, but there is no advanced checking, so all delimeters will be considered and split.
'----------------------------------------------------------------------------------------
ReDim tCurArray (CInt(x),CInt)
Dim tCurLineCnt: tCurLineCnt = 0
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFileContents = objFSO.OpenTextFile(File_Name, 1, False)
On Error Resume Next
Do While Not objFileContents.AtEndOfStream
tCurLineData = objFileContents.ReadLine
tCurLineSplit = Split (tCurLineData,",")
For v = 0 To y
If tCurLineCnt <= x Then tCurArray(tCurLineCnt,v) = tCurLineSplit(v)
'If tCurArray(tCurLineCnt,v) <> "" Then tCurEchoLine = tCurEchoLine & tCurArray(tCurLineCnt,v) & ", "
Next
If Right(Trim(tCurEchoLine),1) = "," Then tCurEchoLine = Left(tCurEchoLine,Len(tCurEchoLine)-2)
'tCurEcho = tCurEcho & vbCrLf & tCurEchoLine
'tCurEchoLine = ""
'wscript.echo tCurEcho
tCurLineCnt = tCurLineCnt + 1
Loop
Err.Clear
On Error Goto 0
objFileContents.Close
Set objFileContents = Nothing
Set objFSO = Nothing
Fn_File_Read_CSV_to_Array = tCurArray
End Function

 

[JoCoSupport]

Ok, looks like i posted to soon, i had just made some tweaks and didn't post the fully updated version. Replace the second function.


Function Fn_File_Read_to_Array(File_Name, x, y, MyDelimiter)
'Function to read a delimited file into a 2 dimentional array (x,y). To avoid errors, be
'sure you use the exact amount of rows and columns desired (starting at 0, not 1). For
'best results use the function Fn_File_Count_Rows_And_Columns to count the results, then
'feed those results -1 to this function. Also, be sure you call the function into a
'variable, or you you can not read the results out of the array. Example:
'MyArray = Fn_File_Read_to_Array ("mylist.csv", 14, 4, ",")
'This will read 15 rows and 5 columns into the array (starts at 0). Any delimiter will
'work, but there is no advanced checking, so all delimeters will be considered and split.
'----------------------------------------------------------------------------------------
ReDim tCurArray (CInt(x),CInt)
Dim tCurLineCnt: tCurLineCnt = 0
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFileContents = objFSO.OpenTextFile(File_Name, 1, False)
On Error Resume Next
For i = 0 To x
tCurLineData = objFileContents.ReadLine
tCurLineSplit = Split (tCurLineData,MyDelimiter)
For v = 0 To y
If i <= x Then tCurArray(i,v) = tCurLineSplit(v)
Next
Next
Err.Clear
On Error Goto 0
objFileContents.Close
Set objFileContents = Nothing
Set objFSO = Nothing
Fn_File_Read_to_Array = tCurArray
End Function