csv

Is there a way to include commas in CSV columns without breaking the formatting?

I've got a two column CSV with a name and a number. Some people's name use commas, for example Joe Blow, CFA. This comma breaks the CSV format, since it's interpreted as a new column.

I've read up and the most common prescription seems to be replacing that character, or replacing the delimiter, with a new value (e.g. this|that|the, other).

I'd really like to keep the comma separator (I know excel supports other delimiters but other interpreters may not). I'd also like to keep the comma in the name, as Joe Blow| CFA looks pretty silly.

Is there a way to include commas in CSV columns without breaking the formatting, for example by escaping them?

Solution

  • To encode a field containing comma (,) or double-quote (") characters, enclose the field in double-quotes:

    field1,"field, 2",field3, ...

    Literal double-quote characters are typically represented by a pair of double-quotes (""). For example, a field exclusively containing one double-quote character is encoded as """".

    For example:

    Sheet: |Hello, World!|You "matter" to us.|

  CSV: "Hello, World!","You ""matter"" to us."

    More examples (sheet → csv):

    See Wikipedia.