I need to work with some large csv files. Some of these files have columns exceeding several hundred and rows exceed several thousand. I am moving these needs off Excel VBA and onto XOJO. At this time Excel VBA is faster so I need to make some improvements
Once I load these files I will make modifications and then create another csv file and also create a MySQL CREATE/UPDATE capability.
But first improving the speed of bringing in csv file so I can then perform other work
I am using the following code to separate and placed data into a 2D string array
[code] tis = TextInputStream.Open(f) ’ read file into grid
while not tis.EOF ’ while not end-of-file
ListBox2.AddRow “” ’ add row to grid
s=tis.ReadLine ’ read line from file
fields=Split(s,",") ’ put items in fields() array
currentRow = currentRow + 1 ’ increment 2D array row pointer
Redim my_2d_array1(currentRow, Ubound(fields)) for i=0 to ListBox2.ColumnCount-1 ' copy to grid ListBox2.Cell(ListBox2.ListCount-1,i)=Trim(fields(i)) ' Fill listbox to limit of columns my_2d_array1(currentRow, i) = fields(i) ' Fill 2D Array second element next
That is painfully slow with a file that has more than 400 columns and 300 rows. It is excruciatingly slow when I grab a file that is thousands of lines long. and hundreds of more columns long.
This construct works quickly
However it is only good for a 1D array
So I tried to do something like this
my_2d_array1(currentRow, i) = Split(s,",")
However I could not find a way to do this on a full row due to the need for the second element and there is no ADD or APPEND for a multi dimensional array
So is the 2D array the best way to bring in large files?
Is there a way to speed up multi dimensional arrays input
Or what so I can speed this up and leave Excel VBA in the dust.