Excel cell-values are truncated by OLEDB-provider

Question

Hi folks,

I'm using the OleDbConnection class to retrieve data from an Excel 2000/2003 workbook:

string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                          "Data Source=" + filename + ";" +
                          "Extended Properties=\"Excel 8.0;IMEX=1\";";

OleDbConnection connection = new OleDbConnection(connectionString);
connection.Open();

// code to get table name from schema omitted

var dataAdapter = new OleDbDataAdapter(string.Format("SELECT * FROM [{0}]", name),connection);
var myDataSet = new DataSet();
dataAdapter.Fill(myDataSet, "ExcelInfo");

Now it turns out that cells in the worksheet with length greater than 255 characters are being truncated. Is this a limitation in the Microsoft.Jet.OLEDB-provider, or is there something I can do about it?

Anyone?

Answer 1

Looks like you need a schema.ini file to set the column type(s) to 'memo':

http://msdn.microsoft.com/en-us/library/ms709353.aspx

Answer 2

The OLEDB provider for excel will attempt to automatically determine the DataTypes based off of the first 8 rows of data, this can be set with the HDR=Yes/No property in the connection string. Additionally, there are multiple types that it can apply to text columns. The memo type holds over 255 characters, so if none of the first 8 rows have that then it will incorrectly set the data type.

The way to change this is by changing a registry setting called TypeGuessRows, as described here: Microsoft Support