How to use the csv null and missing value normalizer
- Paste your CSV, with a header row if it has one, and choose the separator.
- List the values that mean missing, separated by commas, such as N/A, null and a dash.
- Type what should replace them, or leave it blank for an empty cell.
- Read the counts for each column, then copy the cleaned CSV or download it.
Formula
A cell matches when its trimmed text equals an item in your list
Case is ignored unless you tick Match upper and lower case
Optionally, a cell holding only spaces is also replaced
The header row is left alone when First row is a header is ticked.
A small customer file
The sample has four columns and five rows. The second row has N/A as a name and a dash as a score, the third row has null as a city, the fourth has a city of spaces and N/A as a score, and the fifth has #N/A as a score. With the default list the tool replaces six cells with nothing, out of 20 data cells, and the table shows one or two replaced cells in each column with the values it found.
The cleaned CSV keeps rows with empty cells in place, as 2,,Porto, with nothing between the commas. If you type NA as the replacement, those same cells read NA instead.
Why missing values cause trouble
A database column that expects a number will refuse N/A, and a spreadsheet may treat a dash as text, which breaks a sum. Different systems mark a missing value in different ways: an empty cell, NULL, N/A, a dash, a question mark, or a number such as -999. Cleaning them to one form before an import avoids errors and makes counts of missing values honest.
The list is yours to edit because the markers differ from file to file. If a column uses 0 or -999 as a marker, add it, but check first that real zeros are not in the same column.
Reading the report
The table lists each column with the number of cells replaced and the values found, such as N/A x3. If a marker shows up in a column you did not expect, you may have a field shifted by a stray comma. Real values that look like markers are replaced too, for example a person whose surname is NA, so look at the counts before you use the file.
To check the structure of the file, use the CSV Validator. To keep only the rows that matter, the CSV Row Filter Tool filters them. The CSV Statistics tool shows counts and averages per column, and the Find and Replace Rule Table applies a longer list of rules. To write the result at fixed widths, use the CSV to Fixed Width Converter.
Assumptions and limits
- The file follows the usual CSV quoting. A quote inside a quoted cell is written as two quotes.
- Matching is on the whole cell after trimming spaces, not on part of the text.
- Blank lines are dropped, except in a file with one column.
- The result uses the separator you chose and a line feed between records.
Frequently asked questions
Does it change cells that only contain part of a marker?
No. A cell is changed only when its whole trimmed text equals a marker, so N/A inside a longer sentence is left alone.
Can a marker contain a comma?
Not in this list, because commas separate the items. Replace those with another tool first.
What about empty cells?
They stay empty. If you choose a replacement such as 0, only the markers in your list change, not the cells that were already empty.
Is my data uploaded?
No. The CSV is read and cleaned in your browser.