Data truncated after 255 bytes while using Microsoft.Ace.Oledb.12.0 provider

Question

I am reading an excel sheet using the ACE provider and certain cells contain data greater than 255 bytes. I tried changing the TypeGuessRows in the registry settings as well as setting the same from the connection string. Still I get the truncated value in the code. I am not in a position to restructure the excel sheet or use another provider. I run 64 bit windows. My office edition is 2013. (Have a small doubt if it is because of this).

This is my connection string; it is working fine for those cells having data < 255 bytes.

var connectionString = string.Format("provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=\"Excel 12.0;IMEX=1;HDR=YES;TypeGuessRows=0;ImportMixedTypes=Text\"");

Any solutions? Thanks in advance.

Answer 1

I am also using Microsoft.ACE.OLEDB.12.0 on 64-bit Windows 7.

I found that the TypeGuessRows in the connection string has no effect.

But increasing the TypeGuessRows in the following registry location works:

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel

More info on a similar bug (although you may already know this as you're already trying to change TypeGuessRows)

Answer 2

The solution to this was extremely simple. Just change the format of the column containing this huge data to "Text" from "General" in the excel sheet.

Now I feel like a n00b.