How to read a data row from Excel with more than 255 characters

Question

The issue is When I set up the DataSet as shown below, the default reads only 255 characters from the spreadsheet cell and places them into the table.

DataSet exData = new DataSet();
string connectionString = String.Format(ConnectionString, path);
OleDbDataAdapter ex = new OleDbDataAdapter(ExcelQuery, connectionString);
ex.Fill(exData);

I'm using Extended Properties=Excel 8.0 and Microsoft.Jet.OLEDB.4.0, there is no problem connecting to the excel sheet.

From my reading it follows that it is due to the Jet.OLEDB provider, what should I be using?

And I may be unable to update to new provider of Jet, is there any workaround? Any workaround would would be restricted on not being able to directly modify the Excel document to contain two cells or more for data over 255 chars.

Thanks.

Answer 1

The standard max length of a text column in jet is 255 characters. I haven't tried this, but if you create a schema.ini file and tell it that your column is a memo type, you might be able to put more data in.