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

User · Accepted Answer

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

User · Answer

Overview  Almost 10 years after the original post  Excel hasn t improved in importing CSV files  However  I found that it is much better in importing HTML tables  So  one can use Python to convert CSV to HTML and then import the resulting HTML to Excel   The advantages of this approach are   a  it works reliably   b  you don t need to send your data to a third party service  e g  Google sheets    c  no extra  fat  installations required  LibreOffice  Numbers etc   for most users   d  higher level than meddling with CR LF characters and BOM markers   e  no need to fiddle with locale settings   Steps  The following steps can be run on any bash-like shell as long as Python 3 is installed  Although Python can be used to directly read CSV  csvkit is used to do an intermediate conversion to JSON  This allows us to avoid having to deal with CSV intricacies in our Python code   First  save the following script as json2html py  The script reads a JSON file from stdin and dumps it as an HTML table      usr bin env python3 import sys  json  html  if   name         main         header emitted   False     make th   lambda s    lt th gt  s lt  th gt      html escape s if s else          make td   lambda s    lt td gt  s lt  td gt      html escape s if s else          make tr   lambda l  make cell    lt tr gt  s lt  tr gt          join  make cell v  for v in l         print   lt html gt  lt body gt  n lt table gt        for line in json load sys stdin           lk  lv   zip  line items            if not header emitted              print make tr lk  make th               header emitted   True         print make tr lv  make td       print   lt  table n lt  body gt  lt  html gt      Then  install csvkit in a virtual environment and use csvjson to feed the input file to our script  It is a good idea to disable cell type guessing with the -I argument     virtualenv -p python3 pyenv       pyenv bin activate   pip install csvkit   csvjson -I input csv   python3 json2html py  gt  output html   Now output html can be imported in Excel  Line breaks in cells will have been preserved    Optionally  you may want to cleanup your Python virtual environment     deactivate   rm -rf pyenv

User · Answer

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

User · Answer

Replace the separator with TAB  t  instead of comma     Then open the file in your editor  Notepad etc    copy the content from there  then paste it in the Excel file

User · Answer

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   In Norway  comma is the decimal separator  Excel seems to avoid this character and prefer a semicolon instead  I have access to another computer set to UK English locale  and on that computer  the first example with a comma separator works fine  only on doubleclick   and the one with semicolon actually fails  So much for interoperability  If you want to publish this CSV online and users may have Excel  I guess you have to publish both versions and suggest that people check which file gives the correct number of rows   So all the details that I ve been able to gather to get this to work are    The file must be saved as UTF-8 with a BOM  which is what Notepad does when you chose UTF-8  I tried UTF-8 without BOM  can be switched easily in Notepad     but then double-clicking the document fails  You must use a comma or a semicolon separator  but not the one that is the decimal separator in your Regional Settings  Perhaps other characters work  but I don t know which  You must quote fields that contain a newline with the   character  I ve used Windows line-endings   r n  both in the text field and as a record separator  that works  You must double-click the file to open it  importing data from text doesn t work    Hope this helps someone

User · Answer

1 on J Ashley s comment  I ran into this problem also  It turns out that Excel requires   A newline character  quot  n quot   in the quoted string  A carriage return and newline between each row    E g   quot Test quot    quot Multiline item n multiline item quot  r n  quot Test2 quot    quot Multiline item n multiline item quot  r n  I used notepad    to delimit each row properly and to only use newlines in the string  Discovered this by creating multiline entries in a blank excel doc and opening the csv in notepad

User · Answer

This worked on Mac  using csv and opening the file in Excel    Using python to write the csv file   data    first line of cell a1 r 2nd line in cell a1 r 3rd line in cell a1   cell b1   1st line in cell c1 r 2nd line in cell c1  n first line in cell a2  n   file write data

User · Answer

None of the suggested solutions worked for me  What actually works  with any encoding   Copy paste the data from the csv-file  open in a text editor   then perform  quot text to columns quot  -- gt  data gets transformed incorrectly  The next stap is to go to the nearest empty column or empty worksheet and copy paste again  same thing what you already have in your clipboard  -- gt  automagically works now

User · Answer

Use Google Sheets and import the CSV file   Then you can export that to use in Excel

User · Answer

This is for Excel 2016   Just had the same problem with line breaks inside a csv file with the Excel Wizard   Afterwards I was trying it with the  New Query  Feature  Data -  New Query -  From File -  From CSV -  Choose the File -  Import -  Load  It was working perfectly and a very quick workaround for all of you that have the same problem

User · Answer

