excel - 加载带有换行符的 CSV

Question

我正在使用 VBA 将 [this CSV][1] 加载到 Excel。
我正在使用 [CSV Lint][2] 验证 CSV，并且出现三个换行错误。
我的代码是:

 Dim ws As Worksheet 
    Set ws = ActiveSheet
    
        Dim fileName As String, folder As String    
        folder = ThisWorkbook.Path & "\"
        fileName = Dir(ThisWorkbook.Path & "\*.csv")
    
        ActiveCell.Offset(1, 0).Range("A6").Select
    
    With ActiveSheet.QueryTables _
            .Add(Connection:="TEXT;" & folder & fileName , Destination:=ActiveCell)
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 850
            .TextFileStartRow = 1
            .TextFileParseType = xlDelimited
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = False
            .TextFileTabDelimiter = False
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = True
            .TextFileSpaceDelimiter = False
            .TextFileColumnDataTypes = Array(1, 1, 1, 1)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=True
            
        End With

是否可以在不将 CSV 文件修改为新文件的情况下删除换行符？
我尝试了很多宏，但似乎没有任何效果。
如果我必须将 CSV 修改为新的，我有以下代码:

Dim fso As Object, tsIn, tsOut
Dim s As String
Dim filePath As String
Dim newCSV As String
      
filePath = folder & fileName
newCSV = folder & "report.csv"

Set fso = CreateObject("Scripting.Filesystemobject")
Set tsIn = fso.OpenTextFile(filePath, 1)
Set tsOut = fso.CreateTextFile(newCSV, 1)



Do While tsIn.AtEndOfLine <> True
    s = tsIn.readline
    s = Replace(s, vbCrLf, "")
    tsOut.write s
Loop
tsIn.Close
tsOut.Close
    
Kill filePath

但它会删除 CSV 文件中的所有换行符。
如何搜索影响我的 Excel 的换行符？

Answer 1

将 CSV 作为工作簿打开并将数据复制到事件工作表中。任何引用的换行符都将出现在单元格内。

Option Explicit

Sub loadcsv()

    Dim ws As Worksheet, wbCSV As Workbook, n As Long
    Dim fileName As String, folder As String
    
    folder = ThisWorkbook.Path & "\"
    fileName = Dir(folder & "*.csv")
    
    Set ws = ActiveSheet
    Set wbCSV = Workbooks.Open(folder & fileName, False, True)
    With wbCSV.Sheets(1)
        n = .UsedRange.Rows.Count
        .UsedRange.Copy ws.Range("A7")
    End With
    wbCSV.Close False
    ws.Columns("A:T").AutoFit
    ws.Rows("7").Resize(n).AutoFit
    MsgBox n & " rows loaded from " & folder & fileName, vbInformation
   
End Sub