Regex basics: import a messy orders CSV with JavaScript

Learn regex basics through a real job: import an old orders CSV with dates, phones and amounts written every which way, plus the regex trap that freezes servers.

Regex (a regular expression) describes a pattern of text so a machine can search for it or check a string. With messy hand-typed data, use regex to normalize each cell, and split the CSV cells with separate code. Want to learn regex through a real job? Let's do it.

An old CSV file with thousands of orders: how do you import it?

In the previous post, the report of the order system of the Mộc café went from almost two seconds down to a few milliseconds. (Mộc is an illustrative café that I use as the running example in this series.) The owner was happy, and then mentioned one thing: the old point-of-sale machine left behind a CSV file with thousands of orders that never entered the system. The file was typed in by hand by many people over many years, so everyone had their own style.

Here is a sample of that file (the data is illustrative). It has nine order lines, and each line deliberately stands for one kind of mess, and the first line is the column header:

old-orders.csv
order_id,date,customer,phone,items,total
2001,03/11/2026,"Nguyễn Văn An",0901 000 001,"2 x milk coffee; 1 x egg banh mi","79.000đ"
2002,3-11-26,  Trần Thị Bình  ,+84 902 000 002,"1 x bac xiu","32,000"
2003,2026.11.03,"Lê, Văn Cường","(090)1000003","1x salt coffee ; 2 x egg banh mi",85k
2004,03/11/2026,,,"3 x black coffee",75000
2005,31/02/2026,"Phạm Thị Dung",0904000004,"1 x bac xiu","32.000"
2006,04/11/2026,"Hoàng Văn Em",09O5000005,"1 x milk coffee","29.000"
2007,04/11/2026,"Đỗ Thị Phương",0906000006,"milk coffee","29.000"
2008,05/11/2026,"Võ Văn Giang",0907000007,"2 x peach tea",abc
2009,05/11/2026,"Bùi Thị Hà",0908000008,"1 x milk coffee",35

Read a few lines closely. The date is written three ways (03/11/2026, 3-11-26, 2026.11.03). The phone numbers have spaces, a +84 (the country code of Vietnam), and parentheses. The money is sometimes written with the letter đ (the symbol of the Vietnamese đồng, about 26,000 to one US dollar), sometimes with a comma, sometimes as 85k (thousands), and sometimes as a bare number.

The customer name in line 2003 has a comma inside quotation marks. Order 2004 has neither a name nor a phone number because it was a counter sale. Order 2005 has the date 31/02, order 2006 has the letter O in place of the digit 0 in the phone number, order 2008 has the total "abc", and order 2009 has the total 35, which looks exactly like the lost-separator mistake of "35.000" that we saw in earlier posts of the series.

There are two more things you cannot see: the real file begins with a BOM (an invisible character that Excel often adds at the start of a file) and every line ends with \r\n, like a file from a Windows machine. If you retype this file, you can leave both out, because the code below handles both cases.

My goal: every line either becomes an order with clean data, or is rejected with a clear reason for a person to check by hand. No line may disappear silently. You need Node.js 22.18 or newer, because from that version Node runs .ts files directly, and nothing else needs to be installed. The folder layout:

Folder layout
import-old-orders/
  package.json
  import.ts
  try-naive.ts
  try-redos.ts
  measure-speed.ts
  src/old-orders.csv
  src/csv.ts
  src/normalize.ts
  src/import-csv.ts
  src/import-csv.test.ts
package.json
{
  "name": "import-old-orders",
  "private": true,
  "type": "module",
  "scripts": { "test": "node --test src" }
}

(Put the CSV above into src/old-orders.csv.)

Split by commas and check directly: where does it break?

The first approach that everyone thinks of is split(",") to separate the cells, and then a simple regex ^0\d{9}$ to check the phone number, which means a 0 followed by exactly nine digits. I tried both on the line of order 2003:

try-naive.ts
// The first two ways of doing it (both wrong) on line 4 of old-orders.csv. Run: node try-naive.ts
import { readFileSync } from "node:fs";

const lines = readFileSync("src/old-orders.csv", "utf8").split(/\r?\n/);
const line4 = lines[3]; // order 2003, the customer name is "Lê, Văn Cường"

console.log(`Original line: ${line4}`);
const cells = line4.split(",");
console.log(`split(","):    ${cells.length} cells (6 needed)`);
console.log(`  3rd cell (customer): ${cells[2]}`);
console.log(`  4th cell (phone):    ${cells[3]}`);
console.log(`  6th cell (total):    ${cells[5]}`);

