Generating CSV file for Excel, how to have a newline inside a value

Nir_3509 0
Viewed 385080

I need to generate a file for Excel, some of the values in this file contain multiple lines.

there's also non-English text in there, so the file has to be Unicode.

The file I'm generating now looks like this: (in UTF8, with non English text mixed in and with a lot of lines)

Header1,Header2,Header3
Value1,Value2,"Value3 Line1
Value3 Line2"

Note the multi-line value is enclosed in double quotes, with a normal everyday newline in it.

According to what I found on the web this supposed to work, but it doesn't, at least not win Excel 2007 and UTF8 files, Excel treats the 3rd line as the second row of data not as the second line of the first data row.

This has to run on my customer's machines and I have no control over their version of Excel, so I need a solution that will work with Excel 2000 and later.

Thanks

EDIT: I "solved" my problem by having two CSV options, one for Excel (Unicode, tab separated, no newlines in fields) and one for the rest of the world (UTF8, standard CSV).

Not what I was looking for but at least it works (so far)

newline csv excel
19 Answers

You should have space characters at the start of fields ONLY where the space characters are part of the data. Excel will not strip off leading spaces. You will get unwanted spaces in your headings and data fields. Worse, the " that should be "protecting" that line-break in the third column will be ignored because it is not at the start of the field.

If you have non-ASCII characters (encoded in UTF-8) in the file, you should have a UTF-8 BOM (3 bytes, hex EF BB BF) at the start of the file. Otherwise Excel will interpret the data according to your locale's default encoding (e.g. cp1252) instead of utf-8, and your non-ASCII characters will be trashed.

Following comments apply to Excel 2003, 2007 and 2013; not tested on Excel 2000

If you open the file by double-clicking on its name in Windows Explorer, everything works OK.

If you open it from within Excel, the results vary:

  1. You have only ASCII characters in the file (and no BOM): works.
  2. You have non-ASCII characters (encoded in UTF-8) in the file, with a UTF-8 BOM at the start: it recognises that your data is encoded in UTF-8 but it ignores the csv extension and drops you into the Text Import not-a-Wizard, unfortunately with the result that you get the line-break problem.

Options include:

  1. Train the users not to open the files from within Excel :-(
  2. Consider writing an XLS file directly ... there are packages/libraries available for doing that in Python/Perl/PHP/.NET/etc
user_842700

In Excel 365 while importing the file:

Data -> From Text/CSV: From Text/CSV

-> Select File > Transform Data:

Select File -> Transform Data

In the Power Query Editor, right hand side at "Query Settings", under APPLIED STEPS, on "Source" row, click the "Settings icon"

Source settings icon

-> In the line break dropdown select Ignore line breaks inside quotes.

Ignore quoted line breaks

Then press OK -> File -> Close & Load

user_1318740

On a PC, ASCII character #10 is what you want to place a newline within a value.

Once you get it into Excel, however, you need to make sure word wrap is turned on for the multi-line cells or the newline will appear as a square box.

user_1291640

For File Open only, the syntax is 

 ,"one\n
 two",...

The critical thing is that there is no space after the first ",". Normally spaces are fine, and trimmed if the string is not quoted. But otherwise nasty. Took me a while to figure that out.

It does not seem to matter if the line is ended \n or \c\n.

Make sure you expand the formula bar so you can actually see the text in the cell (got me after a long day...)

Now of course, File Open will not support UTF-8 Properly (unless one uses tricks).

Excel > Data > Get External Data > From Text

Can be set into UTF-8 mode (it is way down the list of fonts). However, in that case the new lines do not seem to work and I know no way to fix that.

(One might thing that after 30 years MS would get this stuff right.)

user_26981670

You could use keyboard shortcut ALT+Enter.

  1. Select the cell you wish to edit
  2. enter edit mode either by double clicking it or pressing F2 3.Press Alt+enter. This will create a new line in cell
user_82845390
Related