568

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?

Mawg says reinstate Monica
  • 38,334
  • 103
  • 306
  • 551
buley
  • 28,032
  • 17
  • 85
  • 106
  • 1
    Possible duplicate of [Dealing with commas in a CSV file](http://stackoverflow.com/questions/769621/dealing-with-commas-in-a-csv-file) – Ciro Santilli OurBigBook.com Nov 22 '15 at 22:43
  • Little hack that worked for me: open the csv file in a text editor, then paste into an excel document. Works if you can do this in big chunks. – Jonas Aug 08 '16 at 17:36
  • 1
    @Jonas: Excel cannot guess, whether the same token is used as a delimiter or part of the data. Pasting from a text editor doesn't change that. – IInspectable Feb 13 '17 at 12:53

18 Answers18

742

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):

  • regular_value → regular_value
  • Fresh, brown "eggs" → "Fresh, brown ""eggs"""
  • " → """"
  • "," → ""","""
  • ,,," → ",,,"""
  • ,"", → ","""","
  • """ → """"""""

See wikipedia.

Nolan Amy
  • 10,785
  • 5
  • 34
  • 45
Ryan
  • 26,884
  • 9
  • 56
  • 83
  • 7
    This worked for me! I was exporting a js variable to CSV, and putting an extra pair of quotes around every string, helped solve the problem of "John Blow, CFA", as mentioned in the question. – Madhulika Mukherjee Dec 02 '15 at 12:29
  • What if there are both quotes and commas inside each cell? – speedplane Feb 19 '16 at 01:20
  • 2
    I updated the answer with some examples - in short, commas are encapsulated in quotes (such as `","`), and quotes are escaped (e.g. `"""`) – Ryan Feb 19 '16 at 01:30
  • 7
    Now THIS should be the accepted answer as it proposes a great solution that likely solves 99% of use cases. – BuZz Jul 19 '18 at 08:51
  • 1
    Is there a C# library to parse csv files with quote-escaped fields? – Minh Tran Jan 29 '19 at 20:35
  • My Excel imports the csv column with the quotes still intact. Is there a way to import the column and remove the quotes? – Blaisem Oct 20 '20 at 12:34
  • 1
    This is the correct answer but not that MANY csv display technologies do not honor this. – markthegrea Dec 17 '20 at 20:31
62

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.

mudin
  • 2,672
  • 2
  • 17
  • 45
39

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.

Community
  • 1
  • 1
Rudu
  • 15,682
  • 4
  • 47
  • 63
  • 15
    RFC 4180, Common Format and MIME Type for Comma-Separated Values (CSV) Files, https://www.ietf.org/rfc/rfc4180.txt; one further 'official' specification. – Shi May 07 '17 at 16:29
  • 3
    Including the most widely-accepted approach would make this a great answer. As it stands, the answer by @Ryan wins. – rinogo Jun 06 '18 at 21:18
22

If you want to make that you said, you can use quotes. Something like this

$name = "Joe Blow, CFA.";
$arr[] = "\"".$name."\"";

so now, you can use comma in your name variable.

Cloud
  • 1,004
  • 1
  • 18
  • 47
14

You need to quote that values.
Here is a more detailed spec.

Sylvain Rodrigue
  • 4,751
  • 5
  • 53
  • 67
SLaks
  • 868,454
  • 176
  • 1,908
  • 1,964
  • 2
    There are multiple specs, Excel uses quotes around the entry, Linux uses the backslash character. Unfortunately, you need to escape for your intended audience/target system. – Rudu Jan 06 '11 at 17:56
12

In addition to the points in other answers: one thing to note if you are using quotes in Excel is the placement of your spaces. If you have a line of code like this:

print '%s, "%s", "%s", "%s"' % (value_1, value_2, value_3, value_4)

Excel will treat the initial quote as a literal quote instead of using it to escape commas. Your code will need to change to

print '%s,"%s","%s","%s"' % (value_1, value_2, value_3, value_4)

It was this subtlety that brought me here.

Mogsdad
  • 44,709
  • 21
  • 151
  • 275
hlongmore
  • 1,603
  • 24
  • 28
8

You can use Template literals (Template strings)

e.g -

`"${item}"`
Krishna
  • 6,107
  • 2
  • 40
  • 43
  • 2
    Worked like a charm! – waliurjs Jun 03 '20 at 13:43
  • 1
    This just stringifies and wraps `item` in double quotes but using template literals has little to do with it, and while this works it doesn't explain the underlying issue or the solution. – goldins Apr 16 '21 at 21:00
8

CSV files can actually be formatted using different delimiters, comma is just the default.

You can use the sep flag to specify the delimiter you want for your CSV file.

Just add the line sep=; as the very first line in your CSV file, that is if you want your delimiter to be semi-colon. You can change it to any other character.

