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.

 

[mikrom]

Some time ago I posted here in Ruby forum a solution for similar problem:

http://www.tek-tips.com/viewthread.cfm?qid=1555687&page=1

The example shows how to split one master table (CSV-file) into more smaller tables for every user.
I used for the task associative array, which is in Ruby called hash and in VBscript dictionary.

You can try the Ruby-example to see how it works and then program something similar in VBscript.

You can read about using dictionaries in VBscript here

http://windows-programming.suite101.com/article.cfm/how_to_use_the_vbscript_scripting_dictionary

or google for 'VBscript dictionary'

 

[mrmovie]

yeap, two dimension dynamic array = dictionary object

 

[kokser]

Yeah I've been looking at dictionary objects. I'll keep trying.

Thanks for the ruby link.

 

[mrmovie]

'perhaps a silly example but to show that the .Item of a dictionary can contain anything, e.g. another dictionary, an array, a instance of a custom class?

'i leave you to get the MyCSV collection / array, i think it serves your purpose?


Set dicOverall = CreateObject("Scripting.Dictionary")
For Each aLine In MyCSV
aArray = Split(aLine, ",")
strCustomer = aArray(0)
If dicOverall.Exists(strCustomer) Then
dicOverall.Item(strCustomer).Add aArray(1) & "," & aArray(2), "doesntmatter"
Else
Set dicTemp = CreateObject("Scripting.Dictionary")
dicTemp.Add aArray(1) & "," & aArray(2), "doesntmatter"
dicOverall.Add aArray(0), dicTemp
Set dicTemp = Nothing
End If
Next
'lets see
For Each aCustomer In dicOverall
Wscript.Echo aCustomer
For Each aItem In dicOverall.Item(aCustomer)
Wscript.Echo aItem & "=" & dicOverall.Item(aCustomer).Item(aItem)
Next
Next

 

[kokser]

I'm not really sure if my idea works, but I came u with something that seems a lot simpler.

Const ForReading = 1
Set objFSO = CreateObject("Scripting.FileSystem")
Set objTextFile = objFSO.OpenTextFile_
	("c:\vbs\csv.csv",ForReading)

Dim CountLines = 1
Dim FirstLine = ""

Do Until objTextFile.AtEndOfStream
strNextLine = objTextFile.ReadLine
arrLine = split(strNextLine,",")
	if CountLines = 1 Then
		strFirstLine = strNextLine
	Else
		
	End if
For i = 0 to UBound(ArrLine)
do stuff
	Next
Loop

Some of the file so you can understand my idea

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,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,
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,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,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,

The idea is:
- Read the csv file line by line.
- What is the first word before the first comma (ie. Customer1 or Customer2).
- Either create or open a csv file with a corresponding name.
- Add the first line of the original csv file (This is always the same) if a new file is created.
- Add the current line
- Loop until finished reading original csv

 

[mrmovie]

so, you didnt create a dynamic, two dimensional array then ;-) glad you found a solution

 

[kokser]

Yeah I figured this was easier than creating the whole dynamic array stuff.

I could still use some help with this new idea though!

 

[mrmovie]

you may want to have a look at the ADO stuff of consuming a CSV file and spitting out a recordset. this might be of interest and you could use the recordset commands / language stuff to get the information you want and only output certain fields etc

 

[kokser]

ADO records seem to be over the top for something this simple.

I have no idea if this would actually work, but when I run the script I get told I lack permissions to write to the file. What do?

Const ForReading = 1
Const ForAppending = 8
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFSO.OpenTextFile("c:\vbs\csv.csv",ForReading)

Dim CountLines, FirstLine, strCompany
CountLines = "1"
FirstLine = ""
strCompany = ""

Do Until objTextFile.AtEndOfStream
strNextLine = objTextFile.ReadLine
arrLine = split(strNextLine,",")
	if CountLines = "1" Then
		strFirstLine = strNextLine
	End if
