从Excel通过ACE.OLEDB的sqlbulkcopy将文本截断为255个字符

excel oledb sqlbulkcopy

使用SqlBulkCopy非常直接导入:

string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1;\"";
using (OleDbConnection excelConnection = new OleDbConnection(excelConnectionString))
{
    excelConnection.Open();
    OleDbCommand cmd = new OleDbCommand("Select " + fileID.ToString() + " as [FileID], * from [Sheet1$] where [Text] IS NOT NULL", excelConnection);
    OleDbDataReader dReader = cmd.ExecuteReader();

    using (SqlBulkCopy sqlBulk = new SqlBulkCopy(ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString))
    {
        sqlBulk.DestinationTableName = "table_name";
        sqlBulk.ColumnMappings.Add(0, "FileID");
        sqlBulk.ColumnMappings.Add(4, "Author");
        sqlBulk.ColumnMappings.Add(3, "Title");
        sqlBulk.ColumnMappings.Add(1, "Body");
        sqlBulk.ColumnMappings.Add(2, "PublishedDate");
        sqlBulk.BulkCopyTimeout = 600;
        sqlBulk.WriteToServer(dReader);
    }
}

数据进入,没问题。除第一列(映射到Body(nvarchar(max))之外,将截断为255个字符。我环顾四周,发现了一些涉及更改注册表设置的变通方法的参考。将值设置为0以强制完全扫描,而不仅仅是前8行,这是Excel的默认值,但即使在重新启动后也没有帮助。寻找其他想法。谢谢。

热门答案

我使用ODBC而不是OLEDB,它不再将值截断为255个符号:

OdbcConnection con = new OdbcConnection(@"Driver={Microsoft Excel Driver
(*.xls)};DBQ=c:\temp\testbook.xls");
OdbcCommand cmd = new OdbcCommand(@"SELECT * FROM [Workbook1$]");
cmd.Connection = con;
OdbcDataAdapter da = new OdbcDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);

您可以使用2007格式驱动程序来访问XLSX文件:.... Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)} â€



许可下: CC-BY-SA with attribution
不隶属于 Stack Overflow
这个KB合法吗? 是的,了解原因
许可下: CC-BY-SA with attribution
不隶属于 Stack Overflow
这个KB合法吗? 是的,了解原因