Comma issue when exporting DataTable to CSV
I have adopted some code that will convert the DataTable to CSV file. This seems to work well, except when commas are used in the actual data. Is there a way to display the comma in this case? This is what I did:
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());
source to share
If the field contains a comma, it must be enclosed in double (or single) quotes.
You can use FileHelpers library to create your CSV file, it should take care of evacuation properly.
If you don't want to use a library, the main bit is this: if you have a comma, you must put your field in quotes, for example.
ID, NAME
1, "Doe, John"
2, 'Doe, Jane'
If your field contains quotes, you should avoid them with an extra quote:
3, "Anakin ""Darth Vader"", Skywalker"
4, 'O''Connor, Sinead'
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());
PS: There is no real csv specification as per Wikipedia , but RFC 4180 describes a format that is often used.
source to share
I posted below modified code to 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>()));
}
CSV supports comma in data between double codes.
You can add binary codes as well as comma in one shot using the following 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());
source to share