CODEWITHSUNDEEP
vbaexcelcsv
elkun49
elkun49

Reputation: 133

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

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?

Upvotes: 2

Views: 17358

Answers (1)

Rosetta
Rosetta

Reputation: 2725

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

Upvotes: 2

Related Questions