2020年8月7日星期五

VBA Read csv file in a fast way,using Array(r, c)

Read CSV File in a Fast way.  Don't set cell value directly, using Array(r, c) and then 

Set it in a line. Example.

CONST  MAX_ROW = 100000
CONST  MAX_COL = 10000

'dataArray(m,n) index from 0, 0
Dim dataArray(MAX_ROW, MAX_COL) As String

Dim r,c, colCount As Integer
Dim items() as String
Dim line as string 

Dim ts As TextStream
Set ts = fso.OpenTextFile(csvFilePath)

r = 1 
Do While Not ts.AtEndOfLine
   line = ts.ReadLine

   items = split(line,",")
   'using regex to split items when meet "item"
   'items = getMatchCollection(line, """([^""]*)""")

    for c = 0 to Ubound(items)
        dataArray(r, c) = items(c)
    next 

    r = r + 1
    DoEvents
    if colCount =0 then 
       colCount = c+1
    end if  

Loop 

'Set Array Values to Cells
destSHeet.Cells(2, 1).Resize(r, colCount) = dataArray

And on convert ,read Values from Sheet.cells to Array.

Dim cellArray as Variant
Dim rx, cx As Integer
rx = fromSheet.UsedRange.Rows.Count
cx = fromSheet.UsedRange.Columns.Count

cellArray = fromSheet.Range(fromSheet.cells(1,1),fromSheet.cells(rx,cx)).Value

When using cellArray 
for r = 1 to rx
    for c =1 to cx
        debug.print "fromSheet.cells(r, c)=" + 
fromSheet.cells(r, c)
        debug.print "cellArray(r, c)=" + cellArray(r, c)
    next
next 
###Attention: Can't use cellArray(r), will throw index exception.
      Must use cellArray(r, c) 

没有评论: