Jet Engine - 255 个字符截断
我需要将 Excel 电子表格导入到我的程序中,并具有以下代码:(
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$]";
注意,上面的代码不是真正的代码,但应该让您看到我在做什么)
我正在导入文件,唯一的问题是Excel 工作表中任何超过 255 个字符的列都将被截断。
有什么办法可以解决这种情况吗?
我在某处读到,如果您确保前 8 行内的列中有一长行文本,那么它将被视为备注字段,因此不会被截断,但这似乎不起作用。
有什么想法吗?
格雷姆
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
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(4)
碰到这个就好几次了。幸运的是,有一个注册表黑客需要修复,MSDN 上对此进行了描述:http://support.microsoft.com/kb/ 189897
实际上,Excel 仅查看前八行数据来确定列的长度。如果长度为 255 个字符或更少,则默认值为 255。我上面引用的 MSDN 文章解释了如何添加注册表项“
TypeGuessRows
”,该注册表项告诉 Excel 要扫描多少行以确定列长度。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.也许您的问题有一个更简单的解决方案,但作为最后的手段,请尝试将 Excel 文件另存为 CSV 文本文件,然后使用常规文件和字符串操作类而不是 JET 引擎对其进行处理。
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.
因为我找不到我需要的确切答案,所以我将把它留在这里,以防它对任何人有帮助。
来源
Because I couldn't find the exact answer I needed, I'm going to leave this here in case it helps anyone.
Source
通常最好导入到现有表中。通过代码创建合适的表并不太困难。
It is usually best to import into an existing table. It is not too difficult to create a suitable table via code.