This how-to describes how to handle data where a numeric field uses a comma as a decimal separator. I.e. the numbers look as follows:

1123100,1265
1.123.100,1265
1 123 100,1265
1'123'100,1265

Answer

Numeric fields CSV file usually use the dot (.) as the decimal separator. If they use a thousand separator they might use a comma (,) or a space character. E.g.:

1123100.1265
1,123,100.1265
1 123 100.1265

This is the format used in many countries, especially English-speaking countries and it's normally the standard in computers.

Duco supports this format. A field using this format should be detected as "decimal" in the data input screen. Also the NRL operator "number contained in" expects this format.

We sometimes encounter files (especially in CSV files) that contain numeric data that uses the comma as a decimal separator. If they use a thousand separator, it might be a space, a dot (.) or even a single-quote character. In other words, the data looks as follows:

1123100,1265
1.123.100,1265
1 123 100,1265
1'123'100,1265

Many countries use this format, notably: France, Germany, Italy, Austria, and Central- and South-American countries. 

Duco does not currently support these formats directly. Fortunately, it's possible to make suitable adjustments in the set up of a matching process that enable to still use these number formats. At a high level, the general approach is to:

• In the data input screen, set the data set field as a "text"
• In the match fields screen, use NRL rules to transform the value to a number that uses the dot (.) as a decimal separator
• use the NRL rule "number contained in" to turn the text value into a number.

As the image below shows, when a CSV file that uses these number format is uploaded, Duco may detect the format incorrectly. For example, as "integer". If this happens, change the format to "text".

Format 1

The first format uses simply the comma as the decimal separator and does not use a thousands separator. I.e.: 1234567,9876 .

To turn a text value like this to a decimal, we can construct an NRL rule as follows:

Step-by-step instructions

1. Start the rule as follows:
2. Expand the drop-down menu as follows:
3. Expand the first drop-down menu and continue constructing the rule as follows:
4. Expand the next drop-down menu and continue constructing the rule as follows:
5. Finally, expand the last drop-down menu and finish constructing the rule as follows:
6. The fully constructed rule should look as follows:

Formats 2, 3, and 4

Formats 2, 3, and 4 use also a thousands separator. For example:

1.123.100,1265
1 123 100,1265
1'123'100,1265

In this case, we need to use an additional NRL rule to remove these characters. This can be done with the NRL rule "a [field] with the characters [text] removed".

Step-by-step instructions

1. Start the rule as follows:
2. Expand the drop-down menu as follows:
3. Expand the first drop-down menu and continue constructing the rule as follows:
4. Enter the rule and expand the first drop-down menu as follows:
5. Enter the rule to remove the thousands separator character as follows:
6. Expand the next drop-down menu and continue constructing the rule as follows:
7. Finally, expand the last drop-down menu and continue constructing the rule as follows:
8. Add the rule to remove the thousands separator characters:
9. The fully constructed rule should look as follows:

For formats 3 and 4, the steps are the same. The only difference is that the rule "a [field] with the characters [text] removed" will need to remove the space or the single-quote (') character instead. Or whichever character is used as thousands separator.

End result

The end result should look as follows in Duco:

The columns "Amount format 1/2 (orig)" show the original values in the input data and the columns "Amount format 1/2" show the values transformed to a number format.