vbscript to read csv into array

Question

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:

I want it to skip the first line of the csv file which is a header that will never change.
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

And what is your problem/question?

Ekkehard.Horner
– Ekkehard.Horner

2014-02-27 15:34:43 +00:00
Commented Feb 27, 2014 at 15:34 — Ekkehard.Horner
– Ekkehard.Horner, Commented Feb 27, 2014 at 15:34
Sorry about that. I edited to include my issue.

user3352792
– user3352792

2014-02-27 15:50:23 +00:00
Commented Feb 27, 2014 at 15:50 — user3352792
– user3352792, Commented Feb 27, 2014 at 15:50

Ekkehard.Horner · Accepted Answer · 2014-02-27 17:25:12Z

3

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

edited Feb 27, 2014 at 17:25

answered Feb 27, 2014 at 15:59

Ekkehard.Horner

38.8k2 gold badges50 silver badges101 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ekkehard.Horner Over a year ago

To explain the formatting of the Evidence section: That is an unchanged copy of a REPL session.

Collectives™ on Stack Overflow

vbscript to read csv into array

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related