5

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.

Rowland Shaw
  • 37,700
  • 14
  • 97
  • 166
n0thing
  • 99
  • 1
  • 1
  • 4
  • 3
    Historically Excel was limited to 255 characters per cell, perhaps this is the root cause? – DavidG Jul 21 '14 at 12:35
  • Did you try removing the `ImportMixedTypes=Text` part? – Steve Jul 21 '14 at 12:37
  • @Steve : Yes I did. Still getting the truncated value. – n0thing Jul 21 '14 at 12:41
  • Well, I have read your requirement about not changing the Excel file, but just for testing, what happen if you add a row at the beginning of the sheet with more than 255 chars? Are you able to read also the following rows as memo fields instead of text fields? – Steve Jul 21 '14 at 12:44
  • Sorry but I do not know what a MEMO field is. I have two adjacent cells in the seventh row, having more than 255 characters, which, while reading from the code gets truncated. Apparently I am getting exactly the same truncated values for both of these cells (which contains the same data). – n0thing Jul 21 '14 at 13:09

5 Answers5

5

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)

Aximili
  • 28,626
  • 56
  • 157
  • 216
4

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.

n0thing
  • 99
  • 1
  • 1
  • 4
  • 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. – ctorx Jul 13 '16 at 21:26
  • Thank you. Had different problem - a large text field wouldn't show at all. Changing type to General made it appear. – Yuriy Galanter Dec 14 '17 at 17:10
2

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

bastos.sergio
  • 6,684
  • 4
  • 26
  • 36
  • 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. – n0thing Jul 21 '14 at 13:00
  • 1
    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.** – bastos.sergio Jul 21 '14 at 13:13
1

refer this link. I think this is the problem (try with Memo fields)

http://allenbrowne.com/ser-63.html

In Access tables, Text fields are limited to 255 characters,but Memo fields can handle 64,000 characters (about 8 pages of single-spaced text)

Nice workaround: have a look at this stack answer

Community
  • 1
  • 1
faby
  • 7,394
  • 3
  • 27
  • 44
  • My issue is more on the lines of this article : http://social.msdn.microsoft.com/Forums/en-US/40f62ac7-7a14-44a7-9e38-115fde4f0e66/truncate-at-255-characters-with-xlsx-files?forum=adodotnetdataproviders , for which, there seems to be no definite solution. – n0thing Jul 21 '14 at 12:48
  • I've updated my answer with a link to another answer in SO. Let me know if it helps you – faby Jul 21 '14 at 12:57
  • I am using office 15.0 and I have already changed the TypeGuessRows value to 0 from 8. And it didn't work. – n0thing Jul 21 '14 at 13:03
  • did you change `ImportMixedTypes` to `Text`? – faby Jul 21 '14 at 13:10
  • Yes I did. If you notice the connection string, I have appended the same to it as well. – n0thing Jul 21 '14 at 13:12
  • you should change that value in the registry key too – faby Jul 21 '14 at 13:13
0

Excel has some limits.

Excel specifications and limits - 2013

As you can see in the link posted:

Feature Maximum Limit Column width 255 characters

mxix
  • 3,539
  • 1
  • 16
  • 23