Skip to content

Privacy and preparation

International dataset numeric format errors: checking decimals and units

By SourceX Editorial · Updated

9 sections

Short answer

Validate decimal separators, commas and units column by column: declare each numeric column's source locale, test every value against that locale's decimal and grouping symbols, flag values that read differently under another convention, keep the original string beside the converted number, and require explicit unit and currency codes. Most locale errors still parse as valid numbers, so a parse check alone will pass corrupted data.

Countries and languages

International dataset

This page covers international dataset numeric format errors: checking decimals and units.Illustrative

Key takeaways

  • The same digits mean different numbers under different locales, and most wrong readings still parse cleanly.
  • Declare a source locale for every numeric column, then test each value against it before converting.
  • Values with exactly three digits after a single separator are ambiguous; resolve them with a source report or hold them back.
  • Units and currencies need explicit codes, because a correct number with a lost unit is still wrong.
  • Deliver the original string, the declared locale and a conversion log with every converted value.

How do you validate decimal separators, commas and units in international datasets?

Validate them column by column, not file by file: record the source locale of each numeric column, test every value against that locale's decimal and grouping symbols, flag values that read differently under another plausible locale, keep the original string next to the converted number, and require an explicit unit and currency code for every measured or monetary field. Convert only after a column passes.

The reason is that most numeric format errors produce a valid number. A German export that writes 1.234 for one thousand two hundred thirty-four parses without complaint as one point two three four under U.S. rules. Nothing fails, the column loads, and the totals are wrong by a factor of a thousand. A validation step that only checks whether a value parses will pass the corrupted data.

This guide gives a checklist a data team can run before an international dataset leaves the company, a worked example with an exception, and the assumptions that can reverse a column's reading. Dates have the same problem and deserve their own treatment; this page covers numbers, units and currencies.

Why the same digits mean different numbers from one office to the next

Different locales use opposite symbols for the decimal mark and the thousands separator, so identical characters carry different values. The Unicode Common Locale Data Repository (CLDR), the locale data that most operating systems, browsers and libraries draw on, records these symbols for each locale.

In CLDR's German data the decimal symbol is a comma and the group symbol is a period. French uses a comma for decimals and a narrow no-break space (U+202F) to group digits, a character that looks like a space but is not one and often survives copy and paste into exports. CLDR's English (India) data uses the grouping pattern #,##,##0.###, which groups the last three digits and then every two, the lakh and crore style. The table shows the same quantity written under each convention, built from those CLDR symbols.

Why the same digits mean different numbers from one office to the next
Locale (CLDR)Decimal markGroup separator1234567.89 written as
English (United States)periodcomma1,234,567.89
German (Germany)commaperiod1.234.567,89
French (France)commanarrow no-break space U+202F1 234 567,89
English (India)periodcomma, grouped 3 then 212,34,567.89

Why parsers and standards will not catch the error for you

Neither parsing libraries nor measurement standards resolve the ambiguity, so the decision has to be made and recorded by people who know the source system. Libraries need to be told the locale. In pandas, read_csv takes a thousands parameter that is optional and a decimal parameter that defaults to a period, with the documentation suggesting a comma for European data. Leave them unset on a German file and grouped integers either stay as text or, worse, parse as small decimals.

Standards allow both conventions. The General Conference on Weights and Measures resolved in 2003 that the decimal marker may be either the point on the line or the comma on the line. NIST Special Publication 811 recommends the point for U.S. use and groups digits in threes with a thin space rather than a comma, precisely because the comma is a decimal marker elsewhere. Correct documents from two countries can therefore disagree, and both be right.

File formats add a third trap. RFC 4180, the informational description of CSV, says fields containing commas should be enclosed in double quotes. A decimal-comma value in a comma-delimited file must be quoted, or the exporter switches to another delimiter. When neither happens, 12,5 splits into two columns and every column to its right shifts by one.

Reading a column's locale from its own values

Most columns reveal their locale if you look at all their values together rather than one at a time. A value with both a period and a comma shows its convention by which symbol comes last. A value with one separator followed by exactly three digits is ambiguous; any value in the same column with one, two or four digits after the separator usually is not. Columns of whole quantities, such as units shipped, often contain no decimal part at all, which is what makes them dangerous.

Use the signals below to assign each column a verdict. Record the evidence for the verdict, because the person reviewing the dataset later needs to see why a column was read one way.

Run the inference on every value in the column, not a sample. Ambiguous values often cluster in one period or one source office, so a sample drawn from the first thousand rows can miss them entirely.

Reading a column's locale from its own values
Pattern seen in the columnPossible readingsHow to resolveVerdict
Both separators present, comma last (1.234,56)Decimal commaConfirm against source system locale settingConvert as decimal comma
Both separators present, period last (1,234.56)Decimal pointConfirm against source system locale settingConvert as decimal point
Single separator, never three trailing digits (12,5 or 3,75)Decimal commaCheck field meaning and rangeConvert as decimal comma
Single separator, always exactly three trailing digits (1.250)Grouping or three decimal placesAsk the system owner; compare with a total or a printed reportHold until confirmed
Space or U+202F inside digits (1 250,5)French-style groupingNormalize the space character, then apply decimal ruleConvert after normalization
Mixed patterns in one columnRows from different offices or a re-saved fileSplit by source office or file, then judge each partHold the mixed part

