How data operations work — Trusty CSV User Guide

Data Operations Overview

Four menus have the operations that change a file. Each is named after what it changes:

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

To pick out the cells to change, Selection › Select Cells 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.

Cells › Group Similar Values… 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 (Cells › Reformat Numbers…, Cells › Reformat Dates…, Columns › Split Column…, Columns › Join Columns…), and Columns › Merge Columns…, 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, Table › Append Rows from Other Tabs…, Table › Join with Another Tab…) 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:

Outside the cells an operation takes, the file stays exactly as it was, byte for byte.