Excel cell-values are truncated by OLEDB-provider

Question

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?

Assuan

Answer 1

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

NOTE: The valid range of values for the TypeGuessRows key is 0 to 16. However, if the value is 0, the number of source rows scanned is 16384. So if you have a very large file make sure the biggest rows are first.

Redraft

Thanks. It's a somewhat ugly solution, as my installer will have to modify the user's registry, but I expect it will work. So long as an administrator runs the installer.. – Assuan

i dont understand microsofts suggestion here/ if you change it to 16 doesn't it just check the first 16 rows ?? – Sanctimony

@ooo If you change it to 0, then it scans all rows. Be careful though as this can have a performance impact on larger tables. – Redraft

@rmoore: I have changed it to 0 in registry. But, still it is truncating at 255. Do I need to restart the machine? or is it some other problem? – Morgan

If you do not want to mess with the registry just insert a dummy row at the beginning of the excel table which has more than 255 characters in each column and it will work, too. Of yourse in your program logic you ignore the dummy row when inserting/updating or doing other things. – Joh

Answer 2

Try this OleDBAdapter Excel QA I posted via stack overflow.

I populated an worksheet cell (Rows[0][4]) w/ 445 characters and it worked fine... Add this to the end of the code for the ouput

// DataSet:          
Object row0Col3 = ds.Tables["xlsImport"].Rows[0][2];
Object row0Col4 = ds.Tables["xlsImport"].Rows[0][4];

string rowZeroColumn3 = row0Col3.ToString();
string rowZeroColumn4 = row0Col4.ToString();

Console.WriteLine("Row 0, Col 4 string length: {0} " + Environment.NewLine + "Excel content: {1}", rowZeroColumn4.Length, rowZeroColumn4);           

Lassiter