My experience with Excel 2010 on WinXP with French regional settings   the separator of your imported csv must correspond to the list separator of your regional settings    in my case  you must double click on the file from the explorer  don t open it from Excel

User · Answer

Short Answer  Remove the newline linefeed characters   n with Notepad     Excel will still recognise the carriage return character   r  to separate records   Long Answer  As mentioned newline characters are supported inside CSV fields but Excel doesn t always handle them gracefully  I faced a similar issue with a third party CSV that possibly had encoding issues but didn t improve with encoding changes   What worked for me was removing all newline characters   n   This has the effect of collapsing fields to a single record assuming that your records are separated by the combination of a carriage return and a newline  CR LF   Excel will then properly import the file and recognise new records by the carriage return   Obviously a cleaner solution is to first replace the real newlines   r n  with a temporary character combination  replacing the newlines   n  with your seperating character of choice  e g  comma in a semicolon file  and then replacing the temporary characters with proper newlines again

User · Answer

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

User · Answer

What just worked for me  importing into Excel directly provided that the import is done as a text format instead as csv format   M

User · Answer

Excel is incredibly broken when dealing with CSVs  LibreOffice does a much better job  So  I found out that    The file must be encoded in UTF-8 with BOM  so consider this for all the points below The best result  by far  is achieved by opening it from File Explorer If you open it from within Excel there are two possible outcomes    If it has only ASCII characters  it will most likely work If it has non-ASCII characters  it will mess your line breaks  It seems to be heavily dependent on the decimal separator configured in the OS s regional settings  so you have to select the right one I would bet that it may also behave differently depending on OS and Office version

User · Answer

Paste into Notepad    select Encoding   Encode in ANSI  copy all again and paste into Excel

User · Answer

I also had this problem  ie   csv files  comma delimited  double quote delimited strings  with LF in quoted strings  These were downloaded Square files  I did a data import but instead of importing as text files  imported as  from HTML   This time it ignored the LF s in the quoted strings

User · Answer

just create a new sheet with cells with linebreak  save it to csv then open it with an editor that can show the end of line characters  like notepad     By doing that you will notice that a linebreak in a cell is coded with LF while a  real  end of line is code with CR LF  Voil    now you know how to generate a  correct  csv file for excel

User · Answer

I had a similar problem  I had some twitter data in MySQL  The data had Line feed  LF or  n  with in the data  I had a requirement of exporting  the MySQL data into excel  The LF was messing up my import of csv file  So I did the following -  1  From MySQL exported to CSV with Record separator as CRLF 2  Opened the data in notepad    3  Replaced CRLF   r n  with some string I am not expecting in the Data  I used          as replacement of CRLF 4  Replaced LF   n  with Space 5  Replaced          with  r n  so my record separator are back  6  Saved and then imported into Excel   NOTE- While replacing CRLF or LF dont forget to Check Excended   n  r  t    Checkbox  look at the left hand bottom of the Dialog Box

User · Answer

Line breaks inside double quotes are perfectly fine according to CSV standard   The parsing of line breaks in Excel depends on the OS setting of list separator    Windows  you need to set the list seperator to comma  Region and language    Formats    Advanced  Source  https   superuser com questions 238944 how-to-force-excel-to-open-csv-files-with-data-arranged-in-columns answer-633302 Mac  Need to change the region to US  then to manually change back other settings to your preference  Source  https   answers microsoft com en-us mac forum macoffice2016-macexcel line-separator-comma-semicolon-in-excel-2016-for 7db1b1a0-0300-44ba-ab9b-35d1c40159c6  see NewmanLee s answer    Don t forget to close Excel completely before trying again   I ve succesfully replicated the issue and was able to fix it using the above in both Max and Windows

User · Answer

On MacOS try using Numbers  If you have access to Mac OS I have found that the Apple spreadsheet Numbers does a good job of unpicking a complex multi-line CSV file that Excel could not handle  Just open the  csv with Numbers and then export to Excel

User · Answer

If anyone stumbling across this thread and is looking for a definitive answer here goes  credit to the person mentioning LibreOffice   1  Install LibreOffice 2  Open Calc and import file 3  My txt file had the fields separated by   and character fields enclosed in    4  save as ODS file 5  Open ODS file in Excel 6  Save as  xls x  7  Done  8  This worked perfectly for me and saved me BIGTIME

User · Answer

In my case opening CSV in notepad   and adding SEP     as the first line allows me open CSV with line breaks and utf-8 in Excel without issues

[excel] Importing CSV with line breaks in Excel 2007

Examples related to excel

Examples related to csv

Examples related to newline

Examples related to excel-2007