Export to CSV - How to write values which contain commas? c#

Question

I currently have a string builder for exporting to a csv file. The values that are being exported in the CSV file contain commas in somw values.

Sometimes the data contains a "," causing it to generate incorrectly.

public string ExportActionMapAsCsv()
    {
        StringBuilder builder = new StringBuilder(",");
        List<EggEmbryoActivity> actions = GetActions();
        List<EggStatusActionMap> actionMaps = GetActionMaps();

        builder.AppendLine(string.Join(",", actions.Select(x => x.Title)));

        foreach (EggStatusActionMap actionMap in actionMaps)
        {
            builder.Append(actionMap.StatusDescription);
            builder.Append(",");
            builder.AppendLine(string.Join(",", actionMap.ActionMaskAsBinary.PadLeft(actions.Count, '0').ToCharArray()));
        }

        return builder.ToString();
    }

That is generating the export but how can I display data with commas in a single column

There are a lot of things to think about with csv, unless it is something you wish to do yourself, you could use a library available on nuget, like [csvhelper](https://www.nuget.org/packages/CsvHelper/) for example. When you have an extra `columnCharacter` in a value, you would wrap them with a `wrappingCharacter` and then catch the usage of the `wrappingCharacter` and escape that one — Icepickle, Oct 25 '20 at 22:53

score -1 · Answer 1 · answered Oct 26 '20 at 02:00

I'd be very inclined to create an extension method that converts a string into a valid CSV field.

If a field contains a comma or if it contains a double-quote then it must be enclosed in double-quotes and any double-quote must be duplicated within the string.

Here's the extension method:

public static class Ex
{
    public static string ToCsvSafeField(this string field) =>
        (field.Contains(',') || field.Contains('"'))
            ? $"\"{field.Replace("\"", "\"\"")}\""
            : field;
}

Then I'd rewrite the code like this:

foreach (EggStatusActionMap actionMap in actionMaps)
{
    var items =
        actionMap
            .ActionMaskAsBinary
            .PadLeft(actions.Count, '0')
            .ToCharArray()
            .Select(x => x.ToString().ToCsvSafeField())
            .StartWith(actionMap.StatusDescription.ToCsvSafeField());
            
    builder.AppendLine(string.Join(",", items));
}

I'd seems like you are also missing to escape newlines, which could break the CSV if the field contains the value. — Roy, Jun 14 '23 at 13:27

matmahnke · Accepted Answer · 2020-10-29T13:05:33.307

-2

you will need to add quotation marks to the string you want to put a comma

     foreach (EggStatusActionMap actionMap in actionMaps)
     {
         builder.Append("\"" + neutralizeInput(actionMap.StatusDescription) + "\"");
         builder.Append(",");
         builder.AppendLine("\"" + neutralizeInput(string.Join(",", actionMap.ActionMaskAsBinary.PadLeft(actions.Count, '0').ToCharArray())) + "\"");
     }

     private string neutralizeInput(string input){
         return input.replace("\"", "\"\"")
     }

edited Oct 29 '20 at 13:05

answered Oct 25 '20 at 23:00

matmahnke

432
3
10

1

What if the string already contains a `"`? – Enigmativity Oct 26 '20 at 01:57
Can you please respond to Enigmativity's question? – mason Oct 28 '20 at 13:03
I've been searching, to put quotes in the content you would need to edit from " to "" – matmahnke Oct 29 '20 at 13:02

Export to CSV - How to write values which contain commas? c#

2 Answers2