For i = 0 to UBound(ArrLine)
if objFSO.FileExists("C:\vbs\" & ArrLine(0) & ".csv") Then
Set objTextFile = objFSO.OpenTextFile("c:\vbs\" & ArrLine(0) & ".csv",ForAppending,True)
objTextFile.WriteLine(ArrLine)
Else
Set objTextFile = objFSO.CreateTextFile("C:\vbs\" & ArrLine(0) & ".csv")
Set objTextFile = objFSO.OpenTextFile("c:\vbs\" & ArrLine(0) & ".csv",ForAppending,True)
objTextFile.WriteLine(ArrLine)
End if
Next
Loop

 

[mikrom]

Close the file

objTextFile.Close

 

[kokser]

Thanks.

Now I'm getting a type mismatch for objTextFile.WriteLine instead :S

 

[mrmovie]

Do Until objTextFile.AtEndOfStream
strNextLine = objTextFile.ReadLine
arrLine = split(strNextLine,",")
if CountLines = "1" Then
strFirstLine = strNextLine
End if
For i = 0 to UBound(ArrLine)
Set objTextFile = objFSO.OpenTextFile("c:\vbs\" & ArrLine(0) & ".csv",ForAppending,True)
objTextFile.WriteLine(Join(ArrLine))
objTextFile.Close
Set objTextFile = Nothing
Next
Loop

the OpenTextFile method will deal with creating a file if it doesnt already exist.
you need to Join() your ArrLine before writing it back out again.



I must say the opening of a text file at every line is pretty inefficient, but i guess that isnt your main concern?

the most efficient approach (i believe) would be my first post on the multi multi dictionary. you would read the csv file once and then only open/create each text file once.

i would be happy to give you a race ;-)

 

[kokser]

I really have no idea how I would do that. I only know very simple scripting, but I will give it a go on monday! Lot's of googling shall be done.

 

[mrmovie]

well, heres my quick brain dump (ive tested and it works)
speed as an aside, my advice is to get used to keeping things in memory, that way when you need to manipulate the data you can, with ease. (hence the suggest about ADO)




Set dicOverall = CreateObject("Scripting.Dictionary")

Const ForReading = 1
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFSO.OpenTextFile("c:\scripts\csv.csv", 1, False)

CountLines = 1
FirstLine = ""

Do Until objTextFile.AtEndOfStream
aLine = ""
strRest = ""
aLine = objTextFile.ReadLine
If CountLines = 1 Then 'boring and will take time, must be a better way
CountLines = 0
Else
aArray = Split(aLine, ",")
strRest = Right(aLine, Len(aLine) - InStr(aLine, ","))
'skip the If UBound(aArray) > 1 Then...for speed, lol
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
End If
Loop
'lets see
For Each aCustomer In dicOverall
Wscript.Echo aCustomer
'delete the old file?
Set objTS = objFSO.OpenTextFile("c:\scripts\" & aCustomer & ".csv", 8, True)
For Each aItem In dicOverall.Item(aCustomer)
Wscript.Echo aItem & "=" & dicOverall.Item(aCustomer).Item(aItem)
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

 

[mrmovie]

actually it creates the customer csv files without the headers, but i am sure you will forgive me

 

[kokser]

haha, man that was quick! It does work quite well, and as you commented, I need to figure out a way to add that first line to every file. I will look into it on monday, I'm off from work.

Thank you very much for the help!

 

[kokser]

There we go, fixed it.

Set dicOverall = CreateObject("Scripting.Dictionary")

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

Do Until objTextFile.AtEndOfStream
  aLine = ""
  strRest = ""
  aLine = objTextFile.ReadLine
    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 "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 Template,Virus Cleanup Engine,Manual Scan Exclusion List (Directories),Manual Scan Exclusion List (Files),Manual Scan Exclusion List (File Extensions),Real-time Scan Exclusion List (Directories),Real-time Scan Exclusion List (Files),Real-time Scan Exclusion List (File Extensions),Hotfix,"
   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

 

[mrmovie]

you might find this more future proof ;-)

Set dicOverall = CreateObject("Scripting.Dictionary")

Const ForReading = 1
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFSO.OpenTextFile("c:\scripts\csv.csv", 1, False)

CountLines = 1
FirstLine = ""
strFirstLine = ""

Do Until objTextFile.AtEndOfStream
aLine = ""
strRest = ""
aLine = objTextFile.ReadLine
If CountLines = 1 Then 'boring and will take time, must be a better way
CountLines = 0
strFirstLine = aLine
Else
aArray = Split(aLine, ",")
strRest = Right(aLine, Len(aLine) - InStr(aLine, ","))
'skip the If UBound(aArray) > 1 Then...for speed, lol
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
End If
Loop
'lets see
For Each aCustomer In dicOverall
Wscript.Echo aCustomer
'delete the old file?
Set objTS = objFSO.OpenTextFile("c:\scripts\" & aCustomer & ".csv", 8, True)
objTS.WriteLine strFirstLine
For Each aItem In dicOverall.Item(aCustomer)
Wscript.Echo aItem & "=" & dicOverall.Item(aCustomer).Item(aItem)
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

 

[kokser]

Just tested that. It somehow manages to get the last line of the csv file, instead of the first. Can't quite figure out why, it seems pretty simple.