Excel Data Truncated to 255 Characters
 

I'm trying to import the data from an Excel spreadsheet into a ADO.NET
DataTable using the Jet engine and I'm running into a problem where it
is truncating the text data to 255 characters. I know that it tries to
read x number of lines from the file (I think 8 by default) and make a
determination based on that about how big to make the DataTable
column. And I know that if I put one of the bigger values up at the
front of the spreadsheet that it will work properly but I don't want
to have to rely on doing that.

Apparently there is a registry setting (HKEY_LOCAL_MACHINE\SOFTWARE
\Microsoft\Jet\4.0\Engines\Excel\TypeGuesRows) you can set to make Jet
read in more lines before determining the max data length. Supposedly
setting it to 0 makes it read in all the rows. I've tried tweaking it
with no results. I really don't want to have to rely on modifying the
registry anyway.

I've also tried using the MAXROWSTOSCAN=0 in the Jet connection string
to no avail.

The only thing that appears to work is to move the data around. Isn't
there a better way to do this? Isn't there a way to get Jet to read in
all the rows before making the size determination?

I believe the files are in Excel 2003 format. Here is the connection
string I am using.

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/file.xls;Extended
Properties="Excel 8.0;IMEX=1;HDR=YES"

I also tried the Microsoft.ACE.OLEDB.12.0 provider and got the same
results.