在Excel 2007中导入带换行符的CSV [英] Importing CSV with line breaks in Excel 2007

问题描述

我正在使用某项功能将搜索结果导出为要在Excel中打开的CSV文件。其中一个字段是自由文本字段，可以包含换行符，逗号，引号等。为了抵消这一点，我用双引号（）包装该字段。

I'm working on a feature to export search results to a CSV file to be opened in Excel. One of the fields is a free-text field, which may contain line breaks, commas, quotations, etc. In order to counteract this, I have wrapped the field in double quotes (").

但是，当我将数据导入到Excel 2007中时，设置适当的分隔符，并将文本限定符设置为双引号，换行符仍然在换行符处创建新记录，我希望看到

However, when I import the data into Excel 2007, set the appropriate delimiter, and set the text qualifier to double quote, the line breaks are still creating new records at the line breaks, where I would expect to see the entire text field in a single cell.

我也尝试用CR（\r）替换CR / LF（\r\\\
）

I've also tried replacing CR/LF (\r\n) with just CR (\r), and again with just LF (\n), but no luck.

有没有其他人遇到这种行为，如果是，你是怎么解决的？

Has anyone else encountered this behavior, and if so, how did you fix it?

TIA，

-J

TIA,
-J

EDIT：

这是我用手写的一个快速文件来复制问题。

Here's a quick file I wrote by hand to duplicate the problem.

ID，名称，说明

12345，Smith，Joe，嘿。

我的名字是Joe。

ID,Name,Description
"12345","Smith, Joe","Hey.
My name is Joe."

当我将它导入到Excel 2007中时，我最终得到一个标题行和两个记录。请注意，Smith，Joe中的逗号正在处理中。

When I import this into Excel 2007, I end up with a header row, and two records. Note that the comma in "Smith, Joe" is being handled properly. It's just the line breaks that are causing problems.

希望有帮助。

推荐答案

我终于找到了问题！

事实证明，我们使用Unicode编码，而不是ASCII或UTF-8编写文件。更改FileStream上的编码似乎解决了问题。

It turns out that we were writing the file using Unicode encoding, rather than ASCII or UTF-8. Changing the encoding on the FileStream seems to solve the problem.

感谢大家的所有建议！

这篇关于在Excel 2007中导入带换行符的CSV的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！