How to Find Missing Values in a CSV File
NULL, an empty string, and a value that's just whitespace look similar in a spreadsheet, but they are three different things — and which one you're actually looking at changes what a null-percentage number means.
Three different kinds of “missing”
- NULL — no value at all. In a CSV, this is a field with nothing between the delimiters:
a,,c. - Empty string — a value that exists and is zero-length, e.g. an explicitly quoted
a,"",c. - Whitespace-only — a value that exists and has content, but the content is only spaces or tabs:
a," ",c.
The gotcha: CSV readers erase the empty-string case
A CSV reader that turns every blank field into NULL — which is exactly what DuckDB's own CSV reader does — means a CSV-sourced column's “empty string count” reads as zero by default, always, regardless of what the original file actually contained. This isn't a bug to fix; it's a real property of how CSV gets parsed, and a profiler or quality checker built on top of it needs to state this plainly rather than report a confusing zero with no explanation. Parquet, by contrast, can store a real empty string distinctly from a real NULL, because its type system actually has room for the distinction.
Why whitespace-only needs its own count
A value like " " is not NULL and not empty — it has three space characters in it — but for almost every practical purpose it should be treated as missing. Counting it separately from true NULLs, rather than either ignoring it or silently merging it into the NULL count, preserves the actual fact (“this column has 40 NULLs and 12 whitespace-only values”) instead of rounding it away.
Finding all three
A profiler reports null count and percentage per column, plus empty and whitespace-only counts computed the way described above. A data quality check turns a high missing percentage into a specific finding, with severity, count and a suggested action, rather than just a raw number.
Normalizing before you export
Once you know what you're dealing with, a cleaner can turn a whitespace-only value into a real NULL as one explicit pipeline step — visible, ordered, and only applied when you add it — so the exported file's missing-value count actually means what you think it means.
Try it: CSV Data Quality Analyzer
Detect potential data problems in a CSV, each with severity, count, explanation and a suggested action.