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

Ever wonder how to go to the next line within a cell in Numbers or Excel on a Mac. In this video I show you how! E-mail - macandcomputerhelp@gmail.com.

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.

Excel (at least in Office 2007 on XP) can behave differently depending on whether a CSV file is imported by opening it from the File->Open menu or by double-clicking on the file in Explorer. I have a CSV file that is in UTF-8 encoding and contains newlines in some cells. If I open this file from Excel's File->Open menu, the 'import CSV' wizard pops up and the file cannot be correctly imported: the newlines start a new row even when quoted.

If I open this file by double-clicking on it in an Explorer window, then it opens correctly without the intervention of the wizard. Thanks for the solution. I was still curious what the answer is so I tried creating a csv with a line break in Excel and seeing what it saved. I turns out Excel uses only a line feed for a new line in a cell. If I try to create the same csv in Notepad, it will use a line feed + carriage return for the line break. So for line breaks in a single cell, make sure it's only using a line feed (LF or n) and not a carriage return (CR or r).

Excel does use both to terminate a row. – May 31 '12 at 15:04 •. If you are doing this manually, download LibreOffice and use LibreOffice Calc to import your CSV.

It does a much better job of stuff like this than any version of Excel I've tried, and it can save to XLS or XLSX as required if you need to transfer to Excel afterwards. But if you're stuck with Excel and need a better fix, there seems to be a way. It seems to be locale dependent (which seems idiotic, in my humble opinion). I don't have Excel 2007, but I have Excel 2010, and the example given: ID,Name,Description '12345','Smith, Joe','Hey.

My name is Joe.' Doesn't work. I wrote it in Notepad and chose Save as., and next to the Save button you can choose the encoding. I chose UTF-8 as suggested, but with no luck. Changing the commas to semicolons worked for me, though. I didn't change anything else, and it just worked. So I changed the example to look like this, and chose the UTF-8 encoding when saving in Notepad: ID;Name;Description '12345';'Smith, Joe';'Hey.

My name is Joe.' But there's a catch! The only way it works is if you double-click the CSV file to open it in Excel. If I try to import data from text and chose this CSV, then it still fails on quoted newlines. But there's another catch! The working field separator (comma in the original example, semicolon in my case) seems to depend on the system's Regional Settings (set under Control Panel -> Region and Language).