console.log("\nChecking the phone number with /^0\\d{9}$/ directly on the raw cell:");
for (const phone of ["0901000001", "0901 000 001", "+84 902 000 002", "(090)1000003"]) console.log(`  ${JSON.stringify(phone).padEnd(20)} → ${/^0\d{9}$/.test(phone)}`);
Try it
node try-naive.ts
Output
Original line: 2003,2026.11.03,"Lê, Văn Cường","(090)1000003","1x salt coffee ; 2 x egg banh mi",85k
split(","):    7 cells (6 needed)
  3rd cell (customer): "Lê
  4th cell (phone):     Văn Cường"
  6th cell (total):    "1x salt coffee ; 2 x egg banh mi"
Output (continued)
Checking the phone number with /^0\d{9}$/ directly on the raw cell:
  "0901000001"         → true
  "0901 000 001"       → false
  "+84 902 000 002"    → false
  "(090)1000003"       → false

Look at two things. The line 2003 is split into seven cells instead of six, because the comma in "Lê, Văn Cường" is also treated as a column separator. The consequence is that every cell after it moves one position: the phone number is now "Văn Cường", and the total column holds the list of items. If the code does not count the cells, wrong data is imported without any error calling out.

The second thing is that the regex ^0\d{9}$ is right for "0901000001" but wrong for the other three ways of writing, even though all three are valid phone numbers. The regex is not wrong; I gave it data that was not cleaned.

The basic regex pieces you need for this job

I do not list the whole table of symbols, only the pieces used below. The characters ^ and $ mark the start and the end of the string, so ^0\d{9}$ forces the whole string to match, not just a part of it. \d is one digit, {9} is exactly nine times, + is one or more times, and ? is zero or one time.

Square brackets [...] are any one character from the set, so [x×] is the letter x or the multiplication sign. Round brackets (...) make a group, and (?:...) is a group that is not remembered. A named group (?<day>...) lets you take the result by name instead of by position. The flag i ignores the difference between capital and small letters, and the flag u switches on Unicode mode, needed when you use a character such as đ. The full syntax is in the regex guide on MDN.

The approach I chose splits the work into two steps. Step one is cleaning: remove the characters that we know are decoration (spaces, parentheses, dots in a phone number). Only step two checks strictly with a simple regex. Doing it the other way round (one giant regex that accepts every way of writing) is very hard to read and hard to change.

Normalize each cell with regex

Each function below takes a raw cell and returns a clean value, or throws an error with a reason. I do not let a function "guess" when it meets strange data, because a wrong guess is worse than reporting an error:

src/normalize.ts
// Every function normalizes ONE cell of the old CSV file. A wrong input throws an error with a reason, it does not guess.
export const MIN_AMOUNT = 1000; // the café has no order under 1,000 đồng; anything smaller is almost surely a lost separator ("35.000" became 35)

// "0901 000 001", "+84 902 000 002", "(090)1000003" → "0901000001". An empty cell: a counter sale, no phone number.
export function normalizePhone(raw: string): string | undefined {
  const clean = raw.replace(/[\s.()-]/g, "").replace(/^\+84/, "0");
  if (clean === "") return undefined;
  if (!/^0\d{9}$/.test(clean)) throw new Error(`phone number "${raw.trim()}" is not valid`);
  return clean;
}

// "03/11/2026", "3-11-26", "2026.11.03" → "2026-11-03". It assumes the day comes before the month (a point-of-sale machine in Vietnam).
export function normalizeDate(raw: string): string {
  const match = raw.trim().match(/^(?<a>\d{1,4})[/.-](?<b>\d{1,2})[/.-](?<c>\d{1,4})$/);
  if (!match?.groups) throw new Error(`date "${raw.trim()}" is not in a known format`);
  const { a, b, c } = match.groups;
  // A first number with 4 digits is the year (yyyy.mm.dd); otherwise it is the day (dd/mm/yyyy or dd-mm-yy).
  const [year, month, day] = a.length === 4 ? [a, b, c] : [c.length === 2 ? `20${c}` : c, b, a];
  const realDate = new Date(Date.UTC(Number(year), Number(month) - 1, Number(day)));
  if (year.length !== 4 || realDate.getUTCFullYear() !== Number(year) || realDate.getUTCMonth() !== Number(month) - 1 || realDate.getUTCDate() !== Number(day)) {
    throw new Error(`date ${raw.trim()} does not exist`);
  }
  return `${year}-${month.padStart(2, "0")}-${day.padStart(2, "0")}`;
}

