I am trying to import a CSV into Excel using a macro but some of the CSV data contains what appears to be CR Carriage Returns or LB Line Breaks. Instead of the CSV entry importing into the next one cell, it returns to the first column each time.

The CSV originates from an external application and in reviewing the file with Notepad, it appears they are carriage returns but I’m not certain.

If I use Excel’s ribbon to manually import the CSV; Data > Get Data from Text/CSV > Import File > Load, it appears correctly and the CRs or LBs are disregarded.

If I use a macro, the CRs or LBs are applied and it returns to the first column for import and jumbles the format.

Sub ImportCSV()

flName = Application.GetOpenFilename("CSV files (*.csv),*.csv, All Files (*.*),*.*")

If flName = "False" Then Exit Sub

flName = "TEXT;" & flName

With ActiveSheet.QueryTables.Add(Connection:=flName, Destination:=Range("A2"))
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .SavePassword = False
        .SaveData = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .AdjustColumnWidth = False 
        .RefreshStyle = xlOverwriteCells 
        .Refresh BackgroundQuery:=False 
End With
 
End Sub

UPDATE: As per the comments, manual import works correctly so I recorded a macro using this method. My problem still is that it’s for a specific file and I’m not familiar enough with this coding to modify it to work for a variable file name. If someone is able to assist, I’d greatly appreciate it. This is the recorded macro –

 Sub InputCSV()
'
' InputCSV Macro
'
' Keyboard Shortcut: Ctrl+t
'
    ActiveWorkbook.Queries.Add Name:="ExampleFileCSV", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Csv.Document(File.Contents(""D:ExampleFileCSV.csv""),[Delimiter="","", Columns=17, Encoding=65001, QuoteStyle=QuoteStyle.Csv])," & Chr(13) & "" & Chr(10) & "    #""Promoted Headers"" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Headers"",{{""Submission Date"", type date}, {""First Name"", type text}" & _
        ", {""Last Name"", type text}, {""Director/Tech ***Staff Only***"", type text}, {""Instrument/Colorguard"", type text}, {""Student Cell Phone Number"", type text}, {""Student Email "", type text}, {""Parent Email"", type text}, {""Monday, July 22nd (Whataburger)"", type text}, {""Tuesday, July 23 (Chick Fil A)"", type text}, {""Image Picker"", type text}, {""Thursday" & _
        ", July 25 (Marco's Pizza)"", type text}, {""Friday, July 26th (Smokey Mo's)"", type text}, {""Image Picker_1"", type text}, {""Saturday, July 27 (Marco's)"", type text}, {""Subtotal"", Int64.Type}, {""Total"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=ExampleFileCSV;Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [ExampleFileCSV]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "ExampleFileCSV"
        .Refresh BackgroundQuery:=False
    End With
    Application.CommandBars("Queries and Connections").Visible = False
End Sub