Question

有一个CSV文件，我们希望分发给我们的客户，它包含多行条目（即包含换行符的条目）。根据客户的语言设置，该文件可能无法正确导入Excel。通常，我们会建议使用导入文件，但似乎有一些多行条目的错误，因此它们会分成单独的行（奇怪的是，当文件直接打开时，不会发生这种情况）。

With some languages (e.g. English), a csv with commas is opened correctly, but not a file with semicolons. With other languages (e.g. German), a csv with semicolons can be opened directly, but not a file with commas.

该导入无法处理多行条目。

样本 csv 文件（2 行 csv）：

A; B; "some
stuff"; C;
1; 2; "another line"; 3;

正确导入（两行里有多行输入）：

A B (some
stuff) C
1 2 (another line) 3

错误导入（三行）：

A; B; C; "some
stuff";D;
1; 2; "another line"; 3;

还有另一种干预的可能性-选择一列，在“数据”下按“文本到列”。这基于分隔符整齐地分割行，但仍无法解决换行符的问题。

是否可能导入一个 CSV 文件，使多行条目始终被识别？

Answer 1

你可能会发现它在OpenOffice中可以正常打开。我也是。

在这种情况下，您可以将其保存为Excel表，并将两个文件分发给您的客户。

Answer 2

Your question is not quite clear, but I think this is what you want: Note: For some reason the error catching part did not paste correctly. Sorry

Public Sub ReadCSV()
   Assumes all records:
     Have 5 fields
     The fields are delimited by a ;
     The Last field ends in a ;

Dim FileName As String
Dim ExcelRow As Long
Dim WorkSheetName As String
Dim FieldValue(5) As String
Dim FieldNo As Integer
Dim StringPosition As Integer
Dim LineFromFile As String
Dim CharFromLine As String

WorkSheetName = "Sheet1"
ExcelRow = 2     Assumes Row 1 is a heading that you should not delete

     Get the FileName and Open the file
If Application.FileDialog(msoFileDialogOpen).Show <> -1 Then Exit Sub
FileName = Application.FileDialog(msoFileDialogOpen).SelectedItems(1)

On Error GoTo OpenError
Open FileName For Input As #1

     Clear old data from the workbook
sRange = WorkSheetName + "!A2:E65536"
Range(sRange).ClearContents    Preserves formatting

     Read The file one line at a time
On Error GoTo ReadError
While Not EOF(1)
    Line Input #1, LineFromFile
    Debug.Print LineFromFile
    FieldNo = 1
    While FieldNo <= 5     5 is the number of fields in a record
        DoEvents
        FieldValue(FieldNo) = ""
        StringPosition = 1
             Examine each character from the line
        While StringPosition <= Len(LineFromFile)
            CharFromLine = Mid(LineFromFile, StringPosition, 1)
            If CharFromLine = ";" Then    ";" is the delimitor in the delimited file
                FieldNo = FieldNo + 1
                If FieldNo < 6 Then FieldValue(FieldNo) = ""
            Else
                FieldValue(FieldNo) = FieldValue(FieldNo) + CharFromLine
            End If
                 Test to see if we re at the end of a line, but haven t got all the fields yet
            If StringPosition = Len(LineFromFile) And FieldNo < 6 Then
                FieldValue(FieldNo) = FieldValue(FieldNo) + Chr(10)      Add a LineFeed to represent the new line
                Line Input #1, LineFromFile
                StringPosition = 0
            End If
            StringPosition = StringPosition + 1
        Wend
    Wend
         Put the Data in the Workbook
    For FieldNo = 1 To 5
        Sheets(WorkSheetName).Cells(ExcelRow, FieldNo).Value = FieldValue(FieldNo)
    Next FieldNo
    ExcelRow = ExcelRow + 1
Wend
Exit Sub
OpenError:
    Call MsgBox("Error Opening File:" + FileName, vbCritical, "File Open Error")
    Exit Sub
ReadError:
    Call MsgBox("Error Reading File:" + FileName, vbCritical, "File Read Error")
End Sub

友情链接