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

问题描述

我相信我已经想出了一种非常有效的方法来逐行读取非常大的文件。请告诉我，如果你知道更好/更快的方式或看到改善的余地。我想在编码方面做得更好，所以任何建议你都会很好。希望这是其他人可能会觉得有用的东西。

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

 '该函数将文件读入字符串。 '
'我在使用VBA和.NET编程Excel一书中发现了这个问题。 '
 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 =空格（l）
打开二进制访问的FName读为#I 
获取#I，，res 
关闭I 
 QuickRead = res 
 End Function 
 
这个函数和Line Input语句一样工作
 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（lFilePosition，strFileData，vbNewLine）+ 2 
退出子
 LastLine：
 blnEOF = True 
 End Sub 
 
 Sub Test（）
 Dim strFilePathName As String：strFil ePathName =C：\Fld\File.txt
 Dim strFile As String 
 Dim lngPos As Long 
 Dim blnEOF As Boolean 
 Dim strFileLine As String 
 
 strFile = QuickRead（strFilePathName）& vbNewLine 
 lngPos = 1 
 
直到blnEOF 
调用QRLineInput（strFile，lngPos，strFileLine，blnEOF）
 Loop 
 End Sub 
  

感谢您的建议！

解决方案

您可以使用Scripting.FileSystemObject来做这件事。
从参考：

ReadLine方法允许脚本读取文本文件中的各行。要使用此方法，请打开文本文件，然后设置一个Do循环，直到AtEndOfStream属性为True。 （这仅仅意味着你已经到达了文件的末尾）。在Do循环中，调用ReadLine方法，将第一行的内容存储在变量中，然后执行一些操作。当脚本循环时，它会自动下拉一行并将文件的第二行读入变量。
这样会一直持续下去，直到每一行被读取为止（或者直到脚本特别退出循环）。

例如：

$ p $ Set objFSO = CreateObject（Scripting.FileSystemObject）
Set objFile = objFSO.OpenTextFile（C ：\FSO\ServerList.txt，1）
Do直到objFile.AtEndOfStream
strLine = objFile.ReadLine
MsgBox strLine
Loop
objFile.Close


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.

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:\Fld\File.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

Thanks for the advice!

解决方案

You can use Scripting.FileSystemObject to do that thing. From the Reference:

The ReadLine method allows a script to read individual lines in a text file. To use this method, open the text file, and then set up a Do Loop that continues until the AtEndOfStream property is True. (This simply means that you have reached the end of the file.) Within the Do Loop, call the ReadLine method, store the contents of the first line in a variable, and then perform some action. When the script loops around, it will automatically drop down a line and read the second line of the file into the variable. This will continue until each line has been read (or until the script specifically exits the loop).

And a quick example:

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("C:\FSO\ServerList.txt", 1)
Do Until objFile.AtEndOfStream
 strLine = objFile.ReadLine
 MsgBox strLine
Loop
objFile.Close

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