Import data from CSV file into multidimensional array

[Marcus Tettmar]

Contributed By: Marcus Tettmar [email protected]
Submitted On: 07/08/03

Update: It is now possible to read CSV data into a native array using the DBQuery function added in v10. See:
http://www.mjtnet.com/blog/2009/03/12/r ... csv-files/

The code below is only necessary in earlier versions of Macro Scheduler.

Code: Select all

VBSTART

'An example file.  Save as c: est.csv or change file name and DefaultDir in function
'Remeber to remove comments at start of line if pasting into editor for example file
'header1,header2,header3,header4
'r1c1,r1c2,r1c3
'r2,c1",r2c2,r2c3
'r3c1,"r""3,c""2",r3c3
'r4c1,r4c2,"r,4"",""c3"

'Global Array for holding all data
Dim FieldArray

' ImportCSV - imports data from a CSV file and populates a mulitdimensional array
Sub ImportCSV

   Dim fieldnum
   Dim recnum

   set rs = createobject("ador.recordset")
   strConnect =  "Driver={Microsoft Text Driver (*.txt; *.csv)};" &  "DefaultDir=C:\;"
   Const adOpenStatic = 3
   rs.open "select * from test.csv", strConnect, adOpenStatic
   ReDim FieldArray(rs.recordcount+1,rs.fields.count)

   'set record and field counters.  I'm started at field 1 rather than 0.
   recnum = 0
   fieldnum = 1

   'add headers in row zero (optional, but handily means actual data starts in row 1)
   'if you omit this section data will start in row 0 unless you change recnum declaration above
   for each f in rs.fields
      if f.name <> "" then
        FieldArray(recnum,fieldnum) = f.name
      end if
      fieldnum = fieldnum + 1
   next
   recnum = recnum + 1
   'end header section

   'now add the data starting in row 1
   do until rs.eof
     fieldnum = 1
     for each f in rs.fields
       if f.value <> "" then
          FieldArray(recnum,fieldnum) = f.value
       end if
       fieldnum = fieldnum + 1
     next
     recnum = recnum + 1
     rs.movenext
   loop

End Sub

VBEND

'Get Data into Array
VBRun>ImportCSV

'First header name
VBEval>FieldArray(0,1),field
MessageModal>field

'Get the first field of the first record:
VBEval>FieldArray(1,1),field
MessageModal>field

'Get the second field of the third record:
VBEval>FieldArray(3,2),field
MessageModal>field

The Microsoft Text Driver attempts to determine the data types of the columns by scanning a selection of the fields. In some cases this can cause problems where the column data type is not consistent. To force the import to use a specific data type use a schema.ini file. See:

http://msdn.microsoft.com/library/defau ... i_file.asp

Example of schema.ini

[test.csv]
Format=CSVDelimited
Col1=Code Text
Col2=Part Text
Col3=Desc Text
Col4=Bin Text
Col6=Qty Text
Col8=Price Text[/b]