How to remove unwanted commas in row values when convert Datatable to csv?

Question

Attached image represents sample datatable This is my code to convert data table to CSV. In my row values contains many commas. So values showed undefined format while creating a CSV using comma separator

    StringBuilder tempsb = new StringBuilder(); 

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

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

     File.WriteAllText("sample.csv", tempsb.ToString());

1) label with the programming language you are using, 2) show us a sample from your CSV file, and 3) clean up your formatting. — Tim Biegeleisen, Jan 28 '16 at 07:30
Replace the commas in `fields` with something else, encapsulate a field with " or use another separating character (like ';') — Chrisi, Jan 28 '16 at 08:43
Wouldn't the same problem arise if the user inputs ";" in his form? — Gil Sand, Jan 28 '16 at 08:51
As csv [is not formally defined](https://tools.ietf.org/html/rfc4180#page-2), telling what would use your csv could have some impact. Anyway, there is lot of responses to your question on stackoverflow already. — Frédéric, Jan 28 '16 at 09:16
And http://stackoverflow.com/q/4617935/1178314, http://stackoverflow.com/q/12473480/1178314 — Frédéric, Jan 28 '16 at 09:17

score 2 · Accepted Answer · answered Jan 28 '16 at 09:23

In a CSV file, when a , appears in a field then the field needs to be surrounded by double-quotes. If a field contains double-quotes then the double-quotes should be doubled.

So you need to create your fields like this:

    IEnumerable<string> fields = row.ItemArray
        .Select(field => field.ToString().Contains(",") || field.ToString().Contains("\"")
            ? String.Format("\"{0}\"", field.ToString().Replace("\"", "\"\"")) 
            : field.ToString());

score 0 · Answer 2 · answered Jan 28 '16 at 08:47

I would suggest using the second or third of the options provided by Chrisi in the comments on your question but, to answer the question as asked, you'd use the first option like so:

IEnumerable<string> fields = row.ItemArray.Select(field => field.ToString().Replace(",", myReplacementString);

The replacement string might be string.Empty, an HTML encoded value or whatever.

How to remove unwanted commas in row values when convert Datatable to csv?

2 Answers2