Import multiline csv files into Excel internationally

Question

There is a .csv file we would like to distribute to our customers, it contains multiline entries (i.e. entries with newlines). Depending on the language settings of the customers, the file may or may not be correctly imported into Excel. Normally, we would suggest to use to Import the file, but there seems to be some bug with the multiline entries, so they will 'break apart' into separate lines (curiously, this does not happen when the file is opened directly).

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.

The import doesn't help with the multiline entries.

Sample csv file (2 csv lines):

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

Correct import (2 lines with a multiline entry):

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

Wrong import (3 lines):

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

There is another possibility to intervene - to select a column and press Text to Columns under Data. This splits the lines neatly based on a separator, but still doesn't get around the newlines.

Is it possible to import a csv file, so that multiline entries are always recognized?

Answer 1

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

Answer 2

You will probably find it opens fine in openoffice. I did.

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