The locale formatting validation checklist

The checklist is the asset: run each check per numeric column, keep the result with the dataset, and do not convert a column until every applicable check passes or a named owner signs off on an exception. Checks are written so a script can run most of them and a reviewer can confirm the rest.

At a glance

The locale formatting validation checklist

Run N1 to N5 before any conversion and N6 to N10 after it. The first group tells you whether a column can be read at all; the second proves the reading was right and leaves a trail. If N6 fails after conversion, revisit the locale verdict before suspecting the source data, because a wrong locale is the most common cause of a reconciliation gap.

Thresholds are a team decision. Some teams accept a small number of non-conforming values in N2 when each is listed and corrected by hand; others reject the column. Whatever the rule, write it down before running the checks, so the result cannot be argued into a pass afterward.

The locale formatting validation checklist
CheckWhat to testPass ruleEvidence to keep
N1 source locale declaredEach numeric column has a recorded source locale and systemLocale code and system name filled for every columnData dictionary row
N2 symbol conformanceEvery value matches the declared decimal and group symbolsZero values with a symbol the locale does not useCount of non-conforming values
N3 ambiguity scanValues that read differently under the other common conventionZero ambiguous values, or a recorded resolutionList of ambiguous value patterns and the resolution
N4 hidden charactersNon-breaking, narrow and thin spaces, apostrophes, currency signs inside numbersAll normalized by a documented ruleBefore and after counts per character
N5 delimiter integrityRow field counts in delimited filesEvery row has the header's field countRows with extra fields, by line number
N6 range and magnitudeConverted values against known ranges and source totalsTotals reconcile to a source report within an agreed toleranceReconciliation figures
N7 unit declaredEvery measured field has a unit code, per field or per rowNo measured value without a unitUnit column or dictionary entry
N8 currency declaredEvery monetary field has an ISO 4217 codeNo amount without a currency codeCurrency column or dictionary entry
N9 original preservedOriginal string kept beside the converted valueBoth present for every converted fieldField pair in schema
N10 conversion loggedRule, version and person for every conversionOne log entry per columnConversion log file

Units and currencies: the errors that parse cleanly

Units and currencies cause the costliest numeric errors because the number itself is correct and only its meaning is lost. NASA's Mars Climate Orbiter mishap board found that ground software supplied thruster performance data in English units where the navigation software expected metric units, and the board named that failed translation as the root cause of losing the spacecraft. Every value parsed; the meaning did not travel with it.

Business exports have the same weakness at a smaller scale: a weight column in kilograms from one site and pounds from another, a volume field where gallons means one thing in one country and another elsewhere, a price list that switched currency mid-year. The fix is to make meaning explicit in the data. The Unified Code for Units of Measure (UCUM) was built for unambiguous electronic communication of quantities with their units and is a good default vocabulary for a unit column. ISO 4217 gives each currency a three-letter alphabetic code and a three-digit numeric code, and records how its minor unit divides, so a field holding 1250 is readable only next to a code such as USD or EUR.

Never infer a unit from a column name alone. Names like weight or qty_kg are written once and outlive the system changes that make them wrong.

Treat a unit change as an event with a date. When a site moves from pounds to kilograms, record the change date and apply the unit per row, not per column, so records on both sides of the change stay correct.

Worked example (illustrative): one parts export from three warehouses

This example is illustrative and invented for this guide. A distributor combines parts usage exports from warehouses in Ohio, Bavaria and Pune into one dataset. Each export has a quantity column, a unit price column and a weight column. The data team runs the checklist before converting anything.

The Ohio file passes every check: period decimals, comma grouping, prices in USD, weights in pounds declared in the dictionary. The Bavaria file uses comma decimals and period grouping; N2 passes once its locale is declared as German, and N8 flags that its prices carry no currency column, which the warehouse confirms is EUR. The Pune file passes N2 under English (India), and N6 catches a reconciliation gap until the team notices that values above one lakh were grouped 3 then 2 and the first script had stripped commas only in groups of three.

The exception is the Bavaria quantity column. About 4 percent of its rows, an illustrative share, contain values like 1.250 and nothing else in the column has a decimal part. Under German rules that is one thousand two hundred fifty units; under U.S. rules it is one and a quarter. The warehouse's printed monthly report settles it as grouping for most rows, but a block of rows exported from a different system cannot be matched to any report. Those rows are held back, documented in the exception list and excluded from the delivered dataset rather than guessed.

The team's records for this dataset are the checklist results per file, the conversion log, the exception list and the reconciliation figures. Together they let a reviewer see why each column was read the way it was, which matters more than the conversion itself when the dataset is reviewed months later.

