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.

Grassi

I've looked at the CSV file in Notepad++, and everything appears to be correct. I have other fields with commas, and they are being imported properly. It's just the line breaks that are causing problems. – Grassi

I have issues with UTF8 .csv files with multi-line data and excel. I ended up just uploading the file to Google Docs, opening it into a google sheet, then downloading as a .xls file. Works well for me this way. – Disciplinary

Answer 1

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!

Grassi

ASCII encoding didn't seem to fix the issue for me (on MacOS though), and I don't have a leading space and my field is quoted. The exact same doc imports fine in Google Docs. How frustrating. BTW, there is no such thing as a "Unicode" encoded text file. It has to be one of the implementations of Unicode (UTF-8, UTF-16, UTF-32, etc.) – Lewes

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. – Interesting

ASCII encoding didn't fix the issue for me either - Excel 2000, Windows 7. – Tiddly

For OS X on Macintosh, save as "Windows Comma Separated (csv)". This adds newlines instead of line breaks. It wil be listed in the drop down menu for formats under "Specialty Formats". – Logway

Which Unicode encoding should be used (UTF-8, UTF-16) ? – Requisite

Try to always use UTF-8, UTF-16 is being developed for dual byte characters, and these are very rare in almost all languages. – Chicken

Answer 2

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.

Satiated

Any idea how to get the same settings as with double clicking? – Radioluminescence

It's true! How strange. – Ravin

If you are using german regional settings you have to use semicolon (;) instead of comma (,) in your csv for the double click to work... – Leslielesly

This is my experience as well with Excel 2016. Haven't figured out yet how to get the "Open" behavior when doing an import. Anyone have any luck? – Croissant

it did not work for me. With "," as delimiter it opened everything in one column with double-click. With ";" as delimiter it was imported correctly except for the multi-line text fields, which were imported as several records. I have Excel 2010 – Requisite

@Leslielesly I double that for Hungarian locale. actually any locale that uses comma as decimal separator has to use semicolon as field separator for the double-click csv open to work properly – Meed

it opens for me with double-clicking, but the unicode characters are messed up. i want to use import because i can set codepage in the first step. – Meed

Double click method confirmed here in Excel 2013. I have to convert UTF-8 to Windows-1250 in my case to show diacritics correctly. Line endings CRLF (Win) and LF (Lin) both work. CR (Mac) does not. – Fidelity

Answer 3

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 "text to columns" --> 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) --> automagically works now.

Rimarimas

In my case this worked, in a way: it correctly collapsed the CSV to the single records but removed all data in a field past the newline. – Nicolenicolea

This worked, any ideas why it doesn't work when importing the csv from excel? – Musquash

I can confirm that this works, you can even paste more data in different sheets without repeating the "text to columns" command. This is useful if you need to import several files. – Spindly

Why does this work but neither opening the CSV or adding it as Text Data with all the proper settings not work? Thanks for the tip. The Copy/Paste team needs to talk to the data import team! – Nurture

I didn't believe that it might work, but it does! Excel 2016. As mentioned by @Nicolenicolea it removes all the data in multi-line column past the first line, so if you really do need it, this might not be the best method. Other columns are preserved correctly. – Wilbertwilborn

@VasilyAlexeev & Lilienthal Are you sure? It just worked for me, with the multi-line content, but it didn't expand the row height. So it looked like just one line until I looked in the formula box or expanded the row. – Cytolysis

When you go to the new tab to paste, remember that your column formatting will not be remembered. So if your data has phone numbers, you might want to format the correct columns as text, date, etc... before you paste your data again. Other than that this is the only solution on this page that works.(Oct 2017) – Immanuel

Holy shit. this really works. and it kinda makes sense why. When making a "text to columns" Excel remembers the settings and it will auto transform. When you have the text already separated into rows it will look row by row and ignore new lines. I think that MS should include a checkbox wether to keep the behavior or rescan the data. I don't care, ... if freaking works – Custom

Unbelievable. Actually works. Microsoft Office Home and Student 2013. I read this and thought "yeah right...but what's the harm in trying?" Fan-bloody-tastic! – Turgescent

ONLY worked for me after doing "|" PIPE separated rather than comma separated. Not sure why. but this worked with Pipe and Double quotes, regardless of encoding (UTF-8, ANSI). And Regardless of line endings (all CR /r, or all NL /n) Did NOT work with single quotes. – Alliance

Works with semi-colon delimiter as well. – Turgescent

Answer 4

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:

1. 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.
2. 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.
3. You must quote fields that contain a newline with the " character.
4. I've used Windows line-endings (\r\n) both in the text field and as a record separator, that works.
5. You must double-click the file to open it, importing data from text doesn't work.

Hope this helps someone.

