Jet Engine - 255 character truncation

Question

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

Enterostomy

Answer 1

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.

Lindeberg

Ended up doing it this way - now I open the Excel file through C#, save it to CSV and then parse that. Thanks all – Enterostomy

This is the solution? Seriously? What a crock; worthless, worthless excel spreadsheets. For such a popular format, it's borderline impossible to read data from it into an application. There are no well-maintained, open-source, fully-functional APIs for reading xlsx spreadsheets as of today, and these JET and ACE engines are such a hack (guessing field types by looking at first X rows, truncating fields to 255 characters, registry hacks necessary to change options, excel required to be installed for certain features, file being open in excel changes behavior, etc.). What a joke! – Klansman

@Triynko: I completely agree this is a joke, and sadly it's a joke on us developers :( These kinds of things make me start thinking about moving from MS to something else cause after all this proves that MS doesn't care :( – Selfabuse

@Klansman even in 2012, still faced with this issue – Broderic

And in 2013, same thing. Facing the same problem. Registry hack ineffective. #14637974 – Meteoroid

...and in 2016; i just experienced this limitation. i guess i'll do what Graeme did... – Kero

Answer 2

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.

Tarnetgaronne

Yeah, this is the thing I was meaning in my original post - checked my registry and the TypeGuessRows value is 8. I intentionally put in a value 500 chars long in row 2 of each column which might have large data but this didn't seem to make any difference... – Enterostomy

From blog.lab49.com/archives/196 the idea is that you can use values between 0 and 16 for this registry entry. 0 means read the first 16000 rows, 1 to 16 means read the first 1 to 16 rows. So setting this to 500 won't work, but setting it to 0 should. – Creamer

Answer 3

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

Renaerenaissance

Answer 4

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

Cognoscenti

And to those problems of 255 length and type guessing go away? Can you provide some working sample? – Selfabuse

If you import to an existing table in Access, the types are already set in Access, so long text should be set up with a memo type, eliminating the 255 problem, and other fields (columns) should be set-up with the relevant type, eliminating guessing. It is also possible to change the registry to eliminate guessing. – Cognoscenti

Did this work for you or are you just guessing ("should" does not convicne me)? Also you cannot "change the registry to eliminate guessing", you can just make guessing more reliable, but not eliminate it 100%. – Selfabuse

See support.microsoft.com/kb/194124 for registry settings. I am speaking from previous experience. As far as I can tell, there is less of a problem after 2007 editions. – Cognoscenti