This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(9.6, 39%, 10%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ESJ%3C/text%3E%3C/svg%3E)
I'm having trouble with select * when a field has numeric decimal data like currency. these fields return null data.
i should also point out that when i look at the data in excel it has the warning that these cells have numbers stored as text.
Oddly if i create a mirror column and use a formula to add 0 or multiple by 1 to get a numeric stored then the select picks up the actual value.
I should also make sure that one understands i am using this driver to query an excel file as a database
Which database management system do you use? MySQL，Oracle，Access, SQL Server?
this db driver allows for running sql queries against an excel file. there is no db management software involved.
It seems to be code-related, I added the office-scripts-excel-dev tag.
That's really less on informations.
How do you query data, which programming code and more, how does the connection string look like?
One option is to use extended property IMEX=1 in connection to avoid, that ACE guesses in any way the data type of the column.
See https://www.connectionstrings.com/ace-oledb-12-0/ => Treating data as text => IMEX
this is the connection string. we use a variable for the XLS file source a sample is shown below as well. I can try adding the IMEX option.
$XLSMaster$ = R:\project1\workfiles\jack.xlsx
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$sXLSMaster$;Extended Properties="Excel 12.0 Xml;HDR=YES";
From the link you provided i can try using this:
Treating data as text
Use this one when you want to treat all data in the file as text, overriding Excels column type "General" to guess what type of data is in the column.
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1";
If you want to read the column headers into the result set (using HDR=NO even though there is a header) and the column data is numeric, use IMEX=1 to avoid crash.
To always use IMEX=1 is a safer way to retrieve data for mixed data columns. Consider the scenario that one Excel file might work fine cause that file's data causes the driver to guess one data type while another file, containing other data, causes the driver to guess another data type. This can cause your app to crash.
below is a sample of the output i get when the select query is executed. I put this in ccv format to make it readable here.
1400931373,10/3/2022,NT30,11/2/2022,SH OCQ0641958,WIRE 10.3.22,06,DZ,SH,10013687,,10368,-28,PVL,NEW,10/10/2022,,35,10368
1400931373,10/3/2022,NT30,11/2/2022,SH OCQ0641958,WIRE 10.3.22,06,DZ,SH,10013687,, xxxxx,-28,PVL,NEW,10/31/2022,,35,103.68
the query: select * from [TABLE] where Text = 'SH OCQ0641958' this returns 14 records.
the 'xxxxx' was added because this currency field returns null data in the SQL statement. the excel file has the field formatted as numeric but, it also has the field error indicator "number stored as text" if i clear this error data is returned as expected. Also the last column in the data is a formula in excel that converts the currency field to a value (real numeric).
While, adding the RealCurrency Field Formula does offer a reasonable workaround. its odd that only the numeric with decimals has the problem in the SQL query (all the other numerics are returned accurately).
I have tried setting the currency field to text but, when new data is added to the file it all gets converted back to numeric. I may need to work on forcing text data using the (') apostrophe but, that really makes other calculations cumbersome.
USING the IMEX=1 option on the connect string did not help with the query results.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(150.4, 64%, 24%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EAK%3C/text%3E%3C/svg%3E)
To resolve install
1: Microsoft Access Database Engine 2010 Redistributable
Using below link.
https://www.microsoft.com/en-in/download/details.aspx?id=13255
Steps to install:
1: Open CMD in administrator privilege
276470-image.png
2: Go the installable path of 32 bit Microsoft Access Database Engine 2010 Redistributable in CMD
prompt and start the installation.
Other OLEDB driver
Microsoft Access Database Engine 2010
(Microsoft.ACE.OLEDB.12.0) https://www.microsoft.com/en us/download/details.aspx?id=13255
Microsoft Access 2013 Runtime
(Microsoft.ACE.OLEDB.15.0) https://www.microsoft.com/en us/download/details.aspx?id=39358
Microsoft Access Database Engine 2016
(Microsoft.ACE.OLEDB.16.0) https://www.microsoft.com/en us/download/details.aspx?id=54920