Show comma in CSV without using the comma character

Question

I have a log in CSV format we write out for a certain logging operation. However, one of the fields allows user input and I need to make sure that if they enter a comma in the field that we parse it out and replace it with something that, say, Excel will be able to read and show a comma in its place (so the csv reader will not think it is the end of a column).

Currently I replace the comma with , but this is shows as a literal string in Excel.

Is there a standard way to display a comma in a CSV file without using the actual comma character? Even a solution that only works with excel will work, since most of our customers will be using Excel to view this file.

score 20 · Accepted Answer · edited Sep 16 '16 at 20:14

20

The best way to handle embedded commas is to properly quote the CSV file:

Columns that contain a comma should be quoted
Quoted columns that contain a quote should have the quote escaped

Example:

Joe Smith, "Joe Smith, Jr.", "Joe ""The Man"" Smith, Jr."

I wrote an extension method that helps solve this:

static public string CsvQuote(this string text)
{
    if (text == null) return string.Empty;

    bool containsQuote = false;
    bool containsComma = false;
    int len = text.Length;

    for (int i = 0; i < len && (containsComma == false || containsQuote == false); i++)
    {
        char ch = text[i];
        if (ch == '"')
        {
            containsQuote = true;
        }
        else if (ch == ',' || char.IsControl(ch))
        {
            containsComma = true;
        }
    }

    bool mustQuote = containsComma || containsQuote;

    if (containsQuote)
    {
        text = text.Replace("\"", "\"\"");
    }

    // Quote the cell and replace embedded quotes with double-quote or just return as is
    return mustQuote ? "\"" + text + "\"" : text;
}

USAGE:

logger.Write(myString.CsvQuote());

var csv = string.Join(",", listOfStrings.Select(CsvQuote))

edited Sep 16 '16 at 20:14

Chris Marisic

32,487
24
164
258

answered Feb 08 '13 at 18:59

Eric J.

147,927
63
340
553

1

Thanks. I didn't know that CSV parsers were smart enough to escape entire fields as strings like that – codewario Feb 08 '13 at 19:06
Not all do, especially Excel – Mare Infinitus Feb 08 '13 at 19:13
1

@MareInfinitus: Excel understands the format I described. I do that all the time with huge data sets. Almost any software that's maintained and claims to handle CSV files will understand this quoting. – Eric J. Feb 08 '13 at 19:17
Quoting is usally a good way to do such things. Depending on the parser you use. – Mare Infinitus Feb 08 '13 at 19:59
I made one change to this: `else if (ch == ',' || char.IsControl(ch))` this will make sure strings with CRLFs get quoted too – Chris Marisic Jul 28 '16 at 19:20
1

@ChrisMarisic: Thanks. I made a similar change in my own codebase some time ago. Integrated your change into the answer. – Eric J. Jul 28 '16 at 20:53

score 1 · Answer 2 · answered Feb 08 '13 at 19:02

1

Including your string inside of quotation marks will let you use commas.

"please sir,", can I, have some more?

answered Feb 08 '13 at 19:02

Michael Minter

531
4
16

score 0 · Answer 3 · edited Oct 07 '21 at 06:11

CSV is also "character separated values", not only comma.

You can use any character as a separator, but the tab or \t is widely used for this, as it typically not used in user input.

The RFC for CSV is RFC 4180

It suggests to use data fields and field separators. Here is the original text, please note the special part of Microsoft Excel in (5)

5.  Each field may or may not be enclosed in double quotes (however
   some programs, such as Microsoft Excel, do not use double quotes
   at all).  If fields are not enclosed with double quotes, then
   double quotes may not appear inside the fields.  For example:

   "aaa","bbb","ccc" CRLF
   zzz,yyy,xxx

6.  Fields containing line breaks (CRLF), double quotes, and commas
   should be enclosed in double-quotes.  For example:

   "aaa","b CRLF
   bb","ccc" CRLF
   zzz,yyy,xxx

7.  If double-quotes are used to enclose fields, then a double-quote
   appearing inside a field must be escaped by preceding it with
   another double quote.  For example:

   "aaa","b""bb","ccc"

Please also note that Excel recognizes Tab out of the Box

Like any special character, it *can* show up in data to be logged, if not now perhaps years down the road after someone updates the code. I suggest proper quoting. — Eric J., Feb 08 '13 at 19:03
There will always be a bad programmer breaking your architecture and design in years. There is no silver bullet. — Mare Infinitus, Feb 09 '13 at 07:41
"You can always get t-boned by a drunk driver" doesn't mean it's a bad idea to wear your seat belt. Practicing sound architecture and design reduces opportunities for less-skilled developers to break things. — Eric J., Feb 09 '13 at 23:45

score 0 · Answer 4 · answered Feb 08 '13 at 19:01

0

You can put a quotes around the entire field. Most CSV parsers will understand that the comma is part of the data and not the end of the field.

Or use a different separator. This will require you use the text import wizard in Excel instead of just being able to open the file directly. I typically use~ or |.

answered Feb 08 '13 at 19:01

cadrell0

17,109
5
51
69

That worked great for us years ago until someone named his company along the lines of Store || (yes he used pipe characters). Special characters create special problems. I strongly suggest the use of proper quoting. – Eric J. Feb 08 '13 at 19:02

Show comma in CSV without using the comma character

4 Answers4