Your CSV import works on your laptop. Then a customer in Germany uploads a file.

KevinXiao — Tue, 10 Nov 2026 16:03:17 +0000

A first CSV import often looks like this:

const text = await file.text();
const rows = text.trim().split('\n').map((line) => line.split(','));

It works on the Google Sheets export you used for testing. Then a support ticket arrives:

"The import says every row is invalid. It's the same spreadsheet we use every month."

You open their file:

ID;Name;Email;Plan;Seats;MRR;Country
C-3001;Jos� Mart�nez;jose@example.es;Pro;6;299,00;ES

That one line hides three everyday Excel problems outside the US — and your two-liner hits all of them:

Encoding isn't UTF-8 → accented letters become � (file.text() always decodes UTF-8).
Delimiter is ; → European Excel uses semicolon when comma is the decimal separator.
299,00 is a number → decimal comma, not "invalid".

Add two more traps that show up the same week: newlines inside quoted fields, and dates like 05/03/2025 that mean different days in the US and Europe.

Below is how to handle each in the browser with plain TypeScript — no CSV library required. The pattern that keeps support tickets short: validate strictly, suggest generously.

TL;DR checklist

Read bytes, not file.text(); honour BOM; try strict UTF-8; fall back to Windows-1252 with a visible warning.
Detect , / ; / tab / | outside quotes; don't hard-code comma.
Parse RFC 4180 (quoted fields, "", embedded newlines) — never split('\n') then split(',').
Keep cell values as strings until your schema decides the type (protects leading-zero IDs).
For bad numbers/dates: reject the raw value, offer a deterministic fix the user approves — never silently guess day/month order.
Always re-validate on the server before writing to the DB.

1. Decode bytes — don't trust `file.text()`

export function decodeCsvBytes(buf: ArrayBuffer): {
  text: string;
  encoding: string;
  warning?: string;
} {
  const bytes = new Uint8Array(buf);

  if (bytes[0] === 0xef && bytes[1] === 0xbb && bytes[2] === 0xbf)
    return { text: new TextDecoder('utf-8').decode(bytes.subarray(3)), encoding: 'utf-8' };
  if (bytes[0] === 0xff && bytes[1] === 0xfe)
    return { text: new TextDecoder('utf-16le').decode(bytes.subarray(2)), encoding: 'utf-16le' };
  if (bytes[0] === 0xfe && bytes[1] === 0xff)
    return { text: new TextDecoder('utf-16be').decode(bytes.subarray(2)), encoding: 'utf-16be' };

  try {
    return {
      text: new TextDecoder('utf-8', { fatal: true }).decode(bytes),
      encoding: 'utf-8',
    };
  } catch {
    return {
      text: new TextDecoder('windows-1252').decode(bytes),
      encoding: 'windows-1252',
      warning:
        'This file was not UTF-8, so we read it as Windows-1252. Check accented characters.',
    };
  }
}

{ fatal: true } is the whole trick. Without it, bad bytes become � forever. With it, you get an exception you can recover from.

Also watch for mojibake already in the file (JosÃ©): UTF-8 was once decoded as Latin-1 and saved again. Detect Ã/Â patterns and suggest a repair (re-encode as Latin-1 bytes, decode as UTF-8). Don't auto-apply — show the before/after.

2. Detect the delimiter from the data

export function detectDelimiter(text: string): string {
  const lines = text.split(/\r?\n/).filter(Boolean).slice(0, 10);
  let best = ',', bestScore = -1;

  for (const d of [',', ';', '\t', '|']) {
    const counts = lines.map((line) => {
      let n = 0, inQuotes = false;
      for (const ch of line) {
        if (ch === '"') inQuotes = !inQuotes;
        else if (ch === d && !inQuotes) n++;
      }
      return n;
    });
    if (!counts[0]) continue;
    const consistent = counts.filter((c) => c === counts[0]).length;
    const score = consistent * 100 + counts[0];
    if (score > bestScore) { bestScore = score; best = d; }
  }
  return best;
}

Consistency beats raw frequency. 299,00 contains commas that vary per row; semicolons usually don't.

Sampling with split(/\r?\n/) is fine for a quick guess. Your real parser must ignore newlines inside quotes, or a multi-line address in row 2 skews the counts.

3. Parse quoted fields (including embedded newlines)

Don't do this:

// breaks on commas / quotes / newlines inside fields
text.split('\n').map((line) => line.split(','));

RFC 4180 essentials: fields may be "...", a literal quote is "", newlines inside quotes are part of the field. Prefer CRLF when you write CSV (Excel is happier).

