ADO is truncating Excel data

Question

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

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

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       |
+-------+-----------+

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       |
+---------+-----------+

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?

EDIT: 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.

Are you calling the code from workbook or any other application ? — Santosh, May 22 '13 at 00:48
If this is Excel-VBA, why not consider leveraging the Excel object model and just open the source workbook? You can turn the cursor into a hourglass and turn off screen updating while you do that, so the user doesn't "see" the workbook getting opened and then closed. Turn screen updating back on *on all exit paths - especially error exit paths* to avoid supporting users saying "my Excel just froze". — Mathieu Guindon, May 22 '13 at 00:52
@retailcoder, I'm using ADO because I have to loop through 15000 workbooks and it's a) a lot faster and b) less prone to crashing if I do it this way instead of opening each workbook. — sigil, May 22 '13 at 01:00
@sigil Even i believe the reason `the string in the first row is causing ADO to treat all values in the columns as strings as displayed`. You may correct the data. — Santosh, May 22 '13 at 01:07
15000 workbooks, really?! +1 and good luck! As @Santosh has just mentioned, the problem is that ADO needs to treat your data as if it were a data table, with only a single data type allowed in a given column. What if you formatted the column to display 2 or 3 decimals? You could then treat everything as a string and `TryParse` as needed. But over 15000 workbooks you might get a performance hit that you're not interested in... — Mathieu Guindon, May 22 '13 at 01:12
@sigil You may find this link helpful http://stackoverflow.com/questions/3595131/i-need-a-workaround-for-excel-guessing-data-types-problem — Santosh, May 22 '13 at 01:20
@sigil: try my answer below, I think even though you have `IMEX=1` specified in your connection string, you will need to use column functions to get what you want. — Our Man in Bananas, May 22 '13 at 13:37
@retailcoder, wouldn't formatting the column would require opening the workbook, which I'm trying to avoid (see my previous comment)? — sigil, May 22 '13 at 16:16

Our Man in Bananas · Answer 1 · 2013-05-22T14:20:49.153

1

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

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

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

let us know how you get on

Philip

edited May 22 '13 at 14:20

answered May 22 '13 at 13:36

Our Man in Bananas

5,809
21
91
148

That didn't work. All it did was add a 0 to the end of the truncated value: "0.00","0.10", etc. Also, the name of the column changes from worksheet to worksheet; it's usually but not always at the same column index. So I need a way to get the actual value (as opposed to the display value) for every column. – sigil May 22 '13 at 16:14
Note that I do get the correct value if I run this while the workbook is open. But I need to run it while the book is closed. – sigil May 22 '13 at 16:18

score 0 · Answer 2 · answered Sep 19 '14 at 17:27

Unfortuately I had the same problem once before and the reason is that the ACE driver only looks at the first value in a column to decide the data type for that entire column. So, you can try to sort the data with the numeric values on top.

The "Gold Standard" way to create joined tables in Excel is with vLookup. I would suggest doing this even though it may seem a bit "amateur."

Also, it appears that setting IMEX to 1 basically forces ACE to return the text representations so that alpha-numeric values in a numeric column are not returned as null.

ADO is truncating Excel data

2 Answers2

Linked