How to Normalize Currency Codes and Symbols During Import

ISO 4217 gives every currency it carries a three-letter code so that nothing else has to identify it. A file carries that code sometimes. The rest of the time it carries a sign, a name or a numeric code, because a person copied what the receipt printed. The accounting system and the payment API ask for the code, and something has to stand between the two.

Twelve travel claims came back from twelve cities, and the currency column came back twelve different ways. One traveller wrote €. One wrote Yen. One wrote 32, because the numeric code for the Argentine peso is 032 and the spreadsheet that opened the file read it as a number.

The table behind the register holds three letters.

1Claim,Traveller,City,Currency,Amount2EXP-2041,R. Almeida,Lisbon,€,214.503EXP-2042,H. Nakagawa,Tokyo,Yen,318004EXP-2043,P. Svoboda,Prague,Kč,46205EXP-2044,L. Brunner,Zurich,SFr.,388.206EXP-2045,V. Sosa,Buenos Aires,32,1429007EXP-2046,W. Otieno,Nairobi,KES,184008EXP-2047,C. Rossi,Milan,Eruo,176.809EXP-2048,D. Osorio,Bogota,Colombian Pesso,74200010EXP-2049,S. Kjaer,Copenhagen,kr,184011EXP-2050,A. Kulkarni,Mumbai,Rs,965012EXP-2051,R. Dimitrova,Sofia,BGN,31813EXP-2052,T. Lim,Singapore,$,406.75

Two of those writings are typos, one is a code that no longer exists, and one is a dollar sign in a city that does not use the US dollar. Turning the column into EUR, JPY and CZK is the work, and the part worth getting right is where that work has to stop.

A code list reads only the signs that spell a code

Declare the currency column as a plain list of the 178 codes in the current ISO 4217 list and let a generic value matcher compare each distinct value in the column against them. On Updog Importer 0.1.88, the result splits in two.

$      →  nothing
€      →  nothing
zł     →  nothing
840    →  nothing
Yen    →  PEN    Peruvian Sol
kr     →  KHR    Cambodian Riel
Rs     →  ARS    Argentine Peso
SFr    →  SAR    Saudi Riyal
RMB    →  RUB    Russian Ruble
BTC    →  BTN    Bhutanese Ngultrum

The bare signs score zero against every code, because no rule in a string matcher connects $ to USD. All 36 single-character signs the column ships reach nothing.

Across the whole sign table, 118 of the 167 reach nothing and 17 reach the wrong currency. zk is the Zambian kwacha and reaches CZK. nkr is the Norwegian krone and reaches LKR. The 32 that land right already spell their own code, ca$, us$, jp¥, dkr.

The words score too well. Yen and PEN are both three characters and one substitution apart, which is inside the edit budget at that length, so the Peruvian sol wins a column of Japanese yen. Rs reaches ARS on the same rule, one insertion. Nothing on screen says the answer was a coin toss.

Swapping the option list from codes to currency names moves the failure.

Dollar  →  Australian Dollar
Pound   →  Egyptian Pound
Ruble   →  Belarusian Ruble
CAD     →  XAD
kr      →  nothing
$       →  nothing
USD     →  nothing

Dollar shares a word with 23 currency names, all 23 score alike, and the option list runs in code order, so AUD answers first. XAD sits in that list as a bare code, because it is the one ISO code the engine has no English name for, and CAD lands on it one substitution away, as do MAD and NAD. On this list 166 of the 167 signs reach nothing, and so does USD.

Currency codes sit sparse in their own space. The 178 fill 1.0 percent of the 17,576 three-letter combinations, and an ISO code has 1.65 other real codes one substitution away on average. A column of country codes has the opposite problem, with 250 codes packed into 676 two-letter strings.

The currency failure sits somewhere else. A currency column is a closed list whose values arrive as signs, and ISO 4217 assigns no sign to a code.

The column stores the code and shows the code

A currency column in Updog Importer stores the three-letter ISO 4217 code in upper case for every value it resolves, and the grid prints that same code.

stored value      CZK
grid              CZK
CSV export        CZK
onComplete        CZK

Stripe wants a three-letter ISO code in lower case in every payment request. Microsoft Dynamics GP refuses every web-service operation that touches currency until each currency carries a unique three-character ISO code. A name would have to be translated, and it would widen the column beside an amount for no gain.

