Alexander Miles Alexander Miles - 2 months ago 6
C# Question

Show comma in CSV without using the comma character

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

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