时间戳

首页 > 解决方案 > 如何解析非常大的 Excel 文件 (>6 GB) 并在 VBScript 中解析？

excel - 如何解析非常大的 Excel 文件 (>6 GB) 并在 VBScript 中解析？

问题描述

我有一个非常大的 Excel 文件（>6 GB），我需要在 VBScript 中解析它。

Function LineCount(sFName As String) As Long
Const Bloc = 32 * 1024& '-- 32K bloc
Dim n       As Long
Dim sText   As String
Dim LfCount As Long

Dim t As Single:  t = Timer '-- simple timing

Open sFName For Input As #1
If LOF(1) = 0 Then Close #1: Exit Function
n = LOF(1) Mod Bloc
If n = 0 Then n = Bloc
LineCount = 1
sText = Input(n, #1)
Do
    '-- short code: --------------------------------
    'LineCount = LineCount + UBound(Split(sText, vbLf))
    '-----------------------------------------------
    '-- longer code: ~10% faster -------------------
    n = -1
    Do
        n = InStrB(n + 2, sText, vbLf)
        If n Then LineCount = LineCount + 1 Else Exit Do
    Loop
    '-----------------------------------------------
    If EOF(1) Then Exit Do
    sText = Input(Bloc, #1)
Loop
Close #1
'-- subtract blank line at the bottom of the file
If Right(sText, 1) = vbLf Then LineCount = LineCount - 1

Debug.Print LineCount, Timer - t
End Function

我需要能够读取非常大的 Excel 文件并从中创建 1,000,000 行 Excel 文件。有什么建议可以快速阅读而不会遇到没有内存的运行时错误吗？

标签： excelvbalarge-files

解决方案

您将打开大文件进行输入，一次读取一行，然后打开一系列其他文件进行输出，在打开下一个之前向每个文件写入 1M 行，依此类推。

缩小版：

Sub SplitTextFile()
    
    Dim fso As Object, t, n As Long, ln, t2 As Object, outNum As Long
    Set fso = CreateObject("scripting.filesystemobject")
    
    'create a dummy text file for testing
    Set t = fso.createtextfile("C:\Temp\dummy.txt")
    For n = 1 To 1000
        t.writeline "This is line " & n
    Next n
    t.Close

    Set t = fso.opentextfile("C:\Temp\dummy.txt")
    
    n = 0
    outNum = 0
    Do While Not t.atendofstream
        
        If n Mod 100 = 0 Then
            If Not t2 Is Nothing Then t2.Close
            outNum = outNum + 1
            Debug.Print "Writing file # " & outNum
            Set t2 = fso.createtextfile("C:\Temp\dummy_" & outNum & ".txt", 2)
        End If
        
        t2.writeline t.readline
        n = n + 1
    Loop
    
    t.Close
    t2.Close

End Sub

推荐阅读