Microsoft.ACE.OLEDB.12.0 doesn't get the right value in Excel file

[odaboval]

Hello ,

This is incredible.
We've got a process that read Excel file using a SQL query on Microsoft.ACE.OLEDB.12.0.

In the Excel file, the cell's value = 0,5
In the SQL query, the value = 1

I have tried to save the Excel file as a CSV, and I can see the "0,5".

We don't understand, we have no clue !!!

Here is the query :
SELECT activite.F9
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;HDR=YES;IMEX=1;Database=C:\GammaWeb_TMD\Imports\RMA\Arch\RMA201011_Test.xls',
'SELECT F1,F9 FROM [Activité mensuelle$B20:AJ23]') as activite
where F1 like 'F%'


Do you have any advice,
Thanks in advance,
Olivier

[si_the_geek]

Welcome to VBForums

This kind of issue is common when using a non-database as a database, because non-database files do not have the same level of structure as a database.

With a database each column in a table has a particular data type assigned to it, but that is not the case for non-database files. As such, the database engine (in this case the ACE provider) needs to make an educated guess about what data type each column should be, which can often be wrong - and thus cause errors, and/or "incorrect" values as is happening here.


There are various ways to deal with it, the best of which is to switch to an actual database of some sort (such as an .accdb/.mdb file, or SQL Server Express).

If that isn't an option, the most reliable method (but still not guaranteed) is to add a fake row of data to the "top" of the file (which contains values that can only be stored in your ideal data type), which you then ignore in all of your queries etc.

There are other options, but they are less reliable and they tend to be machine specific (which means your changes can affect [eg: break] other programs, and those programs can change the settings to affect your program).

[odaboval]

That's very helpful.
I didn't know this was common issue.

Welcome to VBForums

This kind of issue is common when using a non-database as a database, because non-database files do not have the same level of structure as a database.

With a database each column in a table has a particular data type assigned to it, but that is not the case for non-database files. As such, the database engine (in this case the ACE provider) needs to make an educated guess about what data type each column should be, which can often be wrong - and thus cause errors, and/or "incorrect" values as is happening here.


There are various ways to deal with it, the best of which is to switch to an actual database of some sort (such as an .accdb/.mdb file, or SQL Server Express).

If that isn't an option, the most reliable method (but still not guaranteed) is to add a fake row of data to the "top" of the file (which contains values that can only be stored in your ideal data type), which you then ignore in all of your queries etc.

There are other options, but they are less reliable and they tend to be machine specific (which means your changes can affect [eg: break] other programs, and those programs can change the settings to affect your program).

[jmcilhinney]

StG is quite correct, but there is another option when reading Excel or other data files using Jet or ACE, if you have access to the Registry on the machine doing the reading. By default, ACE and Jet read the first 8 rows of data and use that to decide the data type of each column. If the data in those rows of a column is all of the same type then that type is assumed, otherwise it will assume text by default. Any data in the remaining rows that doesn't match those data types will either be massaged or ignored.

You can change that value of 8 rows if you want. You can set a higher value or you can use zero to specify that all rows are used. This will slow down the reading process but it helps to avoid issues like you're encountering. On a 64-bit machine with Office 2010 installed, the key of interest is:

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel

You would omit the Wow6432Node for 32-bit systems and you would use 12.0 for Office 2007. Under that key is a value named TypeGuessRows, which is set to 8 by default. That's the value you should change.