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.

Solution 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)

Solution 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.

Solution 3

The problem is that the ACE driver is inferring a TEXT data type for the column you're populating the data set from. Text columns are limited to 255 characters. You need to force it to use the MEMO data type.

Your best bet for that is to garantee that the majority of the first eight rows in that column exceed 255 characters in length.

Source

This behavior is determined by the the predictive nature of the Excel driver/provider. Since it doesn't know what the data types are, it has to make a guess based upon the data in the first several rows. If the contents of a field exceeds 255 characters, and it's in the first several rows, then the data type will be Memo, otherwise it will probably be Text (which will result in the truncation).

Comments

[Admin]

My issue is more on the lines of this article : social.msdn.microsoft.com/Forums/en-US/… , for which, there seems to be no definite solution.

[faby]

I've updated my answer with a link to another answer in SO. Let me know if it helps you

[Admin]

The source talks about fixes in case of Jet provider. In my case, I am using ACE provider. How do i force use MEMO data type? And this cell which exceeds 255 characters is in the 7th row. There is another cell beside this cell which also has more than 255 characters. While I insert the contents into a dataset, I get exactly the same values for both the cells; each truncated to 255 characters.

[Admin]

I am using office 15.0 and I have already changed the TypeGuessRows value to 0 from 8. And it didn't work.

[faby]

did you change ImportMixedTypes to Text?

[Admin]

Yes I did. If you notice the connection string, I have appended the same to it as well.

[faby]

you should change that value in the registry key too

[bastos.sergio]

The solution applies both to the Jet and Ace provider. To force the Memo type, the majority of the first 8 rows in the excel file must contain a value which exceeds 255 characters.

[Alex Meyer]

This is the correct key to change for 64 bit machines, thanks for your solution aximili!

[ctorx]

Not sure why this was down voted, but this actually solved the problem in my case. I believe the registry change might also be needed, but in my case, I was limited to 251 characters until I formatted the column in question as TEXT instead of GENERAL.

[Sadik Ali]

Thanks very much :) its works for .xls extension file i am looking for this since last day

[Sadik Ali]

hi can you please tell me what steps for .xlsx extension file?

[Yuriy Galanter]

Thank you. Had different problem - a large text field wouldn't show at all. Changing type to General made it appear.