When Importing an Excel sheet commas change to decimals for numbers even on string field types.


Description

In a country where commas are used rather than decimals in numbers such as 10,11 rather than 10.11 Excel will automatically be using commas in the program.

When importing this Excel sheet and the numbers go into string fields you may notice they are now decimal points rather than commas.

Even checking the staging table you can see the field has decimals rather than commas.

Release or Environment

Orlando Patch 7

New York Patch 9a

Cause

This is due to Excel formatting based on regions.

If you take this same Excel document and open it in a different region where decimals are used in the same scenario it will show with decimals.

If you save this as a CSV in a region where they should be commas it will show commas if you save this in a region which uses decimals it will show decimals.

The commas have a dynamic formatting based on region in this case and will change depending on where the document is opened.

Resolution

One option is to manually delete the commas and replace them with commas again.


Another option is to save the document as a CSV and import that.