导入大型CSV时Excel冻结
问题描述:
我有一个非常大的CSV文件,有60列和50k行。 (我不能告诉你,因为它是在企业内部网)导入大型CSV时Excel冻结
我写的VBA代码,这是否:
Columns("D:BF").Select
Selection.ClearContents
*Code to import csv file to D1*
LastRow = Cells(Rows.Count, "D").End(xlUp).Row
Range("A1:C1").Select
Selection.Copy
Range("A2:C" & lastrow).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
这是工作,但已停止工作。当我运行宏时,Excel会冻结。我需要按“Esc”,宏停止,然后调试器说PasteSpecial是错误的。 但它不是,如果我只运行一个宏来导入,然后按“Esc”,它会导入得很好,然后运行第二个宏来粘贴公式直到最后一行,它也运行得非常好。
工作簿是人工计算
答
逐行读取CSV文件一行到一个数组是10,000行通过100列。当数组已满时,我将它写入Excel,重新设置数组并继续,直到文件结束。
我最初尝试使用动态数组,但它会冻结Excel。使用静态数组实际上更有效。
您需要调整常数值。从你的图像看起来你的文件是分号分隔的。如果ColumnCount
只要大于或等于实际列数,那么确实如此。
Const CSVFileName = "C:\Users\best buy\Downloads\stackoverfow\Sample Data File\R58K x C60.csv"
Const Delimiter = ","
Const PageSize = 10000
Const ColumnCount = 100
导入100,000行和64列大约需要32秒。 CSV文件在磁盘上为69.5 MB。
Option Explicit
Sub ProcessFile()
Const CSVFileName = "C:\Users\best buy\Downloads\stackoverfow\Sample Data File\R58K x C60.csv"
Const Delimiter = ","
ActiveSheet.DisplayPageBreaks = False
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
Debug.Print Now
Dim Start: Start = Timer
Dim lastRow As Long
Dim arFormulas
arFormulas = Range("A1:C1").Formula
Columns("A:BF").ClearContents
ImportCSVFile CSVFileName, Delimiter
Debug.Print "Time to import CSV file in seconds:"; Timer - Start
Start = Timer
lastRow = Cells(Rows.Count, "D").End(xlUp).Row
Range("A1:C" & lastRow).Formula = arFormulas
Debug.Print "Time to add formulas in seconds:"; Timer - Start
Debug.Print "Column Count:"; Worksheets("Sheet1").UsedRange.Columns.Count
Debug.Print "Row Count:"; Worksheets("Sheet1").UsedRange.Rows.Count
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.AutoRecover.Enabled = True
End With
End Sub
Sub ImportCSVFile(FilePath As String, Delimiter As String)
Const PageSize = 10000
Const ColumnCount = 100
Dim line As String
Dim arData, arLine
Dim x As Long, y As Long, z As Long
ReDim arData(PageSize, ColumnCount)
z = 1
Open FilePath For Input As #1 ' Open file for input
Do While Not EOF(1) ' Loop until end of file
Line Input #1, line
arLine = Split(line, Delimiter)
y = 0
For y = 0 To UBound(arLine)
arData(x, y) = arLine(y)
Next
x = x + 1
If x = PageSize Or EOF(1) Then
Range("D" & z).Resize(x, y) = arData
z = z + x
ReDim arData(PageSize, ColumnCount)
x = 0
End If
Loop
Close #1
Erase arData
End Sub
答
使用粘贴后doevents
命令我希望工程
#felipe31你确定你的代码?!?!?你选择单元格(“D:BF”),并清除那些,那么LastRow将是D1! – Fabrizio
@Fabrizio OP在导入新数据之前清除旧数据。 – 2016-08-24 06:21:51
@ThomasInzina是对的 – felipe31