Load CSV with line breaks-CodePudding

I am loading this CSV to Excel with VBA. I am validating the CSV with CSV Lint and I get three errors with line breaks.

My code is:

 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

Is it possible to remove the line breaks without modifying the CSV file to a new one? I have tried many macros but nothing seems to work.

If I have to modify the CSV to a new one I have this code:

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

but it removes all the line breaks in the CSV file.

How can I search for the line breaks that affect my Excel?

CodePudding user response：

CSV specification RFC4180 states a requirement is:

MS-DOS-style lines that end with (CR/LF) characters (optional for the last line).

This is confirmed for your CSS Linter, in it's "How to write CSV" section.
(See also the "common errors" section.)

Line endings use CRLF (Windows line endings) and the column names and fields are separated by commas.

Your code appears to be removing the CRLF's.

Your sample CSV appears to use LF's (based on me copy/pasting from your link into Notepad and going "View → Show Symbol → Show All Characters".

Therefore, I think you need to replace your Replace line with:

s = Replace(s, vbLf, vbCrLf)

CodePudding user response：

Open the CSV as a workbook and copy the data into your active sheet. Any quoted line breaks will appear within cells.

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