CSV data
Keep missing values distinct from empty CSV cells
Use a documented status column so unknown, intentionally blank and present CSV values survive export without turning ordinary text into a hidden code.
What does an empty cell mean? In one export it means nobody asked. In another it means the person chose to leave the field blank. Once both land in the same CSV column as nothing between two commas, the difference is gone, and no later tool can recover it.
This guide proposes a local convention, not a standard: a companion status column that records why a value is or is not there. It works only where everyone who handles the file agrees to it.
See what the format leaves out
RFC 4180 describes CSV syntax: records separated by line breaks, fields separated by commas, optional double quotes, and an optional header line. It defines no data types, so it has no way to mark a field as missing rather than empty. Some importers treat an empty quoted field differently from an unquoted one, but that is importer behavior, not part of the format.
The W3C tabular data model separates a cell's raw string from its interpreted value. It lets metadata declare which strings should be read as null. That helps when both sides read the metadata, but it still gives a literal string a special meaning.
Sources: RFC 4180: Common Format for CSV Files, W3C: Model for Tabular Data and Metadata
Avoid codes that look like data
Writing NA, NULL or -1 into an empty cell seems simple until a real value matches. A literal word such as NULL can be legitimate text, and -1 can be a valid reading. A convention that never gives special meaning to what is inside a data cell cannot collide with real data.
Add a status column for each field that needs one
For a field named phone, add phone_status. Allow exactly three values:
- present: the phone cell holds the value as given.
unknown: the value was never collected or cannot be confirmed.
blank: the value was deliberately left empty.
The status words live only in the status column, so they never compete with real phone data.
Then define the valid pairs. Unknown and blank require an empty phone cell, and present requires a non-empty one. Any other combination, or any status word outside the three, is an error. Report it rather than guess.
Read a small example
A fictional volunteer roster starts with the header line id,name,phone,phone_status. Six rows follow: three present, two unknown and one blank. Row 104 belongs to someone surnamed Null. That causes no confusion, because no cell value carries a special meaning.
| Raw CSV line | Meaning |
|---|---|
| 101,Ana Ruiz,555-0142,present | Phone known |
| 102,Bo Lind,,unknown | Not asked yet |
| 103,Cy Okafor,,blank | Declined to give one |
| 104,Kim Null,555-0177,present | Phone known; the surname is ordinary text |
| 105,Eli Park,,unknown | Not asked yet |
| 106,Gus Moreau,555-0199,present | Phone known |
Agree downstream and map to JSON deliberately
Write the convention into the data dictionary that travels with the file. List which fields have status columns, the three allowed words and the valid pairs. Ask each receiving team to confirm that they validate it before they rely on it.
JSON has a null that is distinct from an empty string, so mapping unknown to null and blank to an empty string looks natural. That mapping works only if both sides agree that null means unknown, not withheld or not applicable. An omitted key adds a fourth possibility. Carrying phone_status into the JSON keeps the meaning explicit.
Checklist and limits
The convention has limits. It adds columns. Someone editing a spreadsheet can change a value without updating its status. It cannot repair files exported before it existed. If every system involved reads tabular metadata, a declared null may be simpler.
| Check | Pass when |
|---|---|
| Coverage | Every field where an empty cell is ambiguous has a status column |
| Vocabulary | Only present, unknown and blank appear in status columns |
| Pairs | No unknown or blank row has a value, and no present row is empty |
| Documentation | The data dictionary ships with the file |