The person reading the grid pays for that choice. PEN says less than Sol to someone without finance experience, and an Arabic interface shows a Latin code where the rest of the screen is Arabic. The column trades that for a value that never changes with language or CLDR version.

The browser already holds the currency names

Intl.DisplayNames turns a currency code into a name in a given language, and it reads from the CLDR the engine already ships for the rest of Intl. It has been available across browsers since April 2021.

The importer therefore ships no table of names in 26 languages. Give Intl.DisplayNames the 178 codes in one locale and it builds a name index for that language at runtime, weighing nothing in the bundle. Updog Importer builds that index across 26 languages and three name styles the first time any value reaches a currency column.

On Node 24.19.0 with ICU 78.3, the index holds 4,249 forms and takes about 22 milliseconds to build. Once built, a thousand readings of US Dollar take under half a millisecond.

Dólar estadounidense, Доллар США, 美元 and دولار أمريكي all reach USD through it, and none of the four ships with the SDK.

Intl.supportedValuesOf("currency") returns 162 codes on this build. Six of them left ISO 4217 already, ANG, BGN, CUC, HRK, SLL and ZWL. Twenty-two current codes are missing from it, among them every fund code and VED. So the SDK ships its own 178 and asks the engine only for names.

The standard writes some of those names differently. ISO 4217 calls GBP Pound Sterling, JPY Yen, PLN Zloty and ZAR Rand, where CLDR calls them British Pound, Japanese Yen, Polish Zloty and South African Rand. Updog Importer ships the difference as one table of 82 forms, 902 bytes gzipped, and adds to it a hand-written list of writings neither source names, Sterling, RMB, NIS, TL and the four ways a file writes the Swiss franc among them.

ISO 4217 assigns no sign, so the column ships its own table

ISO 4217 defines alphabetic and numeric codes. Symbols sit outside it, and the codes exist to remove the ambiguity a symbol carries. Reading a sign therefore needs data the standard does not publish, and Intl.NumberFormat prints it.

The formatter prints a currency two ways. currencyDisplay: "symbol" hands the bare sign to one dollar and prefixes the rest, so $ belongs to USD while the Canadian dollar prints CA$ and the Australian dollar prints A$. currencyDisplay: "narrowSymbol" drops the prefixes, and 29 of the 178 currencies then print a bare $.

Updog Importer reads both forms across the same 26 languages and keeps the 161 signs it can settle on one currency, 857 bytes gzipped. A sign several currencies print goes to the English symbol rule, which settles $ on USD, ¥ on JPY, £ on GBP and ₩ on KRW. The rule settles nothing for kr, since English prints DKK, ISK, NOK and SEK in that style and hands the bare sign to none of them. Six signs survive the rule and stay contested.

kr, zł, Kč, Rs and Ft carry no character of the Unicode currency category \p{Sc}, so a number reader that strips symbols from an amount leaves them behind.

The name lookup and the sign lookup normalize differently. A name loses its accents, so Kč becomes kc and reaches nothing in the name index. A sign keeps them and folds full-width characters instead, so Kč stays kč and reaches CZK. One trailing dot comes off, so Rs. and kr. reach the table as rs and kr.

Exact reading answers before the matcher does

Each imported value goes through the same reading ladder, and the first step that answers wins. A date column votes on one format for the whole column, since 01/05/2026 cannot be read without knowing what its neighbours are. A currency column needs no such vote. € and 840 name their currency whatever sits above or below them.

1  normalize the text
2  three letters    →  the 178-code list
3  one to three digits, padded to three  →  the numeric map
4  a form several currencies share       →  ambiguity guard, hold them all
5  the runtime name index, 26 languages
6  the ISO name forms table
7  normalize the sign
8  a sign several currencies print       →  ambiguity guard, hold them all
9  the sign table
10 nothing

The numeric step repairs a spreadsheet's own damage. The Argentine peso is ARS, 032, and a spreadsheet can turn the numeric code into 32. Padding a one- to three-digit value back to three digits restores the lookup key. A stock code or a barcode loses its leading zeros the same way, and a currency column can repair the loss because ISO numeric codes have a fixed width of three digits.