Example

Ahmed Mohammed
  • 407
  • 5
  • 7
5

This isn't a perfect solution, but you can just replace all uses of commas with ‚ or a lower quote. It looks very very similar to a comma and will visually serve the same purpose. No quotes are required

in JS this would be

stringVal.replaceAll(',', '‚')

You will need to be super careful of cases where you need to directly compare that data though

RasTheDestroyer
  • 1,756
  • 16
  • 22
  • I feel like this is a bad idea - live how sometimes a bug is caused by a zero-width character. – abc Jan 26 '23 at 04:00
4

Depending on your language, there may be a to_json method available. That will escape many things that break CSVs.

amiksch
  • 82
  • 8
2

I faced the same problem and quoting the , did not help. Eventually, I replaced the , with +, finished the processing, saved the output into an outfile and replaced the + with ,. This may seem ugly but it worked for me.

Mohammed
  • 1,364
  • 5
  • 16
  • 32
  • 3
    And what would you have done if the data contained "+" characters? – Andrew Morton Nov 17 '16 at 20:25
  • 1
    @AndrewMorton Yes, that is a challenge. But in my case, I am sure that `+` does not exist in my data at all. Thanks for you kind comment. – Mohammed Nov 18 '16 at 18:35
  • 4
    The simple and robust way is to enclose the strings in double-quotes and escape double-quotes within strings by using two of them like `""`. This is the normal way of doing it. – Andrew Morton Nov 18 '16 at 18:45
  • 2
    If it isn't working, check that you are not generating spaces alongside your delimiting commas: "Frog","yellow, green" will work "Frog", "yellow, green" will not – Dazbert Jun 19 '17 at 17:11
  • Personally I found @AndrewMorton second comment here actually helped me understand what do better than accepted answer. Step 1 - escape double quotes in the data fields, eg in Python: `field = field.replace('"', '""')` Step 2 - after Step 1, enclose field itself in double quotes eg `field = '"' + field + '"'` - then you're iron-cast I think. – Will Croxford Jan 02 '20 at 17:36
2

May not be what is needed here but it's a very old question and the answer may help others. A tip I find useful with importing into Excel with a different separator is to open the file in a text editor and add a first line like:

sep=|

where | is the separator you wish Excel to use. Alternatively you can change the default separator in Windows but a bit long-winded:

Control Panel>Clock & region>Region>Formats>Additional>Numbers>List separator [change from comma to your preferred alternative]. That means Excel will also default to exporting CSVs using the chosen separator.

user25307
  • 41
  • 3
1

You could encode your values, for example in PHP base64_encode($str) / base64_decode($str)

IMO this is simpler than doubling up quotes, etc.

https://www.php.net/manual/en/function.base64-encode.php

The encoded values will never contain a comma so every comma in your CSV will be a separator.

Kevin
  • 121
  • 1
  • 4
0

You can use the Text_Qualifier field in your Flat file connection manager to as ". This should wrap your data in quotes and only separate by commas which are outside the quotes.

maxshuty
  • 9,708
  • 13
  • 64
  • 77
sakshi jain
  • 321
  • 3
  • 4
0

First, if item value has double quote character ("), replace with 2 double quote character ("")

item = item.ToString().Replace("""", """""")

Finally, wrap item value:

ON LEFT: With double quote character (")

ON RIGHT: With double quote character (") and comma character (,)

csv += """" & item.ToString() & ""","
LifeiSHot
  • 141
  • 2
  • 4
0

Double quotes not worked for me, it worked for me \". If you want to place a double quotes as example you can set \"\".

You can build formulas, as example:

fprintf(strout, "\"=if(C3=1,\"\"\"\",B3)\"\n");

will write in csv:

=IF(C3=1,"",B3)

Stevoisiak
  • 23,794
  • 27
  • 122
  • 225
mathengineer
  • 140
  • 6
0

A C# method for escaping delimiter characters and quotes in column text. It should be all you need to ensure your csv is not mangled.

private string EscapeDelimiter(string field)
    {
        if (field.Contains(yourEscapeCharacter))
        {
            field = field.Replace("\"", "\"\"");
            field = $"\"{field}\"";
        }

        return field;
    }
Squibly
  • 335
  • 2
  • 7
0

Tab-separated (tsv) files are superior to csv and are supported and understood by Excel and other applications. It is highly puzzling that csv files are still in use. Wherever possible use tsv. Commas, double quotes and semi-colons all tend to occur in text (leading to a quagmire of non-standardised encoding conventions), <TAB> characters very much not so. Although not an answer to the question itself, it is worthwhile pointing out for those coming here for whom escaping from csv files is still an option.

micans
  • 1,106
  • 7
  • 16