excel - 将 CSV 导入 Excel

Question

我需要将一些 CSV 导入 Excel 电子表格，每个 CSV 的行/列号都不同。问题是有些值是长数字字符串，例如
341235387313289173719237217391，

Excel 会将这些值视为( double )数字，然后导致数据丢失。

我解决这个问题的方法是使用以下vba完成这项工作的函数:

Sub readCSV(f As TextStream, sh As Worksheet)
    i = 1
    Do
        l = Trim(f.ReadLine)
        If l = "" Then Exit Sub 'skip the last empty line(s)
        l = Mid(l, 2, Len(l) - 1)
        ss = Split(l, """,""")
        For j = LBound(ss) To UBound(ss) 'j starts from 0
            Dim a As Range
            With sh.Cells(i, j + 1)
                .NumberFormat = "@" 'Force to text format
                .Value = ss(j)
            End With
            DoEvents 'Avoid blocking the GUI
        Next j
        i = i + 1
    Loop Until f.AtEndOfStream
End Sub

问题在于性能。它比通过“数据”->“从文本”导入数据或直接打开 CSV 慢得多。

有什么办法可以更有效地做到这一点吗？

Answer 1

您可以一次格式化/写入每一行:

Sub readCSV(f As TextStream, sh As Worksheet)

     Dim i As Long
     Dim ss, l
     i = 1

    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With

    Do
        l = Trim(f.ReadLine)
        If l = "" Then Exit Sub 'skip the last empty line(s)
        l = Mid(l, 2, Len(l) - 1)
        ss = Split(l, """,""")

        With sh.Cells(i, 1).Resize(1, (UBound(ss) - LBound(ss)) + 1)
            If (i-1) Mod 100 = 0 Then .Resize(100).NumberFormat = "@"
            .Value = ss
        End With

        i = i + 1
    Loop Until f.AtEndOfStream

    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End With


End Sub

编辑:经过测试，真正的性能 killer 是将单元格格式设置为文本修改代码，以 100 行的 block 而不是每行进行设置。