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
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.
| Locale (CLDR) | Decimal mark | Group separator | 1234567.89 written as |
|---|---|---|---|
| English (United States) | period | comma | 1,234,567.89 |
| German (Germany) | comma | period | 1.234.567,89 |
| French (France) | comma | narrow no-break space U+202F | 1 234 567,89 |
| English (India) | period | comma, grouped 3 then 2 | 12,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.
| Pattern seen in the column | Possible readings | How to resolve | Verdict |
|---|---|---|---|
| Both separators present, comma last (1.234,56) | Decimal comma | Confirm against source system locale setting | Convert as decimal comma |
| Both separators present, period last (1,234.56) | Decimal point | Confirm against source system locale setting | Convert as decimal point |
| Single separator, never three trailing digits (12,5 or 3,75) | Decimal comma | Check field meaning and range | Convert as decimal comma |
| Single separator, always exactly three trailing digits (1.250) | Grouping or three decimal places | Ask the system owner; compare with a total or a printed report | Hold until confirmed |
| Space or U+202F inside digits (1 250,5) | French-style grouping | Normalize the space character, then apply decimal rule | Convert after normalization |
| Mixed patterns in one column | Rows from different offices or a re-saved file | Split by source office or file, then judge each part | Hold 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.
| Check | What to test | Pass rule | Evidence to keep |
|---|---|---|---|
| N1 source locale declared | Each numeric column has a recorded source locale and system | Locale code and system name filled for every column | Data dictionary row |
| N2 symbol conformance | Every value matches the declared decimal and group symbols | Zero values with a symbol the locale does not use | Count of non-conforming values |
| N3 ambiguity scan | Values that read differently under the other common convention | Zero ambiguous values, or a recorded resolution | List of ambiguous value patterns and the resolution |
| N4 hidden characters | Non-breaking, narrow and thin spaces, apostrophes, currency signs inside numbers | All normalized by a documented rule | Before and after counts per character |
| N5 delimiter integrity | Row field counts in delimited files | Every row has the header's field count | Rows with extra fields, by line number |
| N6 range and magnitude | Converted values against known ranges and source totals | Totals reconcile to a source report within an agreed tolerance | Reconciliation figures |
| N7 unit declared | Every measured field has a unit code, per field or per row | No measured value without a unit | Unit column or dictionary entry |
| N8 currency declared | Every monetary field has an ISO 4217 code | No amount without a currency code | Currency column or dictionary entry |
| N9 original preserved | Original string kept beside the converted value | Both present for every converted field | Field pair in schema |
| N10 conversion logged | Rule, version and person for every conversion | One log entry per column | Conversion 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.