Which assumption can reverse a whole column?

The assumption that most often reverses a column is that one file has one locale. Locale can be set per user, per workstation, per application and per export template, so two people exporting the same report can produce different formats. A spreadsheet opened and re-saved on a machine with another locale can also rewrite numbers silently.

Test the other assumptions in the list below explicitly as well.

When you find any of these assumptions broken, recheck every column from the same source, not just the one that failed. A locale problem in quantities usually means the same export template wrote prices and weights the same way.

  • That a column of whole numbers is safe. Integer quantities with three-digit groups are the most ambiguous values there are.
  • That the office's country decides the locale. A U.S. subsidiary may run a parent company's European ERP configuration, and a European office may export in an English template.
  • That the unit never changed. Sites switch scales, suppliers and systems; confirm the unit for each period, not once for the file.
  • That a currency symbol identifies a currency. A dollar sign appears on several currencies; only a code is unambiguous.
  • That English text means U.S. formatting. English-language records from India, the UK, Singapore or South Africa can follow their own number, date and unit conventions.
  • That rounding is harmless. Converting through floating point or trimming decimals can change totals that a buyer will try to reconcile.

What to ship with converted numbers, and how this fits an international dataset review

Ship each converted value with its original string, the declared source locale, the unit code, the currency code where relevant, and a conversion log naming the rule and version used. Keep the exception list with the data. A buyer who can see the original and the rule can audit a conversion; a buyer who sees only the result has to trust it.

Stop and ask for specialist review when a column's reading changes a material total and no source report can confirm it, when units changed during the period and the change date is unknown, or when records come from a jurisdiction whose rules on cross-border transfer you have not checked. Those are decisions for the system owner, a localization or data specialist and, where personal data is involved, a privacy reviewer.

For companies weighing a data license, language also decides SourceX eligibility, and it is measured on the data rather than assumed from the address. Its rule starts with size and location: U.S., Canadian and EU companies qualify when they had at least 50 full-time employees at their peak, contractors not counted. A company based in the UK, Australia, New Zealand, India, Singapore, South Africa, Latin America, the UAE or Saudi Arabia can be considered too, provided at least 90 percent of the records it offers are in English, a share measured on those records rather than inferred from the company's country or working language. Every dataset still goes through rights, privacy, quality and buyer-fit review, and the first step is a short company assessment with no data upload.

Frequently asked questions

What should a locale formatting validation checklist include?

At least a declared source locale per numeric column, a symbol conformance test, an ambiguity scan for values that read differently under another convention, normalization of hidden space characters, delimiter integrity, reconciliation to a source total, declared units and currency codes, preserved original strings and a conversion log. Each check should leave evidence a reviewer can read.

How can I tell if 1.250 means one thousand two hundred fifty or one and a quarter?

Look at the rest of the column and the source system. If other values have one, two or four digits after the separator, the separator is a decimal mark; if every value has exactly three, compare with a printed report or a known total, or ask the system owner. If nothing settles it, hold the rows back instead of guessing.

Which language, date, unit or jurisdiction assumption could reverse the answer?

Most often the assumption that one file has one locale, since locale can be set per user, application or export template. Others are that the country decides the format, that units never changed during the period, that a currency symbol identifies a currency and that English-language records follow U.S. conventions.

Should I convert numbers before delivery or deliver the originals?

Deliver both. Keep the original string, the declared locale, the unit and the currency code next to the converted value, plus a log of the conversion rule. That lets a buyer audit or redo the conversion without going back to your systems.

Do pandas or Excel detect the locale automatically?

Do not rely on it. In pandas, read_csv uses a period as the default decimal character and needs the thousands and decimal parameters set for other conventions. Spreadsheet behavior depends on the locale of the machine opening the file, which is exactly the variable you are trying to control.

Sources

  • CLDR's German locale data uses a comma as the decimal symbol and a period as the group symbol. Source
  • CLDR's French locale data uses a comma as the decimal symbol and a narrow no-break space (U+202F) to group digits. Source
  • CLDR's English (India) locale data uses the decimal pattern #,##,##0.###, grouping digits 3 then 2. Source
  • pandas read_csv has an optional thousands parameter and a decimal parameter that defaults to a period, with a comma suggested for European data. Source
  • The CGPM's 2003 Resolution 10 allows either the point on the line or the comma on the line as the decimal marker. Source
  • NIST SP 811 recommends the dot on the line as the U.S. decimal marker and grouping digits in threes with a thin space instead of commas. Source
  • RFC 4180, an informational document, says CSV fields containing commas should be enclosed in double quotes. Source
  • UCUM is a code system for units of measure designed for unambiguous electronic communication of quantities with their units. Source
  • ISO 4217 assigns each currency a three-letter alphabetic code and a three-digit numeric code and records how its minor unit divides. Source
  • NASA's Mars Climate Orbiter mishap board identified thruster data supplied in English units instead of metric units as the root cause of the loss. Source

See if your company qualifies

A short company assessment. No data uploads are needed.

See if your company qualifies