CSV schema guessing — type heuristics, mixed columns, and when not to trust inferred types

Published: 2026-09-05

How browser CSV profilers infer column types from cell patterns, when columns become “mixed,” and why guessed types are exploration aids—not contracts for production pipelines.

CSV has no schema. Every cell is text until something decides otherwise. “Schema guessing” (also called profiling or type inference) scans sample rows, classifies each cell with pattern heuristics, and summarizes what each column looks like: integer, number, boolean, ISO date, email, URL, plain string—or mixed when the column disagrees with itself.

That summary is useful for exploration, import checklists, and drafting typed loaders. It is not a formal schema language (JSON Schema, Avro, SQL DDL) and it is easy to over-trust. This guide explains the usual heuristics, how mixed columns are resolved, and when you should keep a column as string anyway.

Try it on your own paste with the CSV schema guesser—parsing and classification run locally in the browser; nothing is uploaded.

What “inferred type” actually means

A profiler walks data rows (optionally treating the first row as headers) and, for each column:

Classify every cell (after trim) into a kind: empty, boolean, integer, number, date, datetime, email, url, or string.
Aggregate counts of those kinds (empties tracked separately).
Resolve one label for the column—plus stats such as unique count, sample values, and numeric min/max/mean when applicable.

So “inferred type = integer” means: among non-empty cells, the heuristics mostly saw integer-shaped text. It does not mean the column is a safe PK, that leading zeros are preserved, or that a database should store INT without review.

Delimiter detection (comma vs semicolon vs tab) is a separate step—tools often use a CSV parser such as Papa Parse with auto-delimiter—so European ; exports still form a grid before typing starts.

Common cell heuristics (order matters)

Exact rules vary by tool, but LocalTools-style classifiers typically try more specific patterns first:

Kind	Typical pattern	Notes
empty	blank / whitespace-only	Counted separately; does not force `mixed`
boolean	`true` / `false` / `yes` / `no` (case-insensitive)	Bare `y`/`n` are often avoided—they collide with short codes
datetime	ISO-ish `YYYY-MM-DD` + time (`T` or space), optional zone	Prefer ISO in source data
date	`YYYY-MM-DD` that parses as a calendar day	`01/02/2026` is usually not guessed as date
integer	optional `-` + digits only	`007` matches integer text → often becomes `7` if coerced later
number	decimal and/or scientific (`1.5e-3`)	Locale thousands separators usually fail
email	simple `[email protected]` shape	Not RFC-complete validation
url	starts with `http://` or `https://`	Relative paths stay string
string	everything else	Default bucket

Booleans before numbers avoids treating nothing special as numeric; dates before integers so 2026-01-02 is not “just digits with hyphens ignored.” Emails and URLs after numbers so 123 stays numeric.

Resolving a column: majority, widening, and “mixed”

After counting kinds for non-empty cells:

Single kind → that type (e.g. all integers → integer).
Widening pairs: integer + number → number; date + datetime → datetime. Those are compatible widenings, not conflicts.
Clear majority: if one kind is ≥ ~70% of non-empty cells, many profilers pick that kind and treat the rest as outliers to inspect in samples.
Otherwise → mixed.

Mixed is the most important label. Examples:

Mostly integers with a few "N/A" or "unknown" strings.
IDs that are numeric in some rows and prefixed (A-102) in others.
A “flag” column with yes/no plus occasional maybe.

Do not paper over mixed by forcing a SQL type. Either clean the source, split columns, or keep string and parse in application code with explicit rules.

Stats that help more than the type label

Beyond the single type word, useful signals include:

Signal	Why it matters
Empty vs non-empty	Sparse columns may be optional fields, not “bad” types
Unique count	Near-unique → candidate key; very low cardinality → enum/category
Samples	Spot the outliers that caused `mixed` or false integers
Numeric min / max / mean	Sanity-check ranges (ages of 999, negative prices)
String length min–max	Fixed-width codes vs free text

Copying a JSON or TSV profile into a ticket is often more honest than saying “column 3 is integer.”

When not to trust inferred types

Treat guesses as hypotheses. Keep or coerce to string when:

Leading zeros matter — ZIP/postal codes, account numbers, Excel-exported IDs (007). Integer inference will destroy them on import.
Locale numbers — 1.234,56 or 1,234.56 fail simple regexes or parse wrong. Do not infer number until you know the locale.
Ambiguous calendar dates — 01/02/2026 is MDY vs DMY. Prefer ISO (2026-02-01) or leave as string.
Phone / IBAN / card-like digits — digit strings are not “integers”; checksum and region rules matter (see E.164, IBAN, Luhn).
Boolean synonyms — Y/N, 1/0, localized yes/no. Heuristics only catch a small vocabulary.
URLs without scheme — www.example.com may stay string while https://… becomes url; normalize before typing.
Small samples — five clean rows can look “all integer” while row 10,000 is free text. Profile a representative slice, not a header demo.

For pipelines that must be correct, define an explicit schema (or validate with JSON Schema after conversion) and treat the guesser as a drafting aid.

Workflow: guess → decide → convert

A practical loop:

Paste CSV into the CSV schema guesser; confirm header toggle and delimiter summary.
Flag every mixed column and any integer/date column where samples show codes or locales.
Clean or document overrides (keep as string, parse dates with a known format, map booleans explicitly).
Convert with CSV to JSON only after type policy is decided—see CSV to JSON: headers, types, and edge cases.
Pretty-print or validate JSON with the JSON Formatter when the payload must match an API contract.

Sensitive customer CSVs benefit from local-only tools so profiling never hits a third-party upload—see why local-only matters.