在 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 ( ) 替换 CR/LF ( )，然后再用 LF ( ) 替换，但没有成功.

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

有没有其他人遇到过这种行为，如果有，你是如何解决的?

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

TIA，
-J

这是我手工编写的一个快速文件，用于复制问题.

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

ID、名称、描述
12345"、史密斯，乔"、嘿.
我叫乔."

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屋！