CODEWITHSUNDEEP
arrayscsvvbscript
user3352792
user3352792

Reputation: 31

vbscript to read csv into array

My Task: A vbscript that opens a .csv file reads data into an array, uses that data to perform functions and then returns the updated data to the same file. I believe I have everything else working except my function to read the data from the file.

My issue: I am receiving runtime errors when I try to use this code. with the script as-is I get an Error:Type mismatch. I try altering the code some and get different runtime errors.

A few notes on this code:

  1. I want it to skip the first line of the csv file which is a header that will never change.
  2. there will always be exactly 12 fields across. However the number of rows is dynamic and will chance with each input file.

My code to read the file:

Function BuildArrayFromCsv(filepath)    'Function to read file and load into an array

Const ForReading = 1    ' Declare constant for reading for more clarity

Set inputFile = FileSysObj.OpenTextFile(filepath, ForReading, True) ' Set inputFile as file to be read from

Dim row, column
Dim fields(11) '12 fields per line  
inputFile.ReadAll 'read to end of file  
ReDim MyArray(11,inputFile.Line-2) 'current line, minus one for header, and minus one for starting at zero  
inputFile.close     'close file so that MyArray can be filled with data starting at the top
Set inputFile = FileSysObj.OpenTextFile(filepath, ForReading, True) 'back at top  
inputFile.ReadLine 'skip header 


Do Until inputFile.AtEndOfStream  
    fields = Split(inputFile.Readline,",") 'store line in temp array  
    For column = 0 To 11 'iterate through the fields of the temp array  
        myArray(row,column) = fields(column) 'store each field in the 2D array with the given coordinates  
    Next
    row = row + 1  'next line 
Loop

inputFile.close
End Function

Upvotes: 3

Views: 30096

Answers (1)

Ekkehard.Horner
Ekkehard.Horner

Reputation: 38745

In VBScript Dim name(n) creates a fixed array of size n + 1; such a beast can't be overwrittem with/by a dynamic one (as Split() returns). Evidence

>> Dim fields(1)
>> fields = Split("a b")
>>
Error Number:       13
Error Description:  Type mismatch
>> Dim fields
>> fields = Split("a b")
>> WScript.Echo Join(fields)
>>
a b
>>

So replace 

Dim fields(11) '12 fields per line

with

Dim fields 'should be 12 fields per line, checking the result of Split() advisable

Upvotes: 3

Related Questions