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.

Answer 1

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