CODEWITHSUNDEEP
c#.netwpfcsvexport-to-csv
Dot NET
Dot NET

Reputation: 4907

Comma issue when exporting DataTable to CSV

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

Upvotes: 3

Views: 8367

Answers (2)

Paolo Tedesco
Paolo Tedesco

Reputation: 57272

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.

Upvotes: 8

Smaug
Smaug

Reputation: 2673

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

Upvotes: 0

Related Questions