Is there a way to include commas in CSV columns without breaking the formatting?
Enclose the field in quotes, e.g.
field1_value,field2_value,"field 3,value",field4, etc...
See wikipedia.
Updated:
To encode a quote, use "
, one double quote symbol in a field will be encoded as ""
, and the whole field will become """"
. So if you see the following in e.g. Excel:
---------------------------------------
| regular_value |,,,"| ,"", |""" |"|
---------------------------------------
the CSV file will contain:
regular_value,",,,""",","""",","""""""",""""
A comma is simply encapsulated using quotes, so ,
becomes ","
.
A comma and quote needs to be encapsulated and quoted, so ","
becomes ""","""
.
The problem with the CSV format, is there's not one spec, there are several accepted methods, with no way of distinguishing which should be used (for generate/interpret). I discussed all the methods to escape characters (newlines in that case, but same basic premise) in another post. Basically it comes down to using a CSV generation/escaping process for the intended users, and hoping the rest don't mind.
Reference spec document.
I found that some applications like Numbers in Mac ignore the double quote if there is space before it.
a, "b,c"
doesn't work while a,"b,c"
works.