tannhatcms/jarvis/ams-csv-pro

Overview

CSV looks trivial until it isn't: embedded newlines, quoted commas, BOMs, inconsistent dialects, and million-row files. This skill covers robust CSV parsing and generation — the unglamorous work that keeps data pipelines from silently corrupting data.

When to use

• Parsing user-uploaded or third-party CSVs with unknown dialects
• Generating CSV exports that open correctly in Excel and other tools
• Streaming multi-gigabyte CSV files without loading them into memory
• Validating CSV schemas (headers, types, required columns)
• Handling encoding issues (BOM, Latin-1 vs UTF-8)

Core concepts

Never split on commas. Real CSV has quoted fields containing commas, newlines, and escaped quotes (""). Always use a proper CSV parser from your language's standard library or a battle-tested package — hand-rolled splitting is a bug factory.

Dialects vary. Delimiter (comma, semicolon, tab, pipe), quote character, escape style, and line endings differ by producer and locale (much of Europe uses ; because , is the decimal separator). Sniff the dialect from a sample when it's unknown, but let users override — sniffing is heuristic.

Encoding: expect UTF-8 with BOM. Excel writes UTF-8 CSVs with a BOM; strip it or your first header becomes \ufeffid. For unknown encodings, try UTF-8, then fall back to likely candidates (Windows-1252 for Western data) — and log when you guess.

Headers are data, not gospel. Normalize them (trim, lowercase, deduplicate) and validate against an expected schema. Map by name, not position — column order changes. Report unknown/missing columns explicitly rather than silently ignoring them.

Stream large files. Process row-by-row with iterators/generators; never load a 2GB CSV into a list of dicts. Validate and transform in the streaming pass, collecting only error summaries and aggregates in memory.

Practical workflow

1. Detect: read a sample — sniff delimiter, check for BOM, detect encoding,

locate the header row (not always row 1).

1. Define the schema: expected columns, types, required fields, value

constraints; normalize header names.

1. Parse streaming: iterate rows, coerce types per column with clear error

messages including the row number and raw value.

1. Validate: collect all errors (don't fail on the first); distinguish

fatal (wrong columns) from row-level (bad value in row 4521).

1. Generate: write with the standard library writer, QUOTE_MINIMAL,

\r\n line endings for Excel compatibility, UTF-8 with BOM if the audience is Excel users, and a header row always.

1. Test with adversarial fixtures: quoted commas, embedded newlines,

empty fields, trailing delimiters, mixed line endings, and a 1M-row file for the streaming path.

Common pitfalls

• Hand-rolled `line.split(',')` — breaks on the first quoted comma; use a

real parser, always.

• Ignoring the BOM — invisible character corrupts the first column name

and breaks header mapping.

• Loading everything into memory — fine for 10k rows, fatal for 10M;

stream by default.

• Silent type coercion — "0123" becoming 123 loses leading zeros

(zip codes, SKUs); coerce deliberately per column, not globally.

• Excel mangling your output — long digit strings become scientific

notation, dates get reinterpreted; for Excel audiences consider prefixing with = or quoting, and document the behavior.

• No row numbers in error messages — "invalid value" without a row number

is unactionable; always report where the problem is.