Even with numeric values, you may encounter unexpected difficulties when importing.
At first glance, you might think that numerical values should not cause any problems when importing. But unfortunately there are different forms of presentation depending on the country or region and the application:
1000.234567 -> without digit grouping
1 000.234567 -> with thousand separator
1 000.234 567 -> with digit grouping
USD 1000.234567 -> with currency code
1.000234567E+03 -> Scientific format
0.123 -> 3 decimal places
0.123000 -> 6 decimal places
.123 -> Omitting the zeroImporting numeric values
The following challenges may therefore arise when importing decimal numbers:
Instead of a point, a comma is expected as a decimal separator
The number of decimal places does not correspond to the specifications
Numerical values are provided with a thousand separator or digit grouping
Value must be supplied in a different unit (e.g. in millimetres instead of meters)
Numerical value contains a currency code
Decimal separator depending on country
Depending on the country, commas and periods (or fullstop) are used interchangeably as decimal separators or as digit grouping. The following number is therefore not unique:
123.567,089
This could be 123567.089 if the comma is used as a decimal separator and the period as a thousands separator. However, the number can also be interpreted as 123.567089 as soon as the comma is used as a digit grouping for decimal places.
In this case, you need to know the source or the definition of the export in order to determine the correct number format. In IT, the period has become established as the decimal separating character. Therefore, in case of doubt, the period (fullstop) should be selected as the decimal separator.
The period as decimal separator is mainly used in the English-speaking world and in Asia:
Australia
Canada
India
Ireland
Japan
Mexico
New Zealand
United States
United Kingdom
People's Republic of China
The comma as a decimal separator is often used in Europe and in countries where the language is German, French, Spanish or Russian:
Argentina
Brazil
Germany
France
Italy
Poland
Russia
Spain
South Africa
Change number format with smasi CSV-Wizard
With the CSV editor «smasi CSV-Wizard» you can easily change the number format and replace the decimal separator in the CSV file. With the 'Change numeric values' feature, you can make the following changes with just a few mouse clicks:
Change number format in the CSV file
Change the decimal separator
Set the number of decimal places
Specify whether a fixed number of decimal places should always be displayed
Remove or apply digit grouping such as thousand separators
Remove interfering currency specifications or units
Set blank values as '0' or 'NaN'
Apply the scientific format
Round floating point values to a certain number of decimal places
Convert floating point values to an integer
Convert numerical values into another unit
The «smasi CSV-Wizard» also offers the option of converting the numerical values into a different unit. If required, the values can be multiplied by a factor or added with a specific value.
For example, units of measurement can be easily converted from meters to millimeters by multiplying the existing values by 1000.
Or would you like to add an offset to the ID values and add 1 000 000 000 to all existing numbers? No problem!
Big numbers
The CSV editor «smasi CSV-Wizard» can even deal with very large numerical values without errors. It supports up to 1224 digits for integers and up to 77 digits for floating point values.
This also makes conversions with large numbers possible, where many other CSV editors fail.
Comments