This ladder settles seven of the twelve values in the register with no fuzzy matching anywhere in it. Yen is answered by the ISO forms table at step 6, and so is SFr., which sits there beside fr, fr. and sfr because files write the Swiss franc that way. Two of the five it leaves are misspellings.

A correction only happens when one currency is close

Eruo is a swap of two letters away from Euro, and the person who uploaded the file should not have to spend a decision on it. Updog Importer corrects that kind of value during the matching step.

The matcher searches currency names in eight languages across three name styles, plus the ISO forms table. The distance is Damerau-Levenshtein, so a deletion, an insertion, a substitution or a swap of two neighbouring characters each cost one edit. The allowed distance is one edit, and exactly one currency has to be that close.

Two cut-offs sit in front of the matcher, both read off the normalized value. A value under four characters never reaches it, which keeps every three-letter code out. A value carrying anything besides letters and spaces never reaches it either, which keeps $ and 840 out.

Eruo             →  EUR   only Euro is one edit away
Colombian Pesso  →  COP   only Colombian Peso is one edit away
GPB              →  none  three characters, under the floor

GPB shows what the floor costs. A person meant GBP, the two letters are swapped, and the matcher refuses.

Lowering the floor recovers nothing there. The pool holds names, GBP is a code, and nothing in the pool stands one edit from GPB. Measured at a floor of three, the value still reads as nothing.

A floor of three buys yen, won, som, sfr, rmb and nis in the pool, and 582 of the 17,576 three-letter strings then land on one of them. WAN becomes the Korean won.

A test in the SDK runs the rules against generated misspellings of every English currency name the engine returns for the 178 codes. At each character position, the fixture drops the character, doubles it, swaps it with its neighbour, or replaces it with one of a e i o s r n, skipping a replacement that changes nothing and a swap at the last position. That produces 25,837 generated values, 25,725 of them distinct.

One pass over that corpus takes about 0.2 seconds on Node 24.19.0 with ICU 78.3.

The single value in the third row is ouro, a mutation of Euro and the Portuguese name for gold, so exact reading answers XAU before the matcher runs.

Of the 1,916 refusals, 1,880 fail the letters-and-spaces cutoff. Eight English currency names carry a bracket, a full stop or an ampersand, Chilean Unit of Account (UF) and São Tomé & Príncipe Dobra among them, and every misspelling of those keeps the punctuation that stops it at the door.

Not one of the 25,837 was corrected onto the wrong currency.

A word that names a family stays a question

dollar names 23 currencies. franc names 11, pound names 9, and peso names 9. Nothing in the word itself picks one, so the importer keeps every candidate open and leaves the decision to the person who has the rest of the row.

A sign four countries print stays a question the same way. kr in the register comes from Copenhagen, and a person reading the row knows it means Danish kroner. The matcher does not read the city column, so it offers four candidates and chooses none.

A currency value therefore leaves the matching step with one of four verdicts.

BGN was valid when the file was written. Bulgaria adopted the euro on 1 January 2026, and the lev left the current ISO list with it. A withdrawn code stays as text, because 100 BGN and 100 EUR are different amounts of money and no importer can do that arithmetic without a rate. HRK, ANG, SLL, ZWL and MRO behave the same way, and so does BTC, which ISO 4217 never carried.

$ in the Singapore row exposes the opposite boundary. The importer reads it exactly and writes USD, following the way English prints the sign, and for that row the answer is wrong. A bare dollar sign from Singapore is ambiguous to a person as much as to a matcher, and the matching step puts $ → USD on screen where the person can change it.

The column takes one line of configuration

You declare a currency column like any other column.

import type { DataEditorColumn } from "@updog/data-editor";

export const columns: DataEditorColumn[] = [
  { id: "claimRef", title: "Claim ref", validators: [{ type: "required" }] },
  { id: "traveller", title: "Traveller" },
  { id: "city", title: "City" },
  { id: "currency", title: "Currency", editor: { type: "currency" } },
  {
    id: "amount",
    title: "Amount",
    editor: { type: "number" },
    validators: [{ type: "number", min: 0 }],
  },
];

editor: { type: "currency" } enables the 178-code list, exact reading, safe correction, built-in validation, and the currency-specific matcher that runs during value matching.

