vba 使用 Microsoft.Ace.Oledb.12.0 提供程序时数据在 255 字节后被截断
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24865183/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Data truncated after 255 bytes while using Microsoft.Ace.Oledb.12.0 provider
提问by n0thing
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).
我正在使用 ACE 提供程序读取 Excel 工作表，并且某些单元格包含大于 255 字节的数据。我尝试更改注册表设置中的 TypeGuessRows 以及从连接字符串设置相同的内容。我仍然在代码中得到了截断的值。我无法重组 Excel 表或使用其他提供商。我运行 64 位窗口。我的办公版是 2013 年的。（有一点怀疑是不是因为这个）。
This is my connection string; it is working fine for those cells having data < 255 bytes.
这是我的连接字符串；对于那些数据小于 255 字节的单元格，它工作正常。
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.
任何解决方案？提前致谢。
回答by Aximili
I am also using Microsoft.ACE.OLEDB.12.0on 64-bit Windows 7.
我也在Microsoft.ACE.OLEDB.12.064 位 Windows 7 上使用。
I found that the TypeGuessRowsin the connection string has no effect.
我发现TypeGuessRows连接字符串中的 没有效果。
But increasing the TypeGuessRowsin the following registry location works:
但是TypeGuessRows在以下注册表位置增加 有效：
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel
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)
有关类似错误的更多信息（尽管您可能已经知道这一点，因为您已经在尝试更改 TypeGuessRows）
回答by n0thing
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.
解决这个问题的方法非常简单。只需将包含此庞大数据的列的格式从 Excel 表中的“常规”更改为“文本”。
Now I feel like a n00b.
现在我觉得自己像个n00b。
回答by bastos.sergio
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.
问题是 ACE 驱动程序正在为您从中填充数据集的列推断 TEXT 数据类型。文本列限制为 255 个字符。您需要强制它使用 MEMO 数据类型。
Your best bet for that is to garantee that the majority of the first eight rowsin that column exceed 255 characters in length.
最好的办法是保证该列的前八行中的大多数的长度超过 255 个字符。
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).
此行为由 Excel 驱动程序/提供程序的预测性质决定。由于它不知道数据类型是什么，它必须根据前几行中的数据进行猜测。如果字段的内容超过 255 个字符，并且位于前几行，则数据类型为 Memo，否则可能为 Text（这将导致截断）。
回答by faby
refer this link. I think this is the problem (try with Memo fields)
参考这个链接。我认为这是问题所在（尝试使用备注字段）
http://allenbrowne.com/ser-63.html
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)
在 Access 表中，文本字段限制为 255 个字符，但备注字段可以处理 64,000 个字符（约 8 页的单行距文本）
Nice workaround: have a look at thisstack answer
不错的解决方法：看看这个堆栈答案
回答by mxix
Excel has some limits.
Excel 有一些限制。
Excel specifications and limits - 2013
As you can see in the link posted:
正如您在发布的链接中看到的：
Feature Maximum Limit Column width 255 characters
功能 最大限制 列宽 255 个字符
