如何将CsvHelper记录添加到DataTable中以供SqlBulkCopy用于数据库

Question

我正在尝试使用CsvHelper读取CSV文件，将每个记录加载到DataTable中，然后使用SqlBulkCopy将数据插入数据库表中。在当前代码中，当向DataTable添加行时会出现异常。异常为：“无法将类型为'MvcStockAnalysis.Models.StockPrice'的对象转换为类型'System.IConvertible'。无法将 存储在Date列中。期望的类型是DateTime。”

示例CSV文件来自雅虎金融。例如：http://ichart.yahoo.com/table.csv?s=MMM&a=0&b=1&c=2010&d=0&e=17&f=2014&g=d&ignore=.csv

CSV文件包含以下标题： 日期 开盘价 最高价 最低价 收盘价 成交量 调整收盘价

我要将CSV文件读取到的模型：

namespace MvcStockAnalysis.Models
{
    using System;
    using System.Collections.Generic;

    public partial class StockPrice
    {
        public int Id { get; set; }
        public System.DateTime Date { get; set; }
        public int CompanyId { get; set; }
        public double High { get; set; }
        public double Low { get; set; }
        public double Close { get; set; }
        public double AdjClose { get; set; }
        public double Open { get; set; }
        public double Volume { get; set; }

        public virtual Company Company { get; set; }
    }
}

将CSV文件映射到StockPrice类的过程使用以下内容：

public class StockPriceClassMap : CsvClassMap<StockPrice>
{
    public override void CreateMap()
    {
        Map(m => m.Date).Name("Date");
        Map(m => m.Close).Name("Close");
        Map(m => m.AdjClose).Name("Adj Close");
        Map(m => m.High).Name("High");
        Map(m => m.Low).Name("Low");
        Map(m => m.Open).Name("Open");
        Map(m => m.Volume).Name("Volume");
    }
}

尝试将CsvHelper记录添加到DataTable的代码如下：

var connectionstring = ConfigurationManager.ConnectionStrings["MvcStockAnalysis.Models.MvcStockAnalysisContext"];
var connection = new SqlConnection();
connection.ConnectionString = connectionstring.ToString();
var destinationTableName = "StockPrices";
var company = db.Company
            .Where(c => c.Symbol == "MMM")
            .FirstOrDefault();

try
{
    string path = HttpContext.Server.MapPath("~/App_Data/" + company.Symbol + @".csv");

    if (System.IO.File.Exists(path))
    {     

        using (StreamReader sr = new StreamReader(path))
        {
            using (var csv = new CsvReader(sr))
            {
                DataTable dt = new DataTable("StockPrices");
                csv.Configuration.HasHeaderRecord = true;
                csv.Configuration.RegisterClassMap<StockPriceClassMap>();

                dt.Columns.Add(new DataColumn("Date", typeof(DateTime)));
                dt.Columns.Add(new DataColumn("Close", typeof(Double)));
                dt.Columns.Add(new DataColumn("AdjClose", typeof(Double)));
                dt.Columns.Add(new DataColumn("High", typeof(Double)));
                dt.Columns.Add(new DataColumn("Low", typeof(Double)));
                dt.Columns.Add(new DataColumn("Open", typeof(Double)));
                dt.Columns.Add(new DataColumn("Volume", typeof(Double)));
                dt.Columns.Add(new DataColumn("CompanyId", typeof(Double)));
                var records = csv.GetRecords<StockPrice>().ToList();
                foreach (var record in records)
                {                                    
                    record.CompanyId = company.Id;
                    dt.Rows.Add(record);
                }
                // add dt to the database
                using (var bulkCopy = new SqlBulkCopy(connection.ConnectionString))
                {
                    // DataTable column names match my SQL Column names, so I simply made this loop. 
                    foreach (DataColumn col in dt.Columns)
                    {
                        bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
                    }
                    bulkCopy.DestinationTableName = destinationTableName;
                    bulkCopy.WriteToServer(dt);
                }
            }
        }
    }
    connection.Close();
}
catch (Exception e)
{
    Console.Write(e.Message);
}

我该如何将 CsvHelper 记录添加到 DataTable 中，以便用于 SqlBulkCopy 到数据库中？

- Justin Nafe

Answer 1

如果我没看错的话，你应该可以用更少的代码实现它。在进入 DataTable 之前，你也不必将其放入另一个类中。

while( csv.Read() )
{
    var row = dt.NewRow();
    foreach( DataColumn column in dt.Columns )
    {
        row[column.ColumnName] = csv.GetField( column.DataType, column.ColumnName );
    }
    dt.Rows.Add( row );
}

- Josh Close

---

