CSV Best Practices: Type Inference, Encoding, and the RFC 4180 Edge Cases

CSV is one line of text per record, with commas between fields. That is the whole format, and it is exactly why the failures are so expensive: there is no schema, no type, and no place to put an error. A broken CSV does not fail to parse — it parses into the wrong thing, confidently.

Three checks catch almost every real incident. They are at the end of this page.

1. Never let the parser guess types

0123456789 is a phone number with a leading zero. Any parser with type inference switched on can eat it — dynamicTyping: true in PapaParse, cast: true in csv-parse — and the zero is gone with nothing flagged. fast-csv is the exception worth naming: its published ParserOptions has no cast or dynamic-typing option at all, so it hands you strings and makes you do the conversion. That is a feature, and it is the option I would pick.

Same mechanism, same silence:

Inference is not a convenience feature; it is an unlogged data transformation. Default to strings, then cast deliberately per column where you can name the column and the target type. If you must infer, infer toward string and opt in to numbers on the one column that needs arithmetic — and if your parser has no way to turn it off, that is an argument against it, not for it.

This site's tool keeps everything as text for that reason; About documents what it does to your file, measured rather than claimed.

2. Quoting rules, stated precisely

RFC 4180 is short and most producers violate one clause of it:

• A field containing a comma, a double quote, or a line break must be wrapped in double quotes.
• A double quote inside a quoted field is escaped by doubling it: "He said ""hi""" → He said "hi".
• Whitespace is significant. " x" and x are different fields — which is why trim: true is a data change, not a cleanup.
• Spaces outside quotes are not defined by the RFC. "x" is a common producer bug, and parsers disagree about whether the field is x or x.

The one that bites hardest is the embedded newline. A field legitimately containing a line break must be quoted, and if your parser is counting lines instead of tracking quote state it will report a valid 500-row file as 500+ ragged rows. Before you "fix" the export, print the quote state:

// Is the file ragged, or is your line-splitter quote-blind?
import { readFileSync } from 'node:fs';
const src = readFileSync('export.csv', 'utf8');
let quoted = false, realLines = 1;
for (let i = 0; i < src.length; i++) {
  if (src[i] === '"') quoted = !quoted;
  else if ((src[i] === '\n' || src[i] === '\r') && !quoted) realLines++;
}
console.log({ naiveSplit: src.split('\n').length, quoteAwareLines: realLines, endedInsideQuote: quoted });

If those two counts differ, the file is fine and the thing reading it is not. endedInsideQuote: true means the export was truncated mid-write — a different and much worse problem, and the only one of the three that justifies rejecting the file outright.

3. Encoding and the delimiter, which are not defaults

• UTF-8 without a BOM is the safe assumption; a BOM is a signal, not a requirement. Excel on Windows writes UTF-16LE with a BOM for "Unicode Text" and UTF-8 with a BOM for "CSV UTF-8". A BOM is U+FEFF: strip it once, at read time, or it becomes part of your first header name and every lookup on that column silently returns undefined.
• ; vs , is locale, not preference. Most of continental Europe produces ;-delimited files because the decimal separator is a comma — 3,14 cannot coexist with , as a delimiter. Accept the delimiter as a parameter; do not assume.
• \r\n vs \n. Windows producers emit CRLF. Splitting on \n alone leaves a trailing \r on the last field of every row, which then poisons an exact-match join against a clean table.
• Excel will rewrite your file when you open it. Dates get coerced, long digit strings go scientific, leading zeros vanish, and saving over the original is irreversible. Inspect exports in a text tool, not a spreadsheet.
• A field that starts with = is not data to a spreadsheet. =cmd|... or =HYPERLINK(...) in an ingested column executes when someone opens the file. Prefix-guard on write if you emit CSV from user input; see formula injection.

The three checks

Put these in front of anything that ingests a CSV you did not generate, in this order:

1. Header uniqueness and arity. First row's field count becomes the contract. Duplicate header names collapse in every "convert CSV to JSON" tool, and the last value silently wins — so half your column is overwritten and no error is raised.
2. Per-row field count, reported not tolerated. Ragged rows are normal in exports from real systems. What is not normal is padding them with empty strings without counting how many rows needed it. If 4% of rows were short, that is a finding about the export; if the number is silently zero, you cannot tell padding from a clean file.
3. Type-shape stability per column. Sample the first 100 rows and the last 100. A column that is all integers at the top and contains one N/A at the bottom will parse fine and break on insert. Detecting that before a COPY is worth more than any amount of clever parsing.

This site surfaces checks 1 and 2 explicitly — duplicate headers are reported, ragged rows are counted and flagged in the SQL output, and long rows are refused rather than truncated. The behaviour, including where it is deliberately lossy, is on About.

What this page cannot tell you

The exact recovery behaviour of a specific library version is not covered here. Where RFC 4180 is silent — spaces outside quotes, missing closing quote at EOF, stray quotes mid-field — every parser has picked a side and the choice is rarely in the README. Run the snippet above against a file you actually received before deciding which tool is "strict."