Sketch of the state machine:

export function parseCsv(text: string, delimiter = ','): string[][] {
  const src = text.charCodeAt(0) === 0xfeff ? text.slice(1) : text;
  const records: string[][] = [];
  let row: string[] = [], field = '', inQuotes = false, i = 0;

  const endRow = () => {
    row.push(field); field = '';
    if (!(row.length === 1 && row[0].trim() === '')) records.push(row);
    row = [];
  };

  while (i < src.length) {
    const ch = src[i];
    if (inQuotes) {
      if (ch === '"') {
        if (src[i + 1] === '"') { field += '"'; i += 2; continue; }
        inQuotes = false; i++; continue;
      }
      field += ch; i++; continue;
    }
    if (ch === '"' && field === '') { inQuotes = true; i++; }
    else if (ch === delimiter) { row.push(field); field = ''; i++; }
    else if (ch === '\r' || ch === '\n') {
      endRow();
      i += ch === '\r' && src[i + 1] === '\n' ? 2 : 1;
    } else { field += ch; i++; }
  }
  if (field !== '' || row.length > 0) endRow();
  return records;
}

If a data row's cell count ≠ header width, surface a warning. Unquoted commas in free text are the usual cause — later values are shifted into the wrong columns.

Keep every cell as a string through parse. Auto-coercion (dynamicTyping, naive Number(...)) eats leading zeros in IDs and ZIP codes. Let the schema decide types.

4. Don't reject `299,00` — suggest `299.00`

Strict validation is right for your API. Rejecting the whole row with "invalid number" is wrong when the fix is obvious. Separate validating from suggesting:

const NUMBER = /^[+-]?(\d+\.?\d*|\.\d+)(e[+-]?\d+)?$/i;

function suggestNumber(raw: string): string | undefined {
  let s = raw.replace(/[\s$€£¥]/g, '').replace(/^\((.*)\)$/, '-$1');
  if (/^[+-]?\d{1,3}(,\d{3})+(\.\d+)?$/.test(s)) s = s.replace(/,/g, ''); // 1,200.50
  else if (/^[+-]?\d+,\d+$/.test(s)) s = s.replace(',', '.');             // 299,00
  return NUMBER.test(s) ? s : undefined;
}

UI copy that works: MRR expects a number, got "299,00". **Use 299.00** + "Apply all in this column". Order the regexes carefully: strip US thousands before treating a lone comma as a decimal, or 1,200 becomes 1.200.

5. Dates: never guess day-first vs month-first

14/03/2025 → only 14 March is calendar-valid → safe to suggest 2025-03-14.
05/03/2025 → 5 March or 3 May → flag it, let the user choose.

Rules that don't silently corrupt renewals:

Accept only the formats declared on the field.
Validate the calendar (no Feb 30).
Suggest a fix only when exactly one other known format yields a single ISO date.

European Excel also sends 14.03.2025. Support DD.MM.YYYY explicitly if your customers are there — Date.parse will not save you.

// Suggest only when exactly one other format produces one ISO date.
function suggestDate(raw: string, accepted: DateFormat[]): string | undefined {
  if (parseWithAny(raw, accepted)) return undefined;
  const hits = ALL_FORMATS
    .filter((f) => !accepted.includes(f))
    .map((f) => tryParse(raw, f))
    .filter(Boolean);
  const unique = [...new Set(hits)];
  return unique.length === 1 ? unique[0] : undefined;
}

The pattern

Do	Don't
Decode bytes strictly, fall back visibly	Trust `file.text()`
Detect delimiter outside quotes	Hard-code `,`
Parse quotes / embedded newlines	`split('\\n').map(split(','))`
Validate strictly, suggest fixes	Auto-"fix" ambiguous dates
Export an error report (row, field, value, fix)	"Row 4812 failed" with no cell

Client-side checks are UX. Re-validate on the server before you write.

Try it / go further

You can copy the snippets above into any React or vanilla TS app. If you'd rather drop in a guided flow (upload → map columns → cell-level errors → one-click fixes → export) instead of assembling the pieces, I packaged this same approach as PatchRow — a React + TypeScript CSV import kit (source you own, one-time $39). Disclosure: it's my product. It does not parse .xlsx; users export CSV. No sales numbers or testimonials to share yet — shipping this article is part of finding the first buyers.

Question for the comments: what's the worst CSV your import has eaten — encoding, dates, or something weirder? I read every reply.

DEV Community: KevinXiao