If none of those steps resolves a value, the importer keeps the original text in the cell and flags it as invalid. The person still sees what the traveller wrote and can pick the currency by hand.

The list is closed by default. enableCustomValue: true adds a Create option to the dropdown, and a value the person creates passes validation and reaches onComplete as typed. A finance team that still reports on a withdrawn code opens the list that way, and so does anyone who needs a crypto ticker.

The matching step shows what it decided

Value matching works on the distinct values a column carries. If the same sign appears a hundred times, the importer decides it once and applies that decision everywhere it occurs. The same step handles enum columns. A currency column brings its own reading and correction rules to that step.

The file value sits on one side, the resolved currency on the other, an arrow between them.

The step shows Eruo opposite EUR with a Corrected tag. The person can change it or clear it.

kr sits opposite an empty box, with DKK, ISK, NOK and SEK marked Possible match in its list. Rs sits opposite an empty box with five.

BGN also sits opposite an empty box, and nothing in its list is marked.

Three rows reach the grid flagged, at file lines 10, 11 and 12.

Each one keeps its original text, so the person picking the currency still has what the receipt said.

The column can hold fewer than 178 codes

A company settles expenses in the currencies its offices bank in, and this register covers eleven of them. The dropdown offers the other 167 all the same, and the matcher weighs every one of them against each imported value.

only on the currency editor takes the codes the column accepts.

const settlementCurrencies = [
  "ARS", "CHF", "COP", "CZK", "DKK", "EUR",
  "INR", "JPY", "KES", "SGD", "USD",
];

const currency: DataEditorColumn = {
  id: "currency",
  title: "Currency",
  editor: { type: "currency", only: settlementCurrencies },
};

The dropdown, the matching step, the built-in rule, the export sample and the chat all work from that list. A value that reads to a code outside it stays in the cell as text and gets flagged, so NOK on this column reaches nothing.

The narrowing happens before the matcher builds its pool, so a sign or a word several currencies share settles when the column takes one of them.

kr      →  DKK   Iceland, Norway and Sweden left the list
Rs      →  INR   four rupees left the list
Franc   →  CHF   ten francs left the list
Dollar  →  none  Singapore and the United States both stayed
BGN     →  none  a withdrawn code is still withdrawn

Dollar shows the limit. Narrowing settles a shared word only when one candidate survives, and this list keeps two dollars.

The importer drops a code that ISO 4217 does not carry and names it in a console warning, so only: ["BGN"] narrows nothing. An empty array means the whole list. The array is read once per identity, so keep it stable across renders the way you keep options.

The rest of the column stays fixed

Automatic correction runs only during the upload wizard, where the matching step shows the result to the person importing the file. A value such as Eruo that arrives later through paste, loadData or a formula stays as typed and gets flagged. A correction the person never sees is a change to their data made behind their back.

Exact reading covers 26 languages, and correction covers eight. Česká koruna reads exactly and a misspelled Ceska korouna reads as nothing, because Czech is one of the 26 and none of the eight. Schweizer Frankn is corrected, because German is both.

The importer holds open a fixed list of 26 family words. A word two currencies share that sits outside it reaches nothing, so Dirham stays as text while the United Arab Emirates and Morocco both use one.

Names come from CLDR through the browser, and each engine reads its own ICU build. The same build decides which signs exist, so the shipped sign table records the ICU version it was generated on. A formatter can draw different text in the cell, which turns find and replace on the column one way. It searches the text the formatter drew and cannot write back through it.

A cell holding $100 carries an amount and a currency together, and splitting it is a separate step over two columns. The currency column reads a sign that arrives on its own.

The importer reads and the person settles

The importer resolves what the file makes clear. It writes the currency code when the value is exact, marks a safe correction, shows every candidate when a sign or a word is shared, and leaves the value unresolved when it cannot choose.

The person settles what remains open. On a column over all 178 codes, this register hands them kr, Rs and a lev that no longer exists.

Your handler receives codes such as EUR, JPY and CZK for every currency the importer or the person resolved. Anything left unresolved reaches it as the original text, flagged invalid. Your application still decides what those codes mean when the amounts beside them reach your ledger.

The next register may write Euros where this one wrote €. The same column reads it as another writing of the same currency.