Why do multiline cells in my CSV file appear with a question mark at the end of each line in Excel?

Question

I'm currently working on a project where we'd like to allow a user to export their data to CSV. Some of the data we present has multiple values for a single cell, and so we use the standard CSV method of putting each value on its own line:

Column A, Column B, Column C Value A, "Value B1 Value B2", Value C

Most of the time this works fine, but some people are reporting seeing a small question mark in a box character appear at the end of each line when they load the file in Excel. Why is this happening?

Answer 1

Although the RFC for CSV(http://www.rfc-editor.org/rfc/rfc4180.txt) seems to imply that line breaks should be written as \r\n (CRLF), this is not the format that Excel outputs, and attempting to load files with this format seems to sometimes cause a problem where the [CR] character is displayed as if it were an unknown character.

The CSV format that Excel uses has newlines within a cell represented with a single LF character, and newlines between rows represented using CRLF. So the example above should be formatted:

Column A, Column B, Column C[CR][LF]Value A, "Value B1[LF]Value B2", Value C[CR][LF]