Skip to main content

Missing-data problem

Standardize CSV Null Markers Without Losing Empty Strings

Distinguish missing values from empty text, zero and literal markers such as NA, N/A, NULL and none.

Use the matching tool

Find & Replace

Open Find & Replace

What the error actually means

CSV has no universal null type. Different systems use blank fields, NA, N/A, NULL, none or sentinel numbers. A literal value may be meaningful in one column and missing in another, so normalization must be column-aware.

Likely causes

  • Several source systems use different null markers.
  • A library automatically interprets legitimate text as missing.
  • Quoted empty strings and unquoted empty fields are treated differently downstream.
  • Zero or “unknown” was used as a placeholder.

Column-aware missingness

Problem

Replace every “NA” in the file

Correct pattern

Map “N/A” to missing only in documented columns; preserve state code NA if valid

A safe repair workflow

  1. 1Inventory distinct markers by column.
  2. 2Define missingness from the source and destination contracts.
  3. 3Replace only exact field values in selected columns.
  4. 4Recount nulls and compare key business totals after conversion.

How to verify the result

A file that downloads successfully is not automatically a correct file. Check the result at both the structural and business-data levels:

  • Zeros and false values remain.
  • Legitimate text equal to a marker is preserved.
  • Required fields do not gain unexpected nulls.
  • Destination null counts match the approved mapping.

Read the deeper guides

How to Clean CSV Data Without Hiding the Original ProblemsLearn practical techniques to clean messy CSV files. Remove duplicates, fix formatting, and standardize data without Excel crashes or complex scripts.How to Prepare CSV Files for Python PandasPrepare CSV files for reliable pandas imports by making encoding, delimiters, nulls, dates, identifiers, and column types explicit.