ADO正在截断Excel数据 [英] ADO is truncating Excel data

问题描述

我有一个功能，使用ADO从工作表的内容中获取ADODB记录集，如下所示：

I have a function that gets an ADODB recordset from the contents of a worksheet using ADO, as follows:

Function WorksheetRecordset(workbookPath As String, sheetName As String) As adodb.Recordset

Dim objconnection As New adodb.Connection
Dim objrecordset As New adodb.Recordset

On Error GoTo errHandler

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H1

objconnection.CommandTimeout = 99999999

objconnection.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=" & workbookPath & ";" & _
        "Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"";"

objrecordset.Open "Select * FROM [" & sheetName & "$]", _
    objconnection, adOpenStatic, adLockOptimistic, adCmdText

If objrecordset.EOF Then
    Set WorksheetRecordset = Nothing
    Exit Function
End If

objrecordset.MoveLast
objrecordset.MoveFirst

Set WorksheetRecordset = objrecordset
Exit Function

errHandler:
Set WorksheetRecordset = Nothing

End Function

我在导入时遇到问题数字数字格式化为1位小数，但实际上有2位小数。只有在列中混合数据类型时才会发生这种情况。例如，这些值：

I'm having a problem importing number data where the numbers are formatted to 1 decimal place but they actually have 2 decimal places. This only happens if the datatype is mixed in the column. For example, these values:

0.03
0.05
0.08
0.13

当我将它设置在这个表格中的小数点后1位：

When I set them to 1 decimal place in this table:

+-------+-----------+
| value | something |
+-------+-----------+
| 0.0   | a         |
| 0.1   | a         |
| 0.1   | sda       |
| 0.1   | sdf       |
+-------+-----------+

然后记录集获取正确的2位小数位数值。但是当我把它们放在这个表中时：

then the recordset gets the correct 2 decimal place values. But when I put them in this table:

+---------+-----------+
|  value  | something |
+---------+-----------+
| asdfasd | asdfas    |
| 0.0     | a         |
| 0.1     | a         |
| 0.1     | sda       |
| 0.1     | sdf       |
+---------+-----------+

然后记录集只得到1个小数位数值，例如它拾取0.0而不是0.03。我认为这是因为第一行中的字符串导致ADO将列中的所有值视为显示的字符串。

then the recordset only gets the 1 decimal place values, e.g. it picks up "0.0" instead of "0.03". I think this is because the string in the first row is causing ADO to treat all values in the columns as strings as displayed.

有没有办法我还可以拿起文本字符串，还可以在数字值中获取正确的小数位数？

Is there a way I can still pick up the text string, but also get the correct number of decimal places in the number values?

编辑：只是注意到一些奇怪的东西。当我打开工作簿时运行这个工作簿时，记录集将得到正确的小数位。如果我在工作簿关闭时运行它，它只能显示小数位数。

Just noticed something odd. When I run this while the workbook is open, the recordset gets the correct decimal places. If I run it while the workbook is closed, it only gets the displayed decimals.

推荐答案

请尝试下面的 objRecordset 功能和查询（在MS Query with Excel中测试）：

try the below for your objRecordset features and query (tested in MS Query with Excel):

With objrecordset
    .CursorLocation = adUseClient
    .LockType = adLockOptimistic
    .CursorType = adOpenStatic
    .ActiveConnection = objconnection
    .Open "Select format(`" & sheetName & "$`.value,'0.00') as [value], something FROM [" & sheetName & "$]"
End With

所以，这里 JET SQL格式功能强制ADO的SQL Parser输出格式为 0.00

so, here the JET SQL format Function is forcing ADO's SQL Parser to output a string formatted as 0.00

此外，我已将 CursorTLocation 属性设置为 adUseClient 所以你不需要使用 MoveLast 和 MoveFirst

ALso, I have set the CursorTLocation property to adUseClient so you won't need to use MoveLast and MoveFirst

让我们知道你如何得到

Philip

这篇关于ADO正在截断Excel数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！