Joining files
Merge several CSV or Excel files into one: stack files with the same columns, or add another file's columns, matching on up to 3 columns.
What you need
- Two files that share a column, such as a customer ID or a SKU.
- Joining files on the spot, as in the steps below, needs the Pro plan or higher. Saving a join in a template works on every plan: Free keeps one reference table, paid plans keep more. See Plans and limits.
Steps
Drop the files together
Drop one or several files at once on the start screen. Files with the same columns are stacked under one another. Files that share a column get the extra columns added. With three or more files you see one plan: a stacking line, one line per extra list and a single Load … rows button. When the product cannot tell, it asks with two picture cards, Add rows and Add columns.
Check the sentence
It writes one sentence: add these columns from the second file to each row of your file where these columns match. It pre-fills the shared column; change it, or the columns to bring, right in the sentence. You can match on up to 3 columns, and all of them must match. Accents, punctuation and spaces are ignored, so Ana-María and ana maria match.
Review the match
Under the sentence you see how many of your rows find a match, and a preview of the new columns. Rows with no match keep empty cells; to remove them, change leave them empty to remove them in the count line. Nothing is applied until you click Load … rows. On the Free plan you see what the join would give, then choose See plans or Use only … to keep your first file alone.
Keep working on the joined file
The joined result replaces your file in the grid. Fix cells and add steps as usual. To repeat the join on every new file, save it in a template, as the next section shows.
Save a join in a template
The file you look values up in, such as a price list, is saved once in your account as a reference table. A template then adds its columns to every new file.
Drop the lookup file on your open file
Drop it anywhere on the page, or choose Add data › Add columns. The join opens in the command bar at the bottom, with the matching column and the columns to add already chosen. Change them there.
Add the step
Leave Save “…” for every run ticked and click Add step. The command bar then says what was added, for example Added Price from “prices”, matched on SKU, with Undo beside it.
Save the template, then reuse it
Click Save as template in the toolbar. Next month, open the new file and pick the template from the toolbar menu that reads No template: the same columns are added again.
Options
- To send the lookup file with each run instead, untick Save “…” for every run. When you apply the template, the command bar asks for that file. This needs the Pro plan or higher.
- Tick Also match close spellings to match Acme Ltd with Acme Limited. Codes whose digits differ never match: INV-1042 is not INV-1043.
- To keep a file as a table on its own, open it and click Save as Reference Table at the foot of the command bar.
Manage your tables
Open Reference tables in the sidebar. In each table’s menu you can Replace with a new file, Edit rows or Delete. Before a replace or a delete, it tells you which templates would break.
A script sends each join file as one more file with the run, and can refresh a table’s rows: see Runs.