Missing values and stray spaces — Trusty CSV User Guide

Missing values

NA, N/A, #N/A, null, NaN, none, nil, \N, -, --, ? and ., in capitals or not, are drawn in grey italics instead of their column’s colour, as databases show a NULL. That is how you tell them from real values at a glance, in any column, and they are also why a column of numbers with an NA in it still lines up as numbers. Names in the header row are never greyed.

Cells › Fill and Clear › Empty Missing Values (NA, null, -…) empties them all, so that the file has one way of saying “nothing”: see Empty and repeated values.

Spaces at the edges

A dot marks every space at the start or end of a value: a London with a space after it trips up a search, a join with another file or a duplicate check. Spaces between words aren’t marked, and inside a value with line breaks, only the spaces at the very start and end of the whole value are.

contacts.csv

9

1

2

3

4

5

6

7

id,name,city,email

1,ada lovelace,London,ada@example.org

2,grace hopper,New York,grace@example.com

3,alan turing,Manchester,alan.turing@example.org

4,hedy lamarr,Vienna,hedy@example.at

5,katherine johnson,Hampton,kj@example.com

id,name,city,email

5 rows × 4 columns

PlayA dot marks each space at the start or end of a value.

Spaces that aren’t spaces

A small ring marks every non-breaking space, and every other space that isn’t the ordinary one (thin spaces, en and em spaces and the like), wherever it is in a value. They come in with text copied from web pages, PDFs and word processors, and they look like a space but don’t compare like one. Rest the pointer on the ring to see its name and code.

Cells › Whitespace › Trim removes them at the edges of a value, Cells › Whitespace › Collapse also turns them into ordinary spaces between words, and Cells › Whitespace › Remove All removes them all: see Whitespace and case.

Characters that take no room at all, such as zero-width spaces, can’t be marked: Select Values with Unusual Characters finds them, together with these spaces.

The marks are only drawn: like the alignment, they are not in the file.