Data & spreadsheets · CSV Cleaner
Why Empty Strings, NULL and Missing Fields Are Not the Same Thing in CSV
· Why it matters
csv json data-formats
CSV has no way to say 'no value'; it only has text. This post explains how empty fields, quoted empty strings, literal NULL and short rows differ, why loaders disagree about them, and how conversion to JSON forces the question.
The same blank cell becomes NULL in one system and an empty string in another — why that changes joins, counts and averages
Blank-looking source cells can carry several histories, but ToolAcre’s JSON output deliberately narrows them to one representation. An empty field, quoted empty field and short row padded by the parser all become the empty string under a present key. No null is inferred from appearance.
That consistency is useful only when the consumer knows it. Code testing specifically for null will not substitute a default for "". Decide the missing-value contract at the receiving boundary instead of assuming the converter preserved distinctions that CSV syntax or its chosen parse model did not retain.
In ToolAcre, a blank and a padded missing cell both become empty strings unless a consumer reinterprets them
CSV can contain two delimiters with nothing between them, an explicitly quoted empty string, literal text such as NULL or NA, and a row ending before the header width. ToolAcre parses the first two as empty strings and leaves the literal tokens untouched as ordinary text.
For a short row, it emits a `RAGGED_ROW` warning and pads absent trailing cells with empty strings. For a long row, it retains extra cells in the table, but JSON conversion has no header key for them and omits them. This asymmetry is why the structural warning requires action before conversion.
How common loaders interpret each — the differing defaults in spreadsheets, databases and data-frame libraries
Different database clients and analysis libraries expose their own configurable null tokens and empty-field policies. This repository cannot establish those defaults, so the article does not claim that all spreadsheets or loaders agree. It documents the one implementation whose source and tests are available.
When handing the file to another system, read that importer’s settings and create fixtures for ``, `""`, NULL and a short row. A reliable contract is demonstrated at the actual boundary. General folklore about what “CSV normally means” is too weak for missing values.
Loader defaults vary; this article documents ToolAcre rather than generalizing spreadsheet or database behavior
The text NULL is especially hazardous because it can be genuine content or a sender’s sentinel. ToolAcre has no configured sentinel list and therefore preserves it as the four-character string "NULL". The same is true for NA, N/A, zero and any other token a producer might use.
Do not globally replace such strings without a field-specific rule. A notes column could legitimately contain “NULL”, and a code column could use NA. Resolve meaning with producer documentation or a schema, then transform only the appropriate columns under review.
JSON makes you decide — null, empty string and absent key are three different things once the file is converted
JSON distinguishes null, an empty string and an absent property, but ToolAcre chooses present keys with string values for every header column. Missing or padded cells become "". The converter does not produce JSON null and does not omit a key merely because a cell is empty.
A surplus cell is different: with no corresponding header, there is no key to assign, so it is absent from generated objects. The row warning tells you that loss is possible. Fix the header or row width before download rather than reading absence as a meaningful null policy.
ToolAcre emits empty strings and present keys, not null or absent keys
Use headers id,note,code and four records: `1,,A`, `2,"",NULL`, `3,hello` and `4,world,B,extra`. The first two notes become empty strings; code in row two remains "NULL". Row three receives code "" after padding, while row four warns that an extra cell has no key.
The resulting objects visibly carry the same three keys for represented columns. This worked case is more informative than a screenshot of blank cells because it connects parse warnings to JSON shape. It also shows why literal tokens require a separate domain decision.
What this does not cover — imputation and business rules for what a missing value should default to
Imputation is outside the route. The converter does not fill missing prices, copy previous values or infer defaults from neighboring rows. Those changes depend on business meaning and should occur after a schema distinguishes unknown, inapplicable and intentionally empty values.
It also does not preserve the syntactic distinction between an unquoted empty field and `""`; both parse to the same cell string. If that distinction matters, CSV is not carrying it through this parser’s table model. Choose a representation with an explicit state or preserve the raw source alongside derived data.
Decide what blank means before you load — how the ToolAcre CSV Cleaner's CSV-to-JSON conversion makes the representation visible
Decide what blank means before loading the JSON into typed code. ToolAcre gives a transparent baseline: string values everywhere, empty strings for missing represented cells, literal sentinel words unchanged and warnings where row shape exceeds the available keys.
Inspect those warnings and document any later coercion. A converter cannot create missing-value semantics from text alone, but it can avoid hiding its own choices. That predictable behavior makes the next boundary responsible for the policy it is actually qualified to enforce.