>AFAIK Excel does not use/store a currency data type, but uses only certain formating options to display numbers.
>So multiplying by 10000 or even higher should move the decmal point far enough to the right for import -
>database currency data format is meant to support large amounts with a bit of leeway in decimal places (8 byte, 4 decimal places).
>
>To fit into decimal data type and "mindset" you should analyze how you reached the 5 decimal places - always keeping in mind to divide at last minute as you'd need to do with the import "trick" is a band-aided crutch and should be eliminated ASAP.

We do not have any control on the incoming data sources. A column, formatted for currency display, is sometimes containing data in the column such as 0.13077, 434, 123.34, 4.12121 and 134.99. We thus need to preserve the real value up to the database. Otherwise, total amount of that row (unit cost * quantity) in Excel would not be the same as what we would obtain from the database when showing the report in Power BI.

We believe that this engine recognizes a currency formatted value from Excel and binds this to the money type of SQL Server. This would then be limited to 4 decimals.

However, in our case, it would surely be nice to preserve the real values. For those exception files, we might look at an intermediate process to change the Excel cell format to Number before importing the data.

Thanks for the reply, this is very useful.