How to import into Excel a UTF-8 encoded CSV file containing line breaks within fields?

Question

Importing CSV files containing line breaks within fields is tricky and the usual import methods as described for Excel do not work. Only opening a file directly with "File -> Open..." menu command or by double-clicking it and making sure it is opened by Excel works for such files. With this technique the line breaks within fields do not get garbled up. However, there is no way to tell Excel about the encoding. If the CSV file is UTF-8 encoded, Excel ignores diacritical characters such as é etc. and cannot represent those characters correctly. It seems Excel assumes a system specific encoding. E.g. on a Mac it assumes Mac encoding. The import succeeds when one uses a technique to change the encoding from UTF-8 to the native encoding. If the mapping does not work, one needs to edit the file accordingly. BBEdit offers nice features to detect such non-mappable characters and may help to edit them and to obtain a CSV file that can be fully imported into Excel. If BBEdit is not available you might need to use a command line tool such as the Gnu iconv or similar.

Note, the usual recommendations to use "File -> Import" or "Data -> Get External Data -> Import Text File..." DO NOT WORK, as all these methods fail to properly import line breaks within fields and completely garble up multiline text within a single cell.

• P.b: Can you share a sample of data and what it currently does versus what you expect it to? Does activating wrap text "un-garble" your data containing line breaks?

Answer 1

Assuming a valid csv file (eg the fields containing linebreaks are within double quote marks), Data -> Get External Data -> Import Text File... works with a slight modification:

If you examine the first line of the automatically generated M-Code (in the formula bar of the PQ editor or in the Advanced Editor), you will likely see something like:

= Csv.Document(File.Contents(fullFilePathName.csv),[Delimiter=",", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None])

You just need to change the QuoteStyle:

= Csv.Document(File.Contents(fullFilePathName.csv"),[Delimiter=",", Columns=1, Encoding=1252, QuoteStyle=QuoteStyle.Csv])

You can also edit the encoding and the number of columns, if necessary.

• Andreas Fischlin: Yes, linebreaks are within double quotes. Using Data -> Get External Data -> Import Text File... gives the Wizard and that garbles the spreadsheet up as the linebreaks within double quotes are not honored. However, I managed to import the data correctly using the PQ editor via the Ribbon clicking on the icon labelled as "Get Data (Power Query)". I needed not to edit much. The only exception was the setting as of the delimiter to the comma. Everything else was automatically set correctly, including the UTF-8 encoding. Thanks for this hint.
• Andreas Fischlin: Unfortunately the import is still not correct as some fields contain also double quotes, which are not delimiters of the field. Those lead again to a garbled up sheet I first overlooked as this occurs only rarely. I need to generate a new CSV that contains double double quotes in these cases to get the import correct.
• Ron Rosenfeld: @AndreasFischlin That is true. As I wrote, this method will only work with a VALID csv file. A csv file where a field contains unescaped double quotes is not valid.
• Andreas Fischlin: Yes, of course. What do you mean exactly by unescaped? Do you mean 2 consecutive double quotes or a back slash before the double quote? Is there a standard method for this with CSV files? I am not sure which, perhaps you can help here too?
• Ron Rosenfeld: @AndreasFischlin Yes, I mean the doubled double quotes.