// "79.000đ", "32,000", "85k", "75000" → an amount in đồng. "abc" or a number below MIN_AMOUNT is an error.
export function normalizeAmount(raw: string): number {
  const match = raw.trim().match(/^(?<number>\d{1,3}(?:[.,]\d{3})+|\d+)\s*(?<suffix>đ|k)?$/iu);
  if (!match?.groups) throw new Error(`total "${raw.trim()}" cannot be read`);
  const amount = Number(match.groups.number.replace(/[.,]/g, "")) * (match.groups.suffix?.toLowerCase() === "k" ? 1000 : 1);
  if (amount < MIN_AMOUNT) throw new Error(`total ${amount}đ is below ${MIN_AMOUNT}đ, probably a lost thousands separator`);
  return amount;
}

export type Item = { name: string; quantity: number };

// "2 x milk coffee; 1x egg banh mi" → two items. An item with no quantity counts as 1.
export function splitItems(raw: string): Item[] {
  const items = raw
    .split(";")
    .map((part) => part.trim())
    .filter(Boolean)
    .map((part) => {
      const match = part.match(/^(?:(?<quantity>\d+)\s*[x×]\s*)?(?<name>.+)$/iu)!;
      return { name: match.groups!.name.trim(), quantity: Number(match.groups!.quantity ?? 1) };
    });
  if (items.length === 0) throw new Error("the order has no items");
  return items;
}

Each function has a few decisions worth explaining. For the phone number, [\s.()-] is a set made of whitespace, a dot, two parentheses and a hyphen, and replace with the flag g removes all those characters. Then ^\+84 turns the country code into a 0. The letter O in order 2006 is not removed, so ^0\d{9}$ catches it. If I were "kind" and turned O into 0, a real data-entry mistake would slip into the system.

For the date, the three ways of writing share one regex with three named groups, separated by /, . or -. Then the code looks at whether the first number has four digits: if so it is the year (2026.11.03), if not it is the day. One easy thing to miss is that a regex only knows the shape and does not know that 31/02 does not exist, so I build a real Date and compare the day, the month and the year again. The first line of the function also records an assumption: the day comes before the month, because the point-of-sale machine is in Vietnam. For a date such as 01/02/2026, from the data alone you cannot know whether it is 1 February or 2 January, so this assumption must be asked of the owner of the file and cannot be deduced.

For the amount, the first branch of the regex \d{1,3}(?:[.,]\d{3})+ matches numbers with a thousands separator (79.000, 32,000, 1.250.000), the second branch \d+ matches a bare number, and then comes an optional suffix of đ or k. A number below MIN_AMOUNT (1,000đ) is rejected, which is exactly how order 2009 is caught: "35" is almost surely "35.000" with the separator lost. Finally the function splitItems uses the optional group (?:(?<quantity>\d+)\s*[x×]\s*)?, so "milk coffee" with no quantity still counts as one.

Do not use regex to split the CSV cells

At this point you may think I will write a very long regex to split cells that have quotation marks. I do not. The rule of the CSV format (described in RFC 4180) is that a cell that contains a comma, a quotation mark or a line break should be put in quotation marks, and a quotation mark inside is written as two. That rule has "state": whether you are inside or outside the quotation marks. Regex describes state very badly, while a loop over the characters describes it very clearly:

src/csv.ts
// Split ONE CSV line into cells. It understands a cell inside quotation marks (which may contain commas) and a doubled "" as one quotation mark.
// Limit: a cell may not contain a line break. For a file with multi-line cells, use a CSV reading library.
export function splitCsvLine(line: string): string[] {
  const cells: string[] = [];
  let current = "";
  let inQuotes = false;
  for (let i = 0; i < line.length; i++) {
    const c = line[i];
    if (inQuotes) {
      if (c === '"' && line[i + 1] === '"') {
        current += '"';
        i++;
      } else if (c === '"') inQuotes = false;
      else current += c;
    } else if (c === '"') inQuotes = true;
    else if (c === ",") {
      cells.push(current);
      current = "";
    } else current += c;
  }
  if (inQuotes) throw new Error("a quotation mark is not closed");
  cells.push(current);
  return cells;
}

This function splits only one line, so a cell may not contain a line break. That is a limit I accept for this file because I have read through it. For a more complicated CSV file, in my view you should use a CSV library that many people have already verified instead of writing your own.

Put it together: every line becomes an order, or is rejected with a reason

The function importCsv does the last step: it removes the BOM with replace(/^/, ""), cuts the lines with split(/\r?\n/) (accepting both \r\n and \n), checks the header line, and for every line splits the cells, counts them and calls the normalizing functions. If any step throws an error, that line goes to the rejected list together with the line number, the reason and the original text:

src/import-csv.ts
// Import the old orders CSV: every line either becomes a valid order, or is rejected WITH a reason and the original line. No line disappears silently.
import { normalizeAmount, normalizeDate, normalizePhone, splitItems, type Item } from "./normalize.ts";
import { splitCsvLine } from "./csv.ts";

