Importing CSV with line breaks in Excel 2007

Question

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 (").

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.

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

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

ID,Name,Description
"12345","Smith, Joe","Hey.
My name is 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.

Hope that helps.

Answer 1

If the field contains a leading space, Excel ignores the double quote as a text qualifier. The solution is to eliminate leading spaces between the comma (field separator) and double-quote. For example:

Broken:
Name,Title,Description
"John", "Mr.", "My detailed description"

Working:
Name,Title,Description
"John","Mr.","My detailed description"

Answer 2

I have finally found the problem!

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.

Thanks everyone for all your suggestions!