Excel、OleDb 和前导零
我有一个使用 OleDb 将数据从 Excel 文件读取到数据集的过程。 一切都运行良好，直到我开始看到其中包含前导 0 的数据。 文本格式为“常规”或“文本”。
我已经在 OleDb 连接中设置了 IMEX=1，但我仍然在数据集中为每个前导 0 的条目获取空值。
有谁知道解决此问题的可行方法吗？
编辑
作为一些附加信息，我必须保留前导 0，并且该列的格式设置为“常规”。 我也尝试过文本。
第一个具有前导零的条目位于第 11 行，并且返回 null。 该特定工作表上的所有其他数据都是该列的数字。
I have a process that using OleDb reads data from an excel file into a DataSet. Everything was working well until I started to see data with leading 0's in it. The text is formatted as General or Text.
I have set IMEX=1 in the OleDb connection, yet I still get a null value in the dataset for each entry with a leading 0.
Does anyone know a way around this that WORKS?
Edit
As a bit of added information, I MUST keep the leading 0's and the column is formatted as General. I have also tried Text.
The first entry that has a leading zero is in row 11 and a null is returned. All other data on this specific sheet is numbers for this column.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我敢打赌，数据是以文本形式存储在 Excel 电子表格中的； 因为它本身不会有前导零（尽管单元格可以格式化以显示它们。）我希望 OleDb 正在询问 Excel 什么数据类型，而 Excel 认为它们是字符串。
查看 Excel 单元格之一，看看它顶部的数据输入条中是否有加载零； 或者它有一个前导撇号或类似的东西吗？
如果是这种情况（或者即使不是，但您想要未格式化的数据），我想到的一种方法是将工作表复制到另一个工作表并使用 Val(address) 函数转换 Excel 列，然后重新格式化它，不带前导零； 然后从中阅读。
I bet the data is stored in the Excel spreadsheet as text; because it wouldn't natively have leading zeroes (although the cells could be formatted to show them.) I expect OleDb is asking Excel what datatype, and Excel thinks they are strings.
Look at one of the Excel cells nad see if it has a loading zero in the data input strip at the top; or does it have a leading apostrophe or some such?
If ihis is the case (or even if it's not, but you want unformatted data), one way off the top of my head would be to copy the worksheet to another worksheet and transform the Excel column with the Val(address) function, and reformat it without leading zeroes; then read from that.
我能看到的最好的事情是，您必须创建一个预先格式化的 Excel 文件，然后将其用于您需要的数据。
The best thing that I can see is that, you have to create a preformatted excel file, then use it for the data that you are requiring.
您的前 10 行是纯数字数据吗？
然后驱动程序做出判断，整个列都是数字。 当它遇到不合适的数据时，它就会窒息。 您可能将其视为 NULL 或 0。
我会尝试：
1) 放弃 IMEX - 没有它的实验
2) 尝试添加单引号作为该列中所有数据的第一个字符。
这在 Excel 中“强制文本”。 作为测试，只需在前 11 行上尝试即可。
这会强制驱动程序将该列视为文本。 NULL 应该消失。
有某种方法可以增加行“样本大小”...但我不记得在连接字符串中使用的关键字。
Are your first 10 rows pure numeric data?
Then the driver is making a judgment call that the whole column is numeric. It chokes when it runs into data that doesn't fit. You are probably seeing this as NULL or 0.
I would try:
1) drop the IMEX - experiment without it
2) Try adding a single quote as the first character to all the data in that column.
This "forces text" in Excel. As a test just try it on the first 11 rows.
This should force the driver to see the column as text. The NULLs should disappear.
There is some way to increase the row "sample size"... but I can't remember the keyword to use in the connection string.