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)
After lots of tweaking, here's a configuration that works generating files on Linux, reading on Windows+Excel
In Perl, I used Text::CSV to do this as follows:
UTF files that contain a BOM will cause Excel to treat new lines literally even in that field is surrounded by quotes. (Tested Excel 2008 Mac)
The solution is to make any new lines a carriage return (CHR 13) rather than a line feed.
Newline inside a value seems to work if you use semicolon as separator, instead of comma or tab, and use quotes.
This works for me in both Excel 2010 and Excel 2000. However, surprisingly, it works only when you open the file as a new spreadsheet, not when you import it into an existing spreadsheet using the data import feature.
Test this: It fully works for me: Put the following lines in a
xxxx.csv
fileOpen with excel.
in some cases will open directly otherwise will need to use column to data conversion. expand the column width and hit the wrap text button. or format cells and activate wrap text.
and thanks for the other suggestions, but they did not work for me. I am in a pure windows env, and did not want to play with unicode or other funny thing.
This way you putting a formula from csv to excel. It may be many uses for this method of work. (note the = before the quotes)
pd:In your suggestions please put some samples of the data not only the code.
Printing a HTML newline
<br/>
into the content and opening in excel will work fine on any excel