sqlbulkcopy from Excel via ACE.OLEDB truncates text to 255 chars

Question

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.

Guarded

Well, I'm not sure if this helps you, but you may play with Cell Format of the Excel sheet – Devalue

Didn't help. I set column type to text. Same result. – Guarded

Answer 1

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)} …

Guarded