将Unix文件- LF作为行尾而不是CRLF问题的VBA脚本运行时库

Question

我正在编写一个非常简单的解析器来将文本文件读取到Excel中。Excel 2012中的文件超过了可用行数，因此我必须采用逐行的方法。

我测试过微软脚本运行库、TextStream对象和ReadLine方法。

它工作得很好，因为我有Windows文件的CRLF作为行尾，而它失败时，只有LF是标记行尾。

我在VBA之外看到了很多解决方案，VBA内部有没有可行的解决方案？

提前感谢

Answer 1

首先创建一个测试文件，该文件在c:\temp\lfs.txt中只使用LFs作为行尾。

这将把文件读入一个字符串：

Dim FileNum As Integer
Dim sBuf As String
Dim sTemp As String

 FileNum = FreeFile
 Open "c:\temp\lfs.txt" For Input As FileNum

 While Not EOF(FileNum)
     Line Input #FileNum, sBuf
     sTemp = sTemp & sBuf
 Wend

 Close FileNum

' Now, what do we have?  First the string itself:
Debug.Print sTemp
' Are there any CRs in it?  
Debug.Print Replace(sTemp, vbCr, "CR")
' LFs?
Debug.Print Replace(sTemp, vbLf, "LF")
' Replace the LFs with CRLFs:
Debug.Print Replace(sTemp, vbLf, vbCrLf)

Now if you write it back out to file, you should be able to use it

Answer 2

我使用了下面这个用LFs分隔的非常简单的文本文件。下面的vba代码适用于我。也许你可以在TextStream.ReadLine方法失败的地方发布你的文本文件数据的简短样本？

​

​

Private Const TEXT_FILE_PATH As String = "C:\Temp\VBA\textFileForUnix.txt"

Public Sub test()
    Dim targetSheet As Worksheet
    Set targetSheet = ThisWorkbook.ActiveSheet
    ReadText sheet:=targetSheet, textFilePath:=TEXT_FILE_PATH
End Sub

Private Sub ReadText(ByRef sheet As Worksheet, ByRef textFilePath As String)
    Dim scriptingFileSystem As Scripting.FileSystemObject
    Dim scriptingFile As Scripting.File
    Dim scriptingStream As Scripting.TextStream

    Set scriptingFileSystem = New Scripting.FileSystemObject
    Set scriptingFile = scriptingFileSystem.GetFile(textFilePath)

    Set scriptingStream = scriptingFile.OpenAsTextStream(ForReading)
    Dim r As Long
    Dim c As Byte
    With scriptingStream
        r = 1
        c = 1
        Do While Not .AtEndOfStream
          sheet.Cells(r, c).Value = .ReadLine
          r = r + 1
        Loop
       .Close
    End With

    Set scriptingFile = Nothing
    Set scriptingFileSystem = Nothing
End Sub

Answer 3

这行代码将解决我遇到的任何“不寻常”的行尾问题，这是我遇到过的正确的CRLF。特别是，它将修复单个LF或单个CR或LF-CR。

 result$ = Replace(Replace(Replace(Replace(Replace(txt$, vbLf & vbCr, vbLf), vbCrLf, vbLf), vbLf, vbCrLf), vbCr, vbCrLf), vbCrLf & vbLf, vbCrLf)