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

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.

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:

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.

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.