export type ImportedOrder = { orderId: string; date: string; customer?: string; phone?: string; items: Item[]; total: number };
export type RejectedLine = { line: number; reason: string; raw: string };
export type ImportResult = { valid: ImportedOrder[]; rejected: RejectedLine[] };

const COLUMNS = ["order_id", "date", "customer", "phone", "items", "total"];

export function importCsv(content: string): ImportResult {
  // Excel often adds a BOM () at the start of the file, and a Windows machine ends lines with \r\n.
  const [header, ...lines] = content.replace(/^/, "").split(/\r?\n/);
  if (splitCsvLine(header).join(",") !== COLUMNS.join(",")) throw new Error(`The header line must be: ${COLUMNS.join(",")}`);

  const result: ImportResult = { valid: [], rejected: [] };
  lines.forEach((raw, i) => {
    if (raw.trim() === "") return; // an empty line at the end of the file
    try {
      const cells = splitCsvLine(raw);
      if (cells.length !== COLUMNS.length) throw new Error(`has ${cells.length} cells, expected ${COLUMNS.length}`);
      const [orderId, date, customer, phone, items, total] = cells;
      result.valid.push({
        orderId: orderId.trim(),
        date: normalizeDate(date),
        customer: customer.trim().replace(/\s+/g, " ") || undefined,
        phone: normalizePhone(phone),
        items: splitItems(items),
        total: normalizeAmount(total),
      });
    } catch (error) {
      result.rejected.push({ line: i + 2, reason: (error as Error).message, raw }); // line 1 is the header, so the first data line is line 2
    }
  });
  return result;
}
import.ts
// Import the old orders file and print a report. Run: node import.ts src/old-orders.csv
import { readFileSync } from "node:fs";

import { importCsv } from "./src/import-csv.ts";

const { valid, rejected } = importCsv(readFileSync(process.argv[2] ?? "src/old-orders.csv", "utf8"));
console.log(`Read ${valid.length + rejected.length} lines: ${valid.length} valid, ${rejected.length} rejected.\n`);
for (const order of valid) console.log(`order ${order.orderId}  ${order.date}  ${(order.customer ?? "(counter)").padEnd(14)} ${(order.phone ?? "-").padEnd(10)} ${String(order.total).padStart(6)}đ  ${order.items.map((item) => `${item.quantity} ${item.name}`).join(", ")}`);
console.log("\nRejected lines (with the reason, for a person to check by hand):");
for (const line of rejected) console.log(`line ${line.line}: ${line.reason}`);
Import the old orders file
node import.ts src/old-orders.csv
Output
Read 9 lines: 5 valid, 4 rejected.

order 2001  2026-11-03  Nguyễn Văn An  0901000001  79000đ  2 milk coffee, 1 egg banh mi
order 2002  2026-11-03  Trần Thị Bình  0902000002  32000đ  1 bac xiu
order 2003  2026-11-03  Lê, Văn Cường  0901000003  85000đ  1 salt coffee, 2 egg banh mi
order 2004  2026-11-03  (counter)      -           75000đ  3 black coffee
order 2007  2026-11-04  Đỗ Thị Phương  0906000006  29000đ  1 milk coffee

Rejected lines (with the reason, for a person to check by hand):
line 6: date 31/02/2026 does not exist
line 7: phone number "09O5000005" is not valid
line 9: total "abc" cannot be read
line 10: total 35đ is below 1000đ, probably a lost thousands separator

Compare with the original file. The five valid orders have dates in the same form 2026-11-03, phone numbers in the same ten-digit form, and order 2003 keeps the name "Lê, Văn Cường" intact. The four rejected lines are exactly the four I broke on purpose (2005, 2006, 2008, 2009), each with a reason that a person checking by hand understands at once. Line 6 is the line number in the file, counting the header line. Five plus four is nine, equal to the number of data lines: no line disappeared.

And what about speed? A real file has thousands of lines, so I measured by repeating those nine data lines 400 times:

measure-speed.ts
// Import a big file: repeat the 9 data lines of old-orders.csv 400 times (3,600 lines) and measure the time. Run: node measure-speed.ts
import { readFileSync } from "node:fs";

import { importCsv } from "./src/import-csv.ts";

const [header, ...lines] = readFileSync("src/old-orders.csv", "utf8").trim().split(/\r?\n/);
const bigFile = [header, ...Array.from({ length: 400 }, () => lines).flat()].join("\n");

