How data operations work
The Cells, Columns, Rows and Table menus change what you select, a whole column with just a cursor, in one step you can undo, and tell you how much they changed.
Four menus have the operations that change a file. Each is named after what it changes:
- Table: the table as a whole. The Quality Check, turning columns into rows and rows into columns, summarizing a table, putting several files into one, and how the file is read: its delimiter, and whether the first row is the header.
- Rows: which rows there are, and where. Removing duplicate or empty rows, or the rows a filter hides or shows, giving each value of a cell a row of its own, and moving, copying and deleting rows.
- Columns: everything about columns. Sorting and filtering by the values of one, splitting, joining or merging columns, inserting, duplicating, moving and deleting them, and tidying up their names.
- Cells: what is written in the cells. Grouping similar values, rewriting numbers and dates in one way, trimming spaces, fixing capitals, removing or normalizing characters, and filling or emptying cells.
They are also in the command palette, each one named after its menu (Cells: Fill Down, Columns: Split Column…), so that Ctrl+Shift+P⇧⌘P and a few letters run any of them.
They all follow the same rules.
They act on what you select
- With just a cursor, an operation takes the cursor’s whole column, every row below the header. Click a value in the
citycolumn and trims every city. - With columns selected in the column bar, it takes every row of those columns below the header.
- With a selection, or several cursors, it takes the cells the selection touches, even partly. Select a few values, a box of columns or whole rows, and only those change.
- With everything selected, Ctrl+A⌘A, it takes the whole file, every column.
To pick out the cells to change, selects them by what they hold: the empty ones, the repeated ones, those that aren’t numbers in a column of numbers… (see Finding the cells to fix).
The header row is left alone, so that a column’s name never becomes a value, unless the whole selection is in the header row: then it is the only thing that changes, which is how you put the names of the columns in capitals or trim them.
is the exception: it always looks at the cursor’s whole column (see Grouping similar values).
Operations on rows (sorting, removing duplicates and removing empty rows) take the selected rows if two or more are selected, and otherwise every row, by the cursor’s column. Operations on columns take the columns the selection touches, or those selected in the column bar.
With a filter on, all of that is among the rows that show: the hidden rows are left as they are, whatever is selected. Only what changes the shape of the table (inserting, deleting, moving, splitting or joining columns, reshaping it, and putting files together) takes every row. See Filtering rows.
One step to undo
However many cells it changes, an operation is a single change: Ctrl+Z⌘Z puts everything back as it was, and Ctrl+Y⇧⌘Z does it again. That is true of files with a million rows too, and the history survives a reload, like the rest of it (see Undo and redo).
Operations with choices show their changes first
The operations that need you to choose something (, , , ), and , which can lose values, open a window with the choices, a few of the values as they are and as they will be, and how many cells or rows will change. The ones that change the table’s shape (reshaping it, , ) show the first rows of the result instead. Nothing changes until you press its button, and Esc leaves the file as it was.
It says what it did
The changes may be far from what you see, so each operation says, at the bottom of the window, how many cells or rows it changed and where: Trim Whitespace: 214 cells changed in “city”. If there was nothing to change, it says that too. Operations on columns don’t: you see them happen.
Rows, values and quotes
The operations work with rows and values, as the file’s format defines them, not with lines and characters:
- A value with line breaks inside it is one value, and its row is one row: it is sorted, moved or removed whole.
- Empty lines aren’t rows: they are left where they are.
- A value keeps its quotes, and gets them if the change needs them, for instance when a cell is filled with text that has a comma in it.
Outside the cells an operation takes, the file stays exactly as it was, byte for byte.