German Excel CSV breaks your React importer: semicolons, decimal commas, and quoted line breaks

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:

1. Encoding isn't UTF-8 → accented letters become � (file.text() always decodes UTF-8).
2. Delimiter is ; → European Excel uses semicolon when comma is the decimal separator.
3. 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.

If you remember only three things:

1. Detect the delimiter and locale first — don't hard-code a comma, and don't guess inside your API.
2. Treat newlines inside quotes as valid CSV — they look like a broken file; they aren't.
3. Show the parse result to the uploader so they can fix rows in the browser before you submit.

Encoding pitfalls and ambiguous dates show up in the same files; the sections below cover them as supporting detail, not as extra takeaways.

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:

1. Accept only the formats declared on the field.
2. Validate the calendar (no Feb 30).
3. 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

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

Those three takeaways again: detect the delimiter and locale first, treat quoted line breaks as legal CSV, and let the uploader fix the parse result in the browser before you submit.

Try it / go further

You can copy the snippets above into any React or vanilla TypeScript app. To see the same German Excel traps in the browser — semicolon delimiters, decimal commas, and quoted line breaks — try the live demo. It runs locally in your browser and does not upload the file.

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 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.

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