在 VBA 中逐行读取大文件的超快方法是什么? [英] What is a superfast way to read large files line-by-line in VBA?

问题描述

我相信我已经想出了一种非常有效的方法来逐行读取非常非常大的文件.如果您知道更好/更快的方法或看到改进的空间，请告诉我.我正在努力提高编码能力，因此您的任何建议都会很好.希望这也是其他人可能会觉得有用的东西.

I believe I have come up with a very efficient way to read very, very large files line-by-line. Please tell me if you know of a better/faster way or see room for improvement. I am trying to get better at coding, so any sort of advice you have would be nice. Hopefully this is something that other people might find useful, too.

它似乎比我的测试中使用 Line Input 快 8 倍.

It appears to be something like 8 times faster than using Line Input from my tests.

'This function reads a file into a string.                        '
'I found this in the book Programming Excel with VBA and .NET.    '
Public Function QuickRead(FName As String) As String
    Dim I As Integer
    Dim res As String
    Dim l As Long

    I = FreeFile
    l = FileLen(FName)
    res = Space(l)
    Open FName For Binary Access Read As #I
    Get #I, , res
    Close I
    QuickRead = res
End Function

'This function works like the Line Input statement'
Public Sub QRLineInput( _
    ByRef strFileData As String, _
    ByRef lngFilePosition As Long, _
    ByRef strOutputString, _
    ByRef blnEOF As Boolean _
    )
    On Error GoTo LastLine
    strOutputString = Mid$(strFileData, lngFilePosition, _
        InStr(lngFilePosition, strFileData, vbNewLine) - lngFilePosition)
    lngFilePosition = InStr(lngFilePosition, strFileData, vbNewLine) + 2
    Exit Sub
LastLine:
    blnEOF = True
End Sub

Sub Test()
    Dim strFilePathName As String: strFilePathName = "C:FldFile.txt"
    Dim strFile As String
    Dim lngPos As Long
    Dim blnEOF As Boolean
    Dim strFileLine As String

    strFile = QuickRead(strFilePathName) & vbNewLine
    lngPos = 1

    Do Until blnEOF
        Call QRLineInput(strFile, lngPos, strFileLine, blnEOF)
    Loop
End Sub

感谢您的建议！

推荐答案

我的两分钱……

不久前我需要使用 VBA 读取大文件并注意到这个问题.我测试了从文件中读取数据的三种方法，以比较其针对各种文件大小和行长的速度和可靠性.方法是:

Not long ago I needed reading large files using VBA and noticed this question. I tested the three approaches to read data from a file to compare its speed and reliability for a wide range of file sizes and line lengths. The approaches are:

1. 行输入 VBA 语句
2. 使用文件系统对象 (FSO)
3. 对整个文件使用 Get VBA 语句，然后按照此处的帖子所述解析读取的字符串

1. Line Input VBA statement
2. Using the File System Object (FSO)
3. Using Get VBA statement for the whole file and then parsing the string read as described in posts here

每个测试用例包含三个步骤:

Each test case consists of three steps:

1. 编写一个文本文件的测试用例设置，其中包含由已知字符模式填充的给定长度的给定行数.
2. 完整性测试.读取每个文件行并验证其长度和内容.
3. 文件读取速度测试.重复读取文件的每一行 10 次.

如您所见，第 3 步验证真实的文件读取速度(如问题中所述)，而第 2 步验证文件读取完整性，因此在需要进行字符串解析时模拟实际情况.

As you can notice, Step #3 verifies the true file read speed (as asked in the question) while Step #2 verifies the file read integrity and therefore simulates real conditions when string parsing is needed.

下图显示了文件读取速度测试的测试结果.所有测试的文件大小为 64M 字节，测试的行长不同，从 2 字节(不包括 CRLF)到 8M 字节不等.

The following chart shows the test results for the File read speed test. The file size is 64M bytes for all tests, and the tests differ in line length that varies from 2 bytes (not including CRLF) to 8M bytes.

结论:

1. 这三种方法对于行长正常和异常的大文件都是可靠的(请对比格雷姆霍华德的回答)
2. 对于正常的行长度，所有三种方法产生的文件读取速度几乎相同
3. 超快方式"(方法 3)适用于极长的线路，而其他两种方式则不然.
4. 所有这些都适用于不同的办公室、不同的 PC，适用于 VBA 和 VB6

这篇关于在 VBA 中逐行读取大文件的超快方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！