{var%20f='http://v.t.sina.com.cn/share/share.php?appkey=1515056452',u=z||d.location,p=['&url=',e(u),'&title=',e(t||d.title),'&source=',e(r),'&sourceUrl=',e(l),'&content=',c||'gb2312','&pic=',e(p||'')].join('');function%20a(){if(!window.open([f,p].join(''),'mb',['toolbar=0,status=0,resizable=1,width=440,height=430,left=',(s.width-440)/2,',top=',(s.height-430)/2].join('')))u.href=[f,p].join('');};if(/Firefox/.test(navigator.userAgent))setTimeout(a,0);else%20a();})(screen,document,encodeURIComponent,'','','https://www.manongdao.com/data/attach/logo/logo.png', '推荐 乱世女痞 的问题《Generating CSV file for Excel, how to have a newli》','https://www.manongdao.com/q-24430.html','页面编码gb2312|utf-8默认gb2312'));){kind=link}
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)
This will not work if you try to import the file into EXCEL.
Associate the file extension csv with EXCEL.EXE so you will be able to invoke EXCEL by double-clicking the csv file.
Here I place some text followed by the NewLine Char followed by some more text AND enclosing the whole string with double quotes.
Do not use a CR since EXCEL will place part of the string in the next cell.
When you invoke EXCEL, you will see this. You may have to auto size the height to see it all. Where the line breaks will depend on the width of the cell.
2
DATE
Here's the code in Basic
It is worth noting that when a .CSV file has fields wrapped in double quotes which contain line breaks, Excel will not import the .CSV file properly if the .CSV file is written in UTF-8 format. Excel treats the line break as if it were CR/LF and begins a new line. The spreadsheet is garbled. That seems to be true even if semi-colons are used as field delimiters (instead of commas).
The problem can be resolved by using Windows Notepad to edit the .CSV file, using File > Save As... to save the file, and before saving the file, changing the file encoding from UTF-8 to ANSI. Once the file is saved in ANSI format, then I find that Microsoft Excel 2013 running on Windows 7 Professional will import the file properly.
You could use keyboard shortcut ALT+Enter.
Recently I had similar problem, I solved it by importing a HTML file, the baseline example would be like this:
I know, it is not a CSV, and might work differently for various versions of Excel, but I think it is worth a try.
I hope this helps ;-)
you can do the next "\"Value3 Line1 Value3 Line2\"". It works for me generating a csv file in java
The way we do it (we use VB.Net) is to enclose the text with new lines in Chr(34) which is the char representing the double quotes and replace all CR-LF characters for LF.