ADO truncates Excel data

Question

I have a function that gets a set of ADODB records 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 have a problem with importing numeric data, where the numbers are formatted to 1 decimal point, but in fact they have 2 decimal places. This only happens if the data type is mixed in a column. For example, these values ​​are:

 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 values ​​in 2 decimal places. 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 decimal places, for example. he chooses "0.0" instead of "0.03". I think this is because the row in the first row causes ADO to treat all the values ​​in the columns as rows displayed.

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

EDITOR: Just noticed something strange. When I run this while the workbook is open, the recordset gets the correct decimal places. If I run it while the book is closed, it receives only decimal places.

Answer 1

try to execute the objRecordset functions below and query (verified 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 causes the ADO SQL Parser to output a string formatted as 0.00

In addition, I set the CursorTLocation property to adUseClient , so you do not need to use MoveLast and MoveFirst

let us know how you get on

Philip

Answer 2

Unfortunately, I had the same problem before, and the reason is that the ACE driver looks only at the first value in the column to determine the data type for the entire column. This way you can try to sort data with numerical values ​​at the top.

The Gold Standard is the way to create joined tables in Excel - using vLookup. I would suggest doing this, although it may seem a bit "amateur".

Also, it seems that setting IMEX to 1 basically causes ACE to return textual representations so that the alpha-numeric values ​​in the numeric column are not returned as null.