感谢您的投票，因为……您知道……您是这个库的作者。 - Joe Phillips

如果有一种方法可以从Map中获取IndexOf(colName)，那么当DataTable列顺序与CSV顺序不匹配时，它将会很有帮助。 - Ňɏssa Pøngjǣrdenlarp

@JoePhillips那又怎样？难道库的作者不能回答问题吗？ - Stefan Fachmann

@StefanFachmann 是的，这就是我为什么点赞的原因。请再读一遍我的评论。 - Joe Phillips

Answer 2

去年Josh添加了读取标题的支持，以下代码块可能对那些只想使用CSV文档模式构建DataTable的人有用。我本想将其作为对Josh答案的评论发布，因为这只是一个小修改，但由于无法在评论中格式化代码块，所以将其发布为答案。

    private DataTable BuildDataTable()
    {
        var dt = new DataTable();
        using (var textReader = new StreamReader(_path))
        {
            using (var csv = new CsvReader(textReader))
            {
                csv.ReadHeader();
                foreach (var header in csv.FieldHeaders)
                {
                    dt.Columns.Add(header);
                }
                while (csv.Read())
                {
                    var row = dt.NewRow();
                    foreach (DataColumn column in dt.Columns)
                    {
                        row[column.ColumnName] = csv.GetField(column.DataType, column.ColumnName);
                    }
                    dt.Rows.Add(row);
                }
            }
        }
        return dt;
    }

- Rahul Misra

---

https://learn.microsoft.com/en-us/dotnet/csharp/fundamentals/coding-style/coding-conventions - Rahul Misra

请解释一下这个编辑的内容以及背后的原因。我认为在读取标题之前不需要进行csv.Read()操作。 - Rahul Misra

Answer 3

我喜欢@JoshClose的答案，但是我发现while(csv.Read())比csv.GetRecords<{Class}>().ToList()要慢得多。它也无法正确处理许多可空类型，例如int?，当返回值应该是DBNull时。我的建议是让CsvHelper导入一个动态记录列表，然后使用一些辅助方法自动映射到DataTable。

var records = csv.GetRecords<dynamic>().ToList();

foreach ( record in records )
{
    var row = dt.NewRow();

    var recordDictionary = DynamicToDictionary( record );

    foreach( DataColumn column in dt.Columns )
    {
        row[column.ColumnName] = GetColumnValue( column, recordDictionary );
    }

    dt.Rows.Add( row );
}

DynamicToDictionary方法处理大小写敏感度和标题空格。我将动态对象转换为一个忽略大小写并删除标题空格的字典对象。如果这不是问题，可以跳过此步骤直接将动态对象传递给GetColumnValue。

public Dictionary<string, object> DynamicToDictionary(dynamic dynObj)
{
    var dictionary = new Dictionary<string, object>(StringComparer.OrdinalIgnoreCase);

    foreach (var kvp in (IDictionary<string, object>) dynObj)
    {
        var obj = kvp.Value;

        // Remove white space.
        var name = new string(kvp.Key.ToCharArray().Where(c => !char.IsWhiteSpace(c)).ToArray());

        dictionary.Add(name, obj);
    }

    return dictionary;
}

< p > GetColumnValue 方法查找并将动态记录值转换为正确的 DataTable 列值。

public object GetColumnValue(DataColumn column, IDictionary<string, object> dynamicDictionary)
{
    object value;

    // Return DBNull if the column name isn't found.
    if (!dynamicDictionary.TryGetValue(column.ColumnName, out value))
    {
        return DBNull.Value;
    }

    // Null values come in as empty strings.
    if (column.AllowDBNull && column.DataType != typeof(string) && (string)value == "")
    {
        return DBNull.Value;
    }

    if (column.DataType == typeof(bool))
    {
        return (string)value != "0" && ((string)value).ToLower() != "false";
    }

    return value;
}

- David Specht

Answer 4

我通过添加一个 DataTable 行并直接填充它，而不是尝试将 CsvHelper 记录作为行添加，成功使其工作。

我使用了以下部分，而不是上面显示的类似部分：

foreach (var record in records)
{
    DataRow row = dt.NewRow();
    record.CompanyId = company.Id;
    row["Date"] = record.Date;
    row["Close"] = record.Close;
    row["AdjClose"] = record.AdjClose;
    row["High"] = record.High;
    row["Low"] = record.Low;
    row["Open"] = record.Open;
    row["Volume"] = record.Volume;
    row["CompanyId"] = record.CompanyId;
    dt.Rows.Add(row);
}

如果您可以不使用大量的硬编码解决问题，我会将您的答案接受为答案。

- Justin Nafe