const start = performance.now();
const { valid, rejected } = importCsv(bigFile);
console.log(`${valid.length + rejected.length} lines: ${valid.length} valid, ${rejected.length} rejected, took ${Math.round(performance.now() - start)} ms`);
Measure the speed
node measure-speed.ts
Output (the time on your machine will differ)
3600 lines: 2000 valid, 1600 rejected, took 30 ms

3,600 lines in 30 ms, so speed is not something to worry about at this scale.

Regex can also freeze a server

There is one more thing I want you to know before you use regex more. Some regexes look harmless but take twice as long for every extra character in the string, when the string does not match. This phenomenon is called catastrophic backtracking, and the OWASP page on ReDoS describes it as a kind of denial-of-service attack. The condition you often meet is a repeat nested inside a repeat, such as (\d+\.?)+.

I tried two regexes with the same meaning, "a string of digits that may have dots as separators". The first is easy to think of, and the second is written so every character can match in only one way. The input is n digits 1 followed by an x at the end, which deliberately does not match:

try-redos.ts
// A harmless-looking regular expression can freeze a server. Run: node try-redos.ts
const FRAGILE = /^(\d+\.?)+$/; // "one or more groups: digits, then maybe a dot"; digits repeated nested inside a repeated group
const SAFE = /^\d+(\.\d+)*$/; // "digits, then groups: a dot with digits"; every character can match in only one way

// The median time of 3 runs (on the first run V8 is still compiling the expression, so it is often unusually slow).
function measure(pattern: RegExp, guess: string): number {
  const runs = Array.from({ length: 3 }, () => {
    const start = performance.now();
    pattern.test(guess);
    return performance.now() - start;
  }).sort((a, b) => a - b);
  return runs[1];
}

console.log("Input: n digits 1 followed by an 'x' at the end (no match). Median time, milliseconds:");
for (const n of [22, 24, 26, 28]) {
  const input = `${"1".repeat(n)}x`;
  console.log(`n=${n}  fragile: ${measure(FRAGILE, input).toFixed(1).padStart(8)} ms   safe: ${measure(SAFE, input).toFixed(3)} ms`);
}
Try it
node try-redos.ts
Output (the times on your machine will differ)
Input: n digits 1 followed by an 'x' at the end (no match). Median time, milliseconds:
n=22  fragile:     19.4 ms   safe: 0.029 ms
n=24  fragile:     78.7 ms   safe: 0.000 ms
n=26  fragile:    338.6 ms   safe: 0.000 ms
n=28  fragile:   1346.7 ms   safe: 0.000 ms

Look at the "fragile" column: for every two extra characters the time grows about four times, which means it doubles for every character. At 28 characters it already takes more than a second, and at 40 characters it would take (an estimate following that same trend, I did not run it) hours. An amount cell of 40 characters in a hand-typed CSV file is entirely possible. The "safe" column hardly changes.

The regexes in normalize.ts have no nested repeat of that kind: the group (?:[.,]\d{3})+ has a fixed length, so every character can only match one way. I take two habits from this. One, when you write a regex and see a repeat (+, *) inside a group that is itself repeated, stop and try it with a long string that does not match. Two, for data you do not control, limit the length before running the regex. My functions do not do this yet, and if I imported a strange file, I would add a line that rejects a cell that is too long.

Tests for each function, and what is still missing

I wrote eight tests for these functions: four ways of writing a phone number give the same number, three ways of writing a date give the ISO form, dates that do not exist (31/02, month 13, a three-digit year) are rejected, amounts in different forms and amounts that cannot be right, a CSV cell with a comma and a doubled quotation mark, items with and without a quantity, and importCsv on the sample file with exactly the four rejected lines. One test records the assumption that the day comes before the month, so anyone who changes it has to change a test and cannot quietly change the meaning of the data. They run with the test runner that ships with Node:

Run the tests (in the folder with the files)
node --test src/import-csv.test.ts
Output
# tests 8
# pass 8
# fail 0

There are a few things this post has not done. It does not check for duplicate order IDs: importing the file twice gives doubled orders. It does not handle cells with line breaks. And there is no way for the person who checks by hand to fix a rejected line and import it again, even though that is exactly the owner's next step.

What if two people edit the same file?

The import function works. In the story of the series, at the very time I wrote it, Khoa was also changing normalize.ts on another branch to add a new way of writing money. When two branches touch the same file and need to be merged, Git reports a conflict. How to read and resolve a conflict without losing code is the job of the next posts.

You can follow the whole story in the series Taking over an old project. If you want to review how to measure before you fix, the post on Node.js performance has the process, and the questions about JavaScript are in the interview question bank.

Get the next post by email.

One email a week, only when there is something worth reading. No spam.