sqlbulkcopy从Excel通过ACE.OLEDB截断文本到255个字符 [英] sqlbulkcopy from Excel via ACE.OLEDB truncates text to 255 chars

问题描述

  string excelConnectionString = @Provider = Microsoft.ACE.OLEDB.12.0; Data Source =+ filePath +;扩展属性= \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，标题）; 
 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）} ...

Pretty straight-forward import using 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);
    }
}

Data goes in, no problem. Except the first column, which is mapped to Body (nvarchar(max)) gets truncated to 255 characters. I looked around, found some references to workaround that involves changing a registry setting. Set value to 0 to force full scan, not just first 8 rows, which is Excel's default, but that didn't help even after reboot. Looking for other ideas. Thank you.

解决方案

I used ODBC instead of the OLEDB and it doesn't truncate the values to 255 symbols anymore:

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

you can use 2007 format driver to access XLSX files: .... Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)} …

这篇关于sqlbulkcopy从Excel通过ACE.OLEDB截断文本到255个字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！