Excel VBA - How to read csv file line by line but not the whole file

Question

This is my csv file content that I need to read:

"header", "header", "header", "header", "header", "header"
"value", "value", "", "value", "value", ""
"value", "value", "value", "value", "value", ""    

I found on internet the code to import file:

Sub ImportCSVFile(ByVal filePath As String, ByVal ImportToRow As Integer, ByVal StartColumn As Integer)

Dim line As String
Dim arrayOfElements
Dim element As Variant


Open filePath For Input As #1 ' Open file for input
    Do While Not EOF(1) ' Loop until end of file
        ImportToRow = ImportToRow + 1
        Line Input #1, line
        arrayOfElements = Split(line, ";") 'Split the line into the array.

        'Loop thorugh every element in the array and print to Excelfile
        For Each element In arrayOfElements
            Cells(ImportToRow, StartColumn).Value = element
            StartColumn = StartColumn + 1
        Next
    Loop
Close #1 ' Close file.
End Sub

For some reason that I haven't figured out, that code doesn't read line by line but the whole file at this line

    Line Input #1, line

Can anybody explain why it doesn't work?

Solution

Just fyr, what i do to correct for the Lf terminator into CrLf terminator

Private Sub Correct_Lf_to_CrLf(ByVal FilePath As String)
    Dim DataLine As String
    Open Environ("TEMP") & "\temp.txt" For Output As #1
        Open FilePath For Input As #2
            While Not EOF(2)
                Line Input #2, DataLine
                Print #1, Replace(DataLine, vbLf, vbNewLine)
            Wend
        Close #2
    Close #1


    'NOTE: Your original file will be replaced with the new file 
    '      where Lf are replaced with CrLf, amend where it is appropriate.
    FileSystem.FileCopy Environ("TEMP") & "\temp.txt", FilePath
End Sub

FYI: vbNewLine is vbCrLf

Comments

[smartobelix]

Most likely this is Unix file with LF only (CHR(10) in vba world). Line input expects CHR(13) or sequence CHR(13) + CHR(10) as line separator.

[Rosetta]

just correct the LineFeed (vbLF) into vbCrLf

[elkun49]

@Rosetta I'm a newbie in VBA. Could you please show me the line of code exactly where I have to change?

[jsotola]

since the file is one long line as far as line input is concerned, you need to split the text into lines arrayOfLines = Split(line, vblf)

[jsotola]

you could use a text editor like notepad++ to convert line endings for LF to CRLF, then use the macro to process the file