在VBA中读取大文本文件中的前几个字符 [英] Reading first few characters in large text files in VBA

问题描述

我正在尝试阅读excel中大（15MB）大文件中的前几个字符。现在，我使用典型的：

I'm trying to read the first few characters in large (>15MB) files in excel. Right now, I'm using the typical:

Set MyObject = New Scripting.FileSystemObject
Set mySource = MyObject.GetFolder(mySourcePath)
For Each myFile In mySource.Files
    With New Scripting.FileSystemObject
        With .OpenTextFile(myFile, ForReading)
            test_str = .ReadLine
            'Do things
        End With
    End With
Next

问题是有大文件，我（相信）你正在加载到内存中，只有阅读前几个字符有没有办法提取前6个字符？

The issue is with large files, I (believe) you're loading into memory the WHOLE thing only to read the first few characters. Is there a way to just extract the first 6 characters?

推荐答案

FileSystemObject 的替代方案是 ADO

An alternative to the FileSystemObject would be ADO

但是，您的陈述

我（相信）你正在加载到内存中，只有读取
的前几个字符。

I (believe) you're loading into memory the WHOLE thing only to read the first few characters.

是错误的。

我认为是误导您的事实是，您在阅读第一行后没有退出循环。通过逐行阅读您可以获得所需的内容，但您不会立即关闭该文件。这是一个很好的程序员练习，始终关闭您在代码中启动的任何对象。不要只是把它挂起来，不要依靠环境去杀死他们。

What I think is misleading you is the fact that you are not exiting the loop after you read the first line. You get what you want by reading line by line but you are not closing the file right away. It's a good programmers practice to always close any objects you initiate in your code. Don't just leave it hanging and don't rely on the environment to kill them.

考虑下面的代码作为你的替代并查看是否存在任何效率差异

Consider the below code as an alternative to yours and see if there is any efficiency difference

Option Explicit

' add references to Microsoft Scripting Runtime
' Tools >> References >> Microsoft Scripting Runtime
Sub Main()

    Dim fileName As String
    ' make sure to update your path
    fileName = "C:\Users\FoohBooh\Desktop\Project.txt"

    ReadTxtFile fileName


End Sub

Sub ReadTxtFile(fileName)

    Dim oFSO As New FileSystemObject
    Dim oFS As TextStream

    Set oFS = oFSO.OpenTextFile(fileName)

    Dim content As String
    content = oFS.ReadLine

    With Sheets(1).Range("A1")
        .ClearContents
        .NumberFormat = "@"
        .Value = content
    End With

    oFS.Close
    Set oFS = Nothing

End Sub

上面的代码将.txt文件的第一行读入第一个工作表的单元格A1。记住将fileName变量设置为完整路径。

The above code reads the first line of a .txt file into cell A1 of the first sheet. Remember to set a fileName variable to a full path.

这篇关于在VBA中读取大文本文件中的前几个字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！