Icelandic

Also, the trick mentioned by @Rimarimas seems to work! I think what happens is that when you first paste and do a "text to columns" maneuver, you're configuring the quoting and field separator stuff in Excel. The second time you paste, it uses this configuration, and splits the data correctly into columns based on the configuration. But this seems to be a very manual approach. – Icelandic

yes, each time you Import Text or do a Text to Column, you recalibrate how copy/paste will work in the given session. it is even applied to new workbooks you create, until you close Excel. it can be frustrating, too. once you use a given separator for import, it will separate your text by that even if you just want to paste a sentence in a cell. you have to redo import with tab as a separator, or restart Excel to stop it. – Meed

Your trick really seems to work. But it looks like the semicolon has nothing to do with the solution. The problem is, that Excel treats CSV files differently, depending on regional settings. I'm from Germany, and for me CSV files from Excel always have semicolons instead of commas (the reason for this is, that in Germany the decimal seperator is comma instead of point). The real solution seems to be, that Excel loads CSV files totally different than all other text files. So CSV files that contains line breaks in between quotations seems to work. All other text files don’t. – Kathyrnkati

@Martini, yes, I have Norwegian Excel and we also use comma as the decimal separator, so I've mentioned how this depends on the regional settings (though I referred to it as the locale). Perhaps I should rephrase for clarity. – Icelandic

This is the answer for all people in regions where comma is the decimal separator. Note that for these regions, Excel also uses semicolon as the formula argument separator (=FOO(1;2) instead of =FOO(1,2)), but clearly it is incorrect that Excel applies this to a file format parser (which other program parses a standard file format dependent on the locale???) – Breastsummer

The problem with double clicking is that Excel won't get the encoding right. The import wizard allows you to select the encoding manually. – Assumption

@Assumption What version of Excel are you referring to? I'm not sure Excel 2007 had an import wizard that could do this? – Icelandic

@Icelandic I have Excel 2017. I know it's a bit off topic, but searching for Excel in general redirects to this thread, so I though I'd comment to warn user of more recent versions. Note that the problem is the same for Excel 2017. – Assumption

Bingo with the BOM hint! It worked for me now (UTF-8 with BOM, double clicking, semicolon as a separator). You can add the BOM in VIM with set :bomb, btw. – Deprivation

The question is asking about Excel, not LibreOffice. – Moseley

Answer 5

Use Google Sheets and import the CSV file.

Then you can export that to use in Excel

Jeffreys

Good tip! This is the most convenient conversion method if you are ok with uploading your CSV to a third party service (i.e. non-confidential data). Note that you may have to manually set the delimiter at importing. And you may need to adjust the cell size in the resulting Excel file for it to display correctly. – Torchier

Also works with Excel in Office 365 in a browser. I could not properly open a CSV with line breaks inside of cells with the desktop Excel application (trying most suggestions from this page), but Excel on office.com could properly open it. – Ladder

In my case, this was THE ONLY SOLUTION, as I have to later re-import the changed CSV to my database and only Google Sheets was able to correctly interpret the line break embedded in the text. Thank you very much! – Rude

This works. Period. – Camail

Answer 6

Multiline CSV can be imported easily in Excel versions with Power Query using following steps (tested in Excel 365 version 2207):

1. Go to Data-tab
2. Click "From Text/CSV" on the ribbon
3. Select file and click Import
4. Click "Transform Data" to open Power Query Editor
5. Click "Data source settings" on the Power Query Editor ribbon
6. Click "Change Source"
7. Select "Ignore quoted line breaks" from the "Line breaks" dropdown.
8. Click OK -> Close -> Close & Load

Ule

Although the "Ignore quoted line breaks" is selected by default, ur hint worked - after opening this dlalogue and closing again. Thx! – Houselights

This process works very well for me when converting a CSV to an Excel file, acting on a CSV that has been exported from MySQL Workbench using the Table Data Export Wizard, when that data contains all kinds of newlines, double quotes, and other special characters. The tabular format is maintained in the resulting Excel. – Angelia

Answer 7

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.

Nicolenicolea

I had the opposite situation: \n between lines and \r\n inside values. Just stripped the latter in Notepad++. – Ghirlandaio

I tried both and neither worked on Office pro plus 2013 – Alliance

Just for completeness, I had the other case - \n in values, \r\n between lines - and removing the \r allowed me to properly use Text to Columns. – Disgusting

Answer 8

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"

Rosemaria

I agree, however, I don't have any leading spaces in my output. Any ideas? – Grassi

