Read a CSV in a 2D array 1

[befortin]

Hi,

I'm looking for a way to read a csv file and copy the data in the csv file in a 2D array.


This is what my csv file looks like :
GroupName,DescriptionName,OwnerName

My array is defined the following way :
Dim groups(1000,2)

I know that this array will be big enough, and I don't want to have to use all the redim and other stuff, since I'm pretty new to vbscript and I only want to write some very easy to understand scripts. When I will use the data in the groups array, I will use a "if isEmpty" to make sure I'm not at the end of the array.

I'm really looking for something very easy to understand, without all the fancy stuff (validate if file exists, it it's empty, or whatever else).

Can anyone help me with this??

 

[PHV]

Why not simply automate Excel ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[befortin]

Hi,

This script will run on a server, and will be scheduled to run each day. The server doesn't have Excel installed on it. And also it uses the data in the array to query the domain controller and give some output.

 

[PHV]

So, what have you tried so far ?

http://msdn.microsoft.com/library/en-us/script56/html/af4423b2-4ee8-41d6-a704-49926cd4d2e8.asp

http://msdn.microsoft.com/library/en-us/script56/html/fb2bbb28-85bc-42fc-85fb-ccc7da8abe8c.asp

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[befortin]

Hi,

I know about the Split function. However, I don't understand how it would work, since sometimes it has to use the comma as a separator (to put data in array(0,0), array(0,1) and array(0,2)), and sometimes it would have to use the new line as a separator (to go into array(1,0), array(2,0), etc.)

 

[Sheco]

In the CSV file, fields are separated by commas and records are separated by new lines (vbCrLf)

So how about read in a line, split, read next line, split, and so on?

 

[xwb]

1) Create a text dsn (csv.dsn)

[ODBC]
DRIVER=Microsoft Text Driver (*.txt; *.csv)
UID=admin
UserCommitSync=Yes
Threads=3
SafeTransactions=0
PageTimeout=5
MaxScanRows=25
MaxBufferSize=512
ImplicitCommitSync=Yes
FIL=text
Extensions=txt,csv,tab,asc
DriverId=27

2) Add a header to your csv file
Say the header is in header.csv and body is in body.csv

copy header.csv+body.csv whole.csv

The header will contain
GroupName,DescriptionName,OwnerName

Alternatively, you can write a bit of code to do it

3) Create a query class

const sTable = "whole.csv"

class CQuery
   ' Building Data Connection
   Dim mRs
   Dim mConn
   
   sub Class_Initialize
      Dim oFSO, oShell
      Dim sDSNFile, sDSN, sPath
   
      set oShell = CreateObject ("WScript.Shell")
      set oFSO = CreateObject ("Scripting.FileSystemObject")
      sPath = oFSO.GetAbsolutePathname (".") & "\"

      ' Set up a File DSN
      sDSNFile = "CSV.dsn"
      sDSN = "FileDSN=" & sPath & sDSNFile & _
          ";DefaultDir=" & sPath & _
          ";DBQ=" & sPath & ";"
   
      'Set up a DSN-less connection
      set mConn = CreateObject("ADODB.Connection")
      mConn.Open sDSN
      
      set oFSO = nothing
   end sub
   
   sub Class_Terminate
      'Close our recordset and connection
      mRs.close
      set mRs = nothing
      mConn.close
      set mConn = nothing
   end sub

   function NumRecs
      dim sqlQuery, disp
      ' Find out how many recs for the max parameter
      sqlQuery = "select count(*) as numrecs from " & sTable
      set mRs = mConn.execute (sqlQuery)
   
      NumRecs = mRs("numrecs")
   end function
   
   sub Retrieve (ByRef groups, ByRef recs)
      dim sqlQuery
   
      ' form the query
      sqlQuery = "select * FROM " & sTable
      set mRs = mConn.execute(sqlQuery)
   
      ' Extract
      r = 0
      Do While Not mRs.EOF
         groups(r,0) = mRs("GroupName")
         groups(r,1) = mRs("DescriptionName")
         groups(r,2) = mRs("OwnerName)
         r = r + 1
	 mRs.MoveNext
      Loop
      recs = r
   end function

end class

4) Run the query

dim qry
set qry = new CQuery
dim groups(1000,2), groupCount
qry.Retrieve groups, groupCount
set qry = nothing

 

[xwb]

Of course you could write your own csv parser but why bother when the system will handle it for you. With a text based dsn, you don't need any additional software. This technique even works on Windows 98.

 

[befortin]

Hi xwb,

Thank you very much for your help!