cleaning data

Recipes Desktop app

A recipe is the operations you did on one file, saved to do them again on another, such as the export that arrives every week, or on a whole folder of files. Recipes come with the desktop app, which is on its way.

Cleaning the same kind of file again and again, such as an export that arrives every week, means repeating the same operations each time. A recipe saves them once and does them again on the next file, in one go, or on every file of a folder.

The steps of a tab

Trusty CSV writes down each operation you do on a tab’s data: the commands of the Table, Rows, Columns and Cells menus, the fixes of the Quality Check, merging values in the column profile, and replace all in Edit › Replace. These are the tab’s steps. There is nothing to start recording: the list begins with the first operation you do on the file.

What you type by hand isn’t a step, with one exception: a name typed in the header row. A column you insert and then name keeps its name in the recipe, and a column you rename is a Rename step, so the steps that come after it find their column in the next file.

Undoing an operation takes its step out of the list, and redoing it puts it back. The list stays with the tab when you close the app and open it again, like the tab’s undo history. It ends when you close the tab, and File › Revert File empties it, since the text is the file’s again.

Seeing the steps

Table › Recipes › Recipe Steps opens the list at the right of the editor, where the column profile and the Quality Check go. It is what a recipe saved now would do, and it fills in as you work, each step in words: “Trim Whitespace in “city””, “Sort by “pop”, descending”.

  • Click a step to take the file back to how it was just after it. The steps after it stay in the list, greyed; click one of them to do them again up to there. It is the undo history, read as a list: Ctrl+Z⌘Z and Ctrl+Y⇧⌘Z move through the same changes.
  • Before the first step takes the file back to before all of them.
  • A step that can’t be repeated on another file says so; point at it to see why.
  • Save as Recipe… is at the bottom, and Clear, which empties the list without touching the text: use it to save as a recipe only what you do from then on.

Saving a recipe

Table › Recipes › Save Steps as Recipe… lists the steps of the tab, in order, each with a check box. Choose the ones the recipe should repeat, give it a name and save it.

Some steps can’t be repeated on another file, and say so:

  • an operation done on cells you selected by hand, since those places mean nothing in another file;
  • Table › Append Rows from Other Tabs… and Table › Join with Another Tab…, which take their rows from another file;
  • pasting into columns.

To make an operation repeatable, do it on whole columns (with the cursor in the column, or the columns selected in the column bar) or on the whole table (Ctrl+A⌘A).

Columns dragged in the column bar are a step too: it remembers the column they landed before (or after, at the end), and finds it by its name in the other file.

Applying a recipe

Open the other file and choose Table › Recipes › Apply Recipe…. Before anything changes, the window shows what each step would do to this file: how many cells or rows it changes, or why it is skipped.

  • Columns go by name, not by place: a step on “city” is done on the column called “city”, wherever it is. In a file without a header row, columns go by their place.
  • A step whose column the file doesn’t have is skipped; the others are done.
  • A step done with a filter on keeps its filter: “UPPERCASE in “city”, in the rows where “country” is “USA””.
  • The fixes of the Quality Check run the check again on the new file, and fix what they find there.

Apply does every step as a single change: one Ctrl+Z⌘Z takes the whole recipe back. The message that follows has a Show Changes button, which compares the file with what it was before the recipe.

The steps a recipe did go into the tab’s own list, so you can add more operations and save a longer recipe.

Applying a recipe to a folder

Table › Recipes › Apply Recipe to a Folder… does a recipe on every CSV file of a folder, without opening any of them. Choose the recipe and the folder; the window says which files it found (.csv, .tsv and .tab, the ones right in the folder).

The files themselves aren’t touched. The results go to a new folder inside the one you chose, named after the recipe (“weekly results”), and a second run makes another (“weekly results (2)”) instead of writing over the first.

  • Every file ends up in the results: the ones the recipe changes, rewritten, and the ones it finds nothing to change in, copied as they are. The folder of results is the whole set.
  • Each file is read as when you open it and written back the same way: its encoding, its delimiter and its line breaks stay.
  • At the end, a list says what happened to each file: how many steps were done, how many were skipped, or that it was copied as it was. Point at a file to see it step by step.

Keeping, changing and sharing recipes

Table › Recipes › Manage Recipes… lists your recipes: rename one, delete it, or Export… it to a file to take it to another computer or give it to someone. Import… reads a recipe from such a file.

Edit… opens a recipe’s steps. Remove a step, move it up or down, or change its texts: the separator of a split, what empty cells are filled with, what a replace all finds and writes, the name of a column, the order of a sort. What a step is done on, its columns, can’t be changed there: do the operation again on a file and save the steps as a new recipe.

Recipes are kept on your computer, with the app’s settings.