Excel VBA-如何逐行读取csv文件，而不是整个文件 [英] Excel VBA - How to read csv file line by line but not the whole file

问题描述

这是我需要阅读的csv文件内容:

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?

推荐答案

请稍等，我将Lf终止子改成CrLf终止子的方法

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

仅供参考: vbNewLine 是 vbCrLf

这篇关于Excel VBA-如何逐行读取csv文件，而不是整个文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！