Comma issue when exporting DataTable to CSV

Question

I've adopted some code which converts a DataTable into a CSV file. It seems to work well, except for when commas are used in the actual data. Is there a way to display the comma in that case? This is what I've done:

StringBuilder sb = new StringBuilder();

IEnumerable<string> columnNames = dtResults.Columns
                                           .Cast<DataColumn>()
                                           .Select(column => column.ColumnName);
sb.AppendLine(string.Join(",", columnNames));

foreach (DataRow row in dtResults.Rows)
{
    IEnumerable<string> fields = row.ItemArray.Select(field => field.ToString());
    sb.AppendLine(string.Join(",", fields));
}

File.WriteAllText(saveFileDialog.FileName, sb.ToString());

possible duplicate of [Commas within CSV Data](http://stackoverflow.com/questions/4123875/commas-within-csv-data) — ChrisF, Feb 18 '13 at 14:00
Another possible duplicate: http://stackoverflow.com/questions/769621/dealing-with-commas-in-a-csv-file — Paolo Tedesco, Feb 18 '13 at 14:01
`field.ToString()` can cause a `NullReferenceException`. Use `string.Join(",",row.ItemArray)` — Tim Schmelter, Feb 18 '13 at 14:02

score 8 · Accepted Answer · edited Oct 07 '21 at 05:51

8

If a field contains a comma it should be put in double (or single) quotes.

You could use the FileHelpers library to create your CSV file, it should take care of escaping properly.

In case you don't want to use the library, the essential bit is this: if you have a comma, you must put your field within quotes, e.g.

ID, NAME
1, "Doe, John"
2, 'Doe, Jane'

If your field includes quotes, you should escape them with an additional quote:

3, "Anakin ""Darth Vader"", Skywalker"
4, 'O''Connor, Sinead'

A possible solution:

static string CsvEscape(this string value) {
    if (value.Contains(",")) {
        return "\"" + value.Replace("\"", "\"\"") + "\"";
    }
    return value;
}

And then in your code:

IEnumerable<string> fields = row.ItemArray.Select(field => field.ToString().CsvEscape());

P.S: according to Wikipedia there is no real csv specification, but RFC 4180 describes a format that is often used.

edited Oct 07 '21 at 05:51

Community

1
1

answered Feb 18 '13 at 14:00

Paolo Tedesco

55,237
33
144
193

Or to save the `OP` changing to much of his code, use `@"\"" + field @"\""` in the select of fields? :) – LukeHennerley Feb 18 '13 at 14:05
@LukeHennerley - Could you kindly elaborate on that please? :) – Dot NET Feb 18 '13 at 14:08
@DotNET Should you have a situation in which you could have your seperator inside some data, you should wrap the data with double quotes `""` – LukeHennerley Feb 18 '13 at 14:16
@LukeHennerley But what if i've got `""` within the data too? :/ – Dot NET Feb 18 '13 at 14:21
@DotNET: I expanded my answer a little. – Paolo Tedesco Feb 18 '13 at 14:59
Really a short and nice solution – Smaug Feb 19 '13 at 05:15
My edit was rejected for some reason, so here comes a comment: You also need to escape a value beginning with a ". So change the **if** in **CsvEscape** this way: if (value.Contains(",") || (value.Length > 0 && value[0] == '\"')) { – thomiel Apr 19 '13 at 12:33

Smaug · Answer 2 · 2013-02-18T14:43:04.327

I published the below modified code which will help you.

StringBuilder sb = new StringBuilder();

            string[] val =  { "ramesh","suresh, Ganesh" };
            IEnumerable<string> columnNames = val;

            IList<string> objFinal = new List<string>();
            foreach (string v in columnNames)
            {
                string temp = v;
                if (temp.Contains(","))
                    temp = "\"" + v + "\"";

                objFinal.Add(temp);
            }
            sb.AppendLine(string.Join(",", objFinal.AsEnumerable<string>()));

            }

The CSV supports the comma in data between the double codes.

You can append the double codes as well as comma in a single shot with help of the below single line of code

IEnumerable<string> columnNames = dtResults.Columns
                                           .Cast<DataColumn>()
                                           .Select(column => column.ColumnName);

var res = columnNames.Aggregate((current, next) => "\"" + current + "\"" + ", " + "\"" + next + "\"");


File.WriteAllText(saveFileDialog.FileName, res.ToString());

Comma issue when exporting DataTable to CSV

2 Answers2

Linked