Import multiline CSV files into Excel internationally

Question

There is a CSV file that we would like to distribute to our customers; it contains multi-line records (i.e. records with newline characters). Depending on the language settings of the clients, the file may or may not be correctly imported into Excel. Usually we recommend using a file to import, but it seems that there is some error with multi-line entries, so they are "broken" into separate lines (oddly enough, this does not happen when the file is opened directly).

With some languages ​​(for example, in English), csv with a semicolon opens correctly, but not a file with a semicolon. In other languages ​​(for example, in German) csv with a semicolon can open directly, but not with commas.

Import does not help with multiline entries.

Example csv file (2 lines of csv):

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

Correct import (2 lines with multi-line recording):

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

Incorrect import (3 lines):

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

There is another way to intervene - select a column and click "Text to Columns" in the "Data" section. This separates the lines neatly based on the separator, but still does not bypass the newline.

Is it possible to import a csv file so that multi-line recordings are always recorded?

Answer 1

You will probably find that it opens in an open office. I did.

In this case, you can save it as an excell sheet and distribute both files to your customers.

Answer 2

, , , : . - ​​.

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