Inferring a Table's Schema From a CSV With an LLM

Deterministic type detection gets you most of the way and then stops at the columns where the answer depends on knowing what the column is. A model is good at that specific judgement and bad at being trusted, so the script below asks it and then checks its homework.

Why a model rather than a rule

A regex cascade can tell you that a column is all digits. It cannot tell you whether a column of nine-digit integers is an amount in cents, a phone number, a zip code that has lost its leading zero, or an opaque account id that must never be treated as a number. That distinction depends on the column name, the neighbouring columns and general world knowledge, and it is the one part of schema inference where a language model has a genuine advantage over the cascade described in automatic column type detection.

It is also the part where a wrong answer is expensive and silent, so the design here is: deterministic detection first, model only for the ambiguous residue, and every model answer re-checked against the actual bytes in the file.

Sampling the values that matter

Do not send the file. Send a profile. For each column you want: the header, a count of non-null values, the number of distinct values, and a sample of the values themselves — and the sample should not be the first twenty rows, because the first twenty rows of a CSV are almost always the cleanest ones. Take a stratified sample: the most common values, plus the rarest, plus the longest and shortest by string length. Anomalies live at the extremes.

import pandas as pd

def profile_column(s: pd.Series, k: int = 8) -> dict:
    vals = s.dropna().astype(str)
    common = list(vals.value_counts().head(k).index)
    by_len = vals.reindex(vals.str.len().sort_values().index)
    extremes = list(dict.fromkeys(list(by_len.head(3)) + list(by_len.tail(3))))
    return {
        "name": s.name,
        "non_null": int(s.notna().sum()),
        "null_frac": round(float(s.isna().mean()), 4),
        "distinct": int(vals.nunique()),
        "common_values": common,
        "extreme_values": extremes,
    }

Ten columns profiled this way is a few hundred tokens. The same ten columns as raw rows is unbounded. That difference is the whole reason this approach scales, and it is the same argument made at more length in what an LLM can and cannot do with a spreadsheet.

Constraining the answer to a vocabulary

A free-text type answer is unusable — you will get "integer", "int64", "whole number" and "numeric (id)" from four calls on the same file. Fix a vocabulary, put it in the prompt, and enforce it in code. Ask for structured output if the provider supports it, and validate the shape regardless, because a schema-constrained decode still leaves the model free to pick the wrong member of the enum.

TYPES = [
    "integer", "decimal", "boolean", "date", "datetime",
    "categorical", "identifier", "free_text", "mixed",
]

SYSTEM = f"""You infer column types for tabular data.
Return JSON only: an object with key "columns", an array of objects with
keys: name, type, reason, confidence.
"type" must be exactly one of: {", ".join(TYPES)}.
Rules:
- "identifier" for values that are numeric but must never be averaged
  (account numbers, order ids, zip codes, phone numbers).
- "categorical" only if distinct count is small relative to non_null.
- "mixed" if the sampled values are not all the same underlying type.
- "confidence" is a number from 0 to 1.
Do not guess a type you cannot support from the sampled values."""

The identifier member is the one that earns the model its place in the pipeline. It is the answer no deterministic rule reaches, and it is the one that prevents somebody computing the mean of a column of zip codes six months later.

Validating the answer against the file

Every returned type is a hypothesis you can test cheaply against the full column, and this is the step that distinguishes a working script from a demo. If the model says integer, try to cast the whole column and count the failures. If it says date, parse it and count the failures. If more than a small fraction fail, the model was wrong or the column really is mixed — and either way you now know, from the file, rather than from the answer.

def check(series: pd.Series, inferred: str) -> dict:
    vals = series.dropna()
    if len(vals) == 0:
        return {"ok": False, "note": "column is entirely null"}
    if inferred in ("integer", "decimal"):
        bad = pd.to_numeric(vals, errors="coerce").isna().sum()
    elif inferred in ("date", "datetime"):
        bad = pd.to_datetime(vals, errors="coerce", format="mixed").isna().sum()
    elif inferred == "boolean":
        ok_set = {"true","false","t","f","yes","no","y","n","0","1"}
        bad = (~vals.astype(str).str.strip().str.lower().isin(ok_set)).sum()
    else:
        bad = 0
    frac = float(bad) / len(vals)
    return {"ok": frac <= 0.01, "bad_frac": round(frac, 4), "bad_count": int(bad)}

A bad_frac between roughly 0.01 and 0.2 is the interesting case and the one the angle of this page is about: it is what a mixed- type column looks like. A column that is 94% parseable dates and 6% the literal string N/A is not a date column and not a text column; it is a date column with a sentinel in it, and the correct output is a flag for a human, not a coerced cast that silently turns those rows into NaT.

The whole thing

1. Read the file with everything as strings — pd.read_csv(path, dtype=str, keep_default_na=False, na_values=[""]) — so that pandas’ own inference does not destroy the evidence before you look at it.
2. Profile every column with profile_column above. Serialise the list of profiles as JSON.
3. Send one request containing the system prompt and the profiles, with temperature 0. One request for the whole file, not one per column: the model needs the neighbouring column names to distinguish an amount from an id.
4. Parse the response, and reject any row whose type is not in TYPES or whose name is not a real column. A hallucinated column name is the most common malformed response and the easiest to catch.
5. Run check() on every column. Where ok is false, downgrade the type to mixed and keep the bad_frac.
6. Emit the schema, with a separate list of columns needing review: anything downgraded to mixed, anything the model returned with confidence below about 0.7, and anything typed identifier that a downstream consumer might average.

Where this stops working

The model sees a sample, so anything whose evidence is outside the sample is invisible to it. A column that is clean for 400,000 rows and then contains a free-text apology in row 400,001 will be typed decimal with high confidence, and only the check() pass over the full column catches it. That is the argument for keeping the validation step even when the model seems reliable.

Wide files are the other limit. Two thousand columns of profiles will not fit comfortably in one request and, more importantly, quality degrades before length does — the model attends less carefully to column 1,700 than to column 3. Batch wide files in groups of 50 to 100 columns, and repeat any column whose name suggests it relates to a column in another batch. Finally, treat the whole thing as advisory for anything regulated: an inferred schema is a starting point for a data contract, not a substitute for one.