将DataTable导出到CSV时出现逗号问题 [英] Comma issue when exporting DataTable to CSV

问题描述

我采用了一些将DataTable转换为CSV文件的代码.除了在实际数据中使用逗号之外，该方法似乎运行良好.在这种情况下，有没有办法显示逗号?这就是我所做的:

  StringBuilder sb = new StringBuilder();IEnumerable< string>columnNames = dtResults.Columns.Cast< DataColumn>().Select(column => column.ColumnName);sb.AppendLine(string.Join(，"，columnNames));foreach(dtResults.Rows中的DataRow行){IEnumerable< string>字段= row.ItemArray.Select(field => field.ToString());sb.AppendLine(string.Join(，"，fields));}File.WriteAllText(saveFileDialog.FileName，sb.ToString()); 

解决方案

如果字段包含逗号，则应使用双引号(或单引号)

.

您可以使用 FileHelpers库来创建CSV文件，它应适当地进行转义./p>

如果您不想使用该库，最重要的一点是:如果有逗号，则必须将字段放在引号中，例如

  ID，NAME1，"Doe，John"2，"Doe，Jane" 

如果您的字段包含引号，则应使用其他引号将其转义:

  3，"Anakin""Darth Vader"，天行者"4，'O'Connor，Sinead' 

可能的解决方案:

 静态字符串CsvEscape(此字符串值){如果(value.Contains(，")){返回"\""+值.Replace(" \"，"\" \")+"\";}返回值；} 

然后在您的代码中:

  IEnumerable< string>字段= row.ItemArray.Select(字段=>字段.ToString().CsvEscape()); 

PS:根据 Wikipedia ，没有真正的csv规范，但 RFC 4180 描述了一种经常使用的格式.

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

解决方案

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.

这篇关于将DataTable导出到CSV时出现逗号问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！