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)
没有评论:
发表评论