top of page
Bücherregal als Symbolbild für den Blogbeitrag.

Blog

Numbers may vary

Even with numeric values, you may encounter unexpected difficulties when importing.


Zahlenreihe


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 zero

Importing 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.



 

Weitere Informationen

Recent Posts

Comments


bottom of page