Jet Engine - 255 character truncation

I'm needing to import an Excel spreadsheet into my program and have the following code:

string connectionString = String.Format(@"Provider=Microsoft.Jet.OLEDB.4.0开发者_开发技巧;Data Source={0};Extended Properties=""Excel 8.0;IMEX=1;HDR=NO;""", MyExcelFile.xls);

command.CommandText = "SELECT * FROM [Sheet1$]";

(Note, code above isn't real code but should let you see what I'm doing)

I'm getting the file imported, only problem is any columns in the Excel sheet which are over 255 characters are being truncated.

Is there any way around this happening?

I read somewhere that if you make sure there is a long line of text in the column within the first 8 rows, then it will be treated as a memo field and therefore not truncated but that didn't seem to work.

Any ideas?

Graeme

Bumped into this one a few times. Fortunatly there's a registry hack to fix, described on MSDN here: http://support.microsoft.com/kb/189897

Effectively, Excel only looks at the first eight rows of data to determine how long columns should be. 255 is the default if the length is 255 chars or less. The MSDN article I've referenced above explains how to add a registry key "TypeGuessRows" that tells Excel how many rows to scan to determine column lengths.

Probably your problem has an easier solution, but as a last resort, try to save your Excel file as a CSV text file, then process it using the regular file and string manipulation classes instead of the JET engine.

Because I couldn't find the exact answer I needed, I'm going to leave this here in case it helps anyone.

HKEY_LOCAL_MACHINE ► Software ► Wow6432Node ► Microsoft ► Office ► 12.0 ► Access Connectivity Engine ► Engines

TypeGuessRows = 0

Source

It is usually best to import into an existing table. It is not too difficult to create a suitable table via code.