we need the line broken :( – Turgescent

Answer 9

+1 on J Ashley's comment. I ran into this problem also. It turns out that Excel requires:

• A newline character("\n") in the quoted string

• A carriage return and newline between each row.

E.g.

"Test", "Multiline item\n
multiline item"\r\n
"Test2", "Multiline item\n
multiline item"\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 ++.

Odericus

it worked for me with only newline character as both a multiline item and a row separator, once i set the field separator according to my locale – Meed

note: this didn't work in office pro plus 2013 I suspect different versions had different handling of this – Alliance

Answer 10

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!

Pentothal

no need to save as ODS, LibreOffice can save xls(x) natively – Meed

Sad to say that this is still the only reliable solution for large data files that can't be handled by the clipboard. – Kenney

Answer 11

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: "<th>%s</th>" % (html.escape(s if s else ""))
    make_td = lambda s: "<td>%s</td>" % (html.escape(s if s else ""))
    make_tr = lambda l, make_cell: "<tr>%s</tr>" % ( "".join([make_cell(v) for v in l]) )
    print("<html><body>\n<table>")
    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("</table\n</body></html>")

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

Torchier

Answer 12

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.

Uneven

Answer 13

With Excel 2019 I had a similar problem when working with CSV files via Data -> Import from text file / CSV. Once the connection is made and the data is synced, it reported xx error(s) because of shifted columns caused by the line breaks.

I managed to solve this by

1. Edit the query (Query -> Edit)

2. This opens the Power Query Editor

3. Go to Start -> Advanced Editor

4. This opens the query in text format, where line #2 had an instruction like

   Source = Csv.Document(File.Contents("my.csv"),[Delimiter=",", .... , QuoteStyle=QuoteStyle.None]),

5. Change QuoteStyle.None to QuoteStyle.Csv

6. Click Finish

7. Apply & close

Documentation found here: https://learn.microsoft.com/en-us/powerquery-m/csv-document

NB. I since found where this is "hidden" in the UI. In the Power Query-editor, click Data source settings, Change source (bottom left), and the Line breaks combo should say Ignore line breaks between quotes.

NB2. Working from Dutch Excel here so my above-mentioned translations of button captions etc. may be a little off.

Annabelannabela

Answer 14

Paste into Notepad++, select Encoding > Encode in ANSI, copy all again and paste into Excel :)

Guenther

Answer 15

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

Conquest

Answer 16

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)

Metopic

Answer 17

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

Tupungato

You're asserting LibreOffice is a better guesser than Excel, right? Excel asks all the right questions when importing text files, unless you tell it to guess. – Effectually

Thank You! It helps me to convert my CSV from "UTF-8 without BOM" to "UTF-8 with BOM" (just simple "UTF-8" in menu) by Notepad++. Then I just opened it from Explorer and Excel showed it in proper way with correct symbols and correct line breaks inside cells. When I opened it in default "UTF-8 without BOM" encoding from Explorer Excel imported line breaks correctly, but displays non-latin symbols in wrong way. If I opened it from Excel, it showed encoding in right way, but didn't cope with line breaks. – Mika

Answer 18

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.

Sarchet

I test but with a tab seperator and line feed inside cells : don't seem to work (file is OK on LibreOffice and GFoogle doc).The line feed inside cell goes to next line … – Maternity

Answer 19

It appears that this is much easier in more recent versions of Excel:

1. Go to "Data" -> "Get Data (Power Query)"
2. In the dialogue that opens, select "Text / CSV" on the right
3. Search for the file and then click "Next" and follow the recommendations (in my case, Excel now correctly realized it's UTF8 and that cells were separated by ";" and the text identifier were double quotes (")
4. You're done!

This took a little moment to load but afterwards I had an auto-formatted table that looked really nice and that did understand that multi-line entries were still part of the same entry.

If you want the multi-lines to show correctly, simply format the cells and under "Alignment", click the checkbox for "Wrap text". That should solve the last of your issues.

Good luck! ;-)

Euniceeunuch

Answer 20

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

Dacosta

Answer 21

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.

Alcalde

Answer 22

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.

Jackstraw

Answer 23

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)

Anuran

Answer 24

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

Ozenfant

Answer 25

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.

Fleam

Try this on large files :) – Dewayne

Answer 26

No idea why this worked, but it did for me. Nothing worked previously, not Open, not import, not double-click on file, nothing.

Finally, I tried what one person suggested was to copy from a text file and paste, then do text to columns.

In the process of trying this, I got lucky. Here's what happened. Note that my CSV file had double quotes surrounding the cells.

• I opened the CSV file in Visual Studio Code
• Saved as XML
• Copied the contents of the file from Visual Studio Code
• Pasted the contents into Excel and it just knew what to do

I had tried saving the file as an XML hoping I'd have better luck importing it into Excel, but what finally worked was just pasting the text straight into excel and it just broke everything into the right columns.

Installment