importing CSVs into Excel

Question

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

Excel 会将这些值视为（双精度）数字，然后导致数据丢失。

我解决它的方法是使用以下 vbafunction 来完成这项工作：

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

编辑：经过测试，真正的性能杀手是将单元格格式设置为文本修订代码，以 100 行而不是每行的块进行设置。

Answer 2

与其

在Excel中工作（按单元格或按行），不如使用Regexp来古怪地创建CSV文件的第二个版本，其中每个长度超过16个字符的alpanumeric字符串都更新为前面的'

然后只需在Excel中导入或打开整个新csv

在 CSV 文件上运行的示例代码StrIn示例的路径，"c:Temptest.csv"

Sub Main()
Dim objFSO As Object
Dim objRegex As Object
Dim objTF As Object
Dim objTF2 As Object
Dim tf As Object
Dim strIn As String
Dim strOut As String
Dim strFile As String
strIn = "c:Temptest.csv"
strOut = "c:Temptest2.csv"
Set objFSO = CreateObject("scripting.filesystemobject")
Set objTF = objFSO.getfile(strIn)
Set objRegex = CreateObject("vbscript.regexp")
Set tf = objTF.OpenAsTextStream(ForReading)
strFile = tf.ReadAll
With objRegex
.Pattern = "(w{16,})"
.Global = True
strFile = .Replace(strFile, "'" & "$1")
End With
Set objTF2 = objFSO.OpenTextFile(strOut, ForWriting, True)
objTF2.Write strFile
objTF2.Close
tf.Close
End Sub

Answer 3

尝试.Value = "'" & ss(j)

'强制值在 Excel 中显示为文本字符串。

此外，尝试在字符串中声明您的 ss 数组，这样它就不会在拆分后长时间存储数字。像这样：

Dim ss() as String = Split(l, """,""")