Can you encode CR/LF in into CSV files?

Asked 19/2, 2009 at 16:18 Answered 19/2, 2009 at 16:43

Solved csv

111

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?

Nonproductive answered 19/2, 2009 at 16:18 Comment(0)

140

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

P.S For fields with double quotes inside, you should also escape double quotes with two quotes, e.g

"Samsung U600 24"""

Reductase answered 19/2, 2009 at 16:20 Comment(9)

Isnt the generally-accepted format RFC 4180 ? en.wikipedia.org/wiki/Comma-separated_values – Diaphone 10/8, 2015 at 8:57

The wikipedia article also says "... (however, many CSV implementations do not support embedded line breaks)." – Harness 28/11, 2016 at 21:24

@Diaphone RFC 4180 says the same thing here. Page 2, item 6. – Expository 30/6, 2017 at 22:57

@Expository 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' – Diaphone 3/7, 2017 at 9:1

even after adding double-quotes, the data gets inserted in new cell. – Stephi 11/5, 2020 at 6: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. – Flap 24/9, 2020 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. – Rake 8/12, 2022 at 9:13

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?! – Rake 8/12, 2022 at 14:40

This works importing to Jira. – Penury 7/11, 2023 at 14:46

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

foo,bar,"blah blah
more blah blah"

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

Enceinte answered 19/2, 2009 at 16:26 Comment(2)

what if I want to embed a quote? – Calyx 21/3, 2019 at 13:23

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. – Apocarp 4/2, 2021 at 19:13

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

whatever excel accepts
the RFC at www.rfc-editor.org/rfc/rfc4180.txt

Normy answered 19/2, 2009 at 16:43 Comment(3)

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. – Wainscoting 19/2, 2009 at 16:48

That RFC was created in 2005! Excel has supported CsV for quite a lot longer then that... – Enceinte 20/2, 2009 at 8:5

ShuggyCoUk that was the last update, it was created long before that : en.wikipedia.org/wiki/Comma-separated_values – Diaphone 10/8, 2015 at 8:58

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.

Weikert answered 19/2, 2009 at 16:20 Comment(3)

csv does not use C-style escaping – Normy 19/2, 2009 at 16:21

some csv apps support this form. csv is a regrettably poorly defined standard. Talking Excel csv is (pragmatically) best though which this is not – Enceinte 19/2, 2009 at 16:22

IETF RFC 4180 :) – Alluring 27/4 at 2:52

Recommended topics

Hot tags