100

Is it possible/legal to somehow encode CR/LF characters into a CSV file?

(as part of a CSV standard?)

If so how should I encode CR/LF?

Yves M.
  • 29,855
  • 23
  • 108
  • 144
Tore Nestenius
  • 16,431
  • 5
  • 30
  • 40

4 Answers4

126

Yes, you need to wrap in quotes:

"some value
over two lines",some other value

From this document, which is the generally-accepted CSV standard:

Fields containing line breaks (CRLF), double quotes, and commas should be enclosed in double-quotes

Brent Worden
  • 10,624
  • 7
  • 52
  • 57
Kent Boogaart
  • 175,602
  • 35
  • 392
  • 393
  • 9
    Isnt the generally-accepted format RFC 4180 ? https://en.wikipedia.org/wiki/Comma-separated_values – HaveAGuess Aug 10 '15 at 08:57
  • 2
    The wikipedia article also says "... (however, many CSV implementations do not support embedded line breaks)." – dansalmo Nov 28 '16 at 21:24
  • 1
    @HaveAGuess [RFC 4180](https://tools.ietf.org/html/rfc4180) says the same thing here. Page 2, item 6. – jpmc26 Jun 30 '17 at 22:57
  • 5
    @jpmc26 indeed, I'm just trying encourage OP to send visitors to the official RFC, instead of claiming this other site is the 'generally-accepted CSV standard' – HaveAGuess Jul 03 '17 at 09:01
  • even after adding double-quotes, the data gets inserted in new cell. – vikas etagi May 11 '20 at 06:25
  • If the double quotes (") shows up and the line-breaks still cause trouble, look for spaces! There should be no space between the coma (,) ending the previous cell and the double quote (") starting the multiline text. – Samuel Åslund Sep 24 '20 at 14:38
  • This simply is not working, not even when re-importing a CSV with multi-line cells exported as CSV right from Excel itself. Excel from MS 365 does not import the file correctly, not even its own variant with CRLF line feeds vs. LF for multi-line cells. – kriegaex Dec 08 '22 at 09:13
  • 1
    Update: It is working, if (and only if) the CSV uses **semicolon (not comma)** as a column separator and - very important - the file is **opened via double-click**, not via Excel's "open file" dialogue. How crazy is that?! – kriegaex Dec 08 '22 at 14:40
19

the most common variant of csv out there which is the excel compatible one will allow embedded newlines so long as the field is surrounded by double quotes.

foo,bar,"blah blah
more blah blah",baz

or

foo,bar,"blah blah
more blah blah"

or

"blah blah
more blah blah",baz

are all valid. This mechanism also allows for embedded commas.

Using quotes around textual fields without embedded new lines (or commas) is fine too. If the text itself contains a double quote then mechanism to escape it is to put two together, for example.

foo,bar,"this person said ""blah blah 
more blah blah""",baz

Writing a csv reader that handles this correctly can be tricky (especially if you are relying on regular expressions).

ShuggyCoUk
  • 36,004
  • 6
  • 77
  • 101
  • 1
    what if I want to embed a quote? – towc Mar 21 '19 at 13:23
  • 1
    Can't confirm this. Importing the data with any newline character in a quoted field breaks the rest of the cells into a new spreadsheet row for me in Office 365 ProPlus Excel v1808. Encoded with Windows-1250. – Tarec Feb 04 '21 at 19:13
11

Mention has been made here of a standard for CSV. I'd be interested to know more about this - the only standards I'm aware of are

  • 1
    yes, the RFC you link to is the definitive standard. It mentions putting CRLF inside double quotes to escape it. Unfortunately, your point about whatever excel accepts is valid... yet another case of MS trying to subvert standards. – rmeador Feb 19 '09 at 16:48
  • 2
    That RFC was created in 2005! Excel has supported CsV for quite a lot longer then that... – ShuggyCoUk Feb 20 '09 at 08:05
  • 2
    ShuggyCoUk that was the last update, it was created long before that : https://en.wikipedia.org/wiki/Comma-separated_values – HaveAGuess Aug 10 '15 at 08:58
1

I don't think it's part of the standard (if there even is one), but you could use standard C-style escaping, i.e. encode \r\n.

Keep in mind, however, that if you do that you should also encode the escape character -- i.e. \\ yields \ after decoding.

Yves M.
  • 29,855
  • 23
  • 108
  • 144
Randolpho
  • 55,384
  • 17
  • 145
  • 179
  • 5
    csv does not use C-style escaping –  Feb 19 '09 at 16:21
  • 1
    some csv apps support this form. csv is a regrettably poorly defined standard. Talking Excel csv is (pragmatically) best though which this is not – ShuggyCoUk Feb 19 '09 at 16:22