Reading a non-rounded value from Excel cells using OPENDATASOURCE

The behavior you're seeing is due to how OLE DB providers (like Microsoft.ACE.OLEDB.12.0) retrieve formatted data rather than the raw cell value from Excel when using OPENDATASOURCE. If the cell is formatted to show only one decimal place, the provider reads the formatted display value, which is already rounded (e.g., 10,5 instead of the actual value 10,45).

The Microsoft.ACE.OLEDB.12.0 provider respects the formatting and often reads the value as displayed — especially when IMEX=1 (Import Mixed Types), which further instructs the provider to interpret values as text or based on formatting.

Suggested approach:

1. Use IMEX=0 (Force reading as native values, not as formatted). Try using IMEX=0 instead of IMEX=1, which may cause the OLEDB provider to read the underlying value (not formatted).

This sometimes works if the column is consistently numeric.

SELECT 
    REPLACE(F1, ',', '.') 
FROM OPENDATASOURCE(
    'Microsoft.ACE.OLEDB.12.0',
    'Data Source=TheExcelPathIsHere;Extended Properties="Excel 12.0;HDR=No;IMEX=0"'
)...['NameOfTheSheetInTheExcelFile$']

2. Convert the Excel file to an Excel XML format (.xlsx) and read it using a Linked Server and OPENROWSET, which can sometimes return more raw values.

3. If you cannot change the formatting, but can add a new column, you could use an Excel formula like "=TEXT(A1, "0.00")". This forces the actual value to be presented as text and unrounded. Then read that column from T-SQL.