如何将数据表转换为CSV? [英] How can I turn a DataTable to a CSV?

问题描述

有人可以告诉我下面的代码为什么不起作用.数据已保存到csv文件中，但是没有分隔数据.所有这些都存在于每一行的第一个单元格中.

Could somebody please tell me why the following code is not working. The data is saved into the csv file, however the data is not separated. It all exists within the first cell of each row.

StringBuilder sb = new StringBuilder();

foreach (DataColumn col in dt.Columns)
{
    sb.Append(col.ColumnName + ',');
}

sb.Remove(sb.Length - 1, 1);
sb.Append(Environment.NewLine);

foreach (DataRow row in dt.Rows)
{
    for (int i = 0; i < dt.Columns.Count; i++)
    {
        sb.Append(row[i].ToString() + ",");
    }

    sb.Append(Environment.NewLine);
}

File.WriteAllText("test.csv", sb.ToString());

谢谢.

推荐答案

以下较短的版本在Excel中可以正常打开，也许您的问题是尾随逗号

The following shorter version opens fine in Excel, maybe your issue was the trailing comma

StringBuilder sb = new StringBuilder(); 

string[] columnNames = dt.Columns.Cast<DataColumn>().
                                  Select(column => column.ColumnName).
                                  ToArray();
sb.AppendLine(string.Join(",", columnNames));

foreach (DataRow row in dt.Rows)
{
    string[] fields = row.ItemArray.Select(field => field.ToString()).
                                    ToArray();
    sb.AppendLine(string.Join(",", fields));
}

File.WriteAllText("test.csv", sb.ToString());

.net> = 4.0

正如Tim所指出的，如果您使用的是.net> = 4，则可以使其更短:

.net >= 4.0

And as Tim pointed out, if you are on .net>=4, you can make it even shorter:

StringBuilder sb = new StringBuilder(); 

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

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

File.WriteAllText("test.csv", sb.ToString());

如克里斯蒂安(Christian)所建议，如果要处理在字段中转义的特殊字符，请用以下方式替换循环块:

As suggested by Christian, if you want to handle special characters escaping in fields, replace the loop block by:

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

最后一个建议是，您可以逐行编写csv内容，而不是整个文档，以避免内存中有大文档.

And last suggestion, you could write the csv content line by line instead of as a whole document, to avoid having a big document in memory.

这篇关于如何将数据表转换为CSV?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！