Import Recipes

An import recipe records how you clean a file: which columns to keep, what to rename them, which types they must have, which rows to drop. Save it once, and next month’s export loads into the same Table with Refresh. If the new file has changed in a way the recipe can’t handle, the refresh stops, the Table keeps its last good result, and VisiGrid tells you exactly what changed.

Recipes start in VisiGrid 0.45.0. The source is a CSV or other delimited text file; from 0.47.0 it can also be a sheet in an Excel workbook (.xlsx, .xlsm, .xls), a Parquet file, or a table in a DuckDB database. From 0.51.0 it can be a JSON file (.json, .jsonl, .ndjson), and from 0.50.0 a VisiBooks report.

Make a recipe

Start from any of these:

• Data → New Import Recipe…, or New Import Recipe… in the command palette (Ctrl+Shift+P). With a CSV open, the recipe starts from that file and the settings it was imported with; otherwise you choose a file.
• Make a recipe… on the banner that appears after opening a CSV.
• New Import Recipe… with an Excel or Parquet file open, or choose an Excel, .parquet, .duckdb, .json, .jsonl or .ndjson file when it asks. An Excel recipe starts on the first sheet with the header row guessed below any title rows; a DuckDB recipe starts on the database’s first table.
• New Recipe from VisiBooks… in the command palette, to read a report from your books. See VisiBooks reports.

The recipe builder has three columns.

Source (left): the file, and for a CSV the delimiter, the encoding, the line that holds the column names, and the decimal mark. An Excel source has Sheet and Header row settings instead (the sheet is saved by name, so reordering sheets is safe); a DuckDB source has a Table setting; a JSON file has Records at; a Parquet file needs no settings. Exports often start with a title or a “generated on” line; VisiGrid guesses the header line below them and shows the lines it skips. Below the settings are the columns the file has. They are saved with the recipe, and next month’s file is checked against them.

Steps (middle): the steps run in order. Each one shows its rows in and out, how long it took, and what it did (“removed 2 rows”, “trimmed 4 values”). Click a step to preview the data after it.

Preview (right): the selected step’s settings, and the data after that step. Counts are for the whole file; the preview shows the first 200 rows.

Key	In the builder
A	Add a step
↑ ↓	Choose a step
Enter	Edit the selected step’s settings
Space, ← →	Change a setting
Delete	Remove the selected step
Ctrl+↑ Ctrl+↓	Move the selected step
Tab	Move between source, steps and settings
Ctrl+S	Save the recipe
Ctrl+Enter	Save and load into a Table (or save and refresh)
Esc	Back to the steps; close (asks once if there are unsaved changes)

Steps

Step	What it does
Keep columns	Keeps the checked columns, in the order you check them, and drops the rest.
Remove columns	Drops the checked columns.
Rename columns	Gives columns new names.
Set types	Declares a column as text, number, or a date (YYYY-MM-DD, DD/MM/YYYY or MM/DD/YYYY). Every value is checked on every run.
Trim spaces	Removes spaces at both ends of values, in the checked columns or all of them.
Filter rows	Keeps the rows where a condition holds: is, is not, greater/less than, contains, starts with, is empty, is not empty.
Remove duplicates	Keeps the first of each set of identical rows, comparing the checked columns or the whole row.
Group by	One row per group of the checked columns, with totals: sum, count, count rows, average, min, max, distinct count, first or last of a column, each with its own name. From 0.48.0.
Unpivot	Keeps the checked columns and turns every other column into rows of two new columns (by default Attribute and Value). From 0.48.0.
Sort rows	Orders the rows by one or more columns, each ascending or descending. From 0.49.0.
Fill down	Fills each empty cell in the checked columns with the value above it. From 0.49.0.
Replace values	Replaces a whole cell, or text inside cells, with something else, in the checked columns or all of them. From 0.49.0.
Split column	Splits a column at a delimiter into new columns that take its place. From 0.49.0.
Merge with a recipe	Joins the rows with another recipe’s result on key columns, like Merge Queries or a VLOOKUP over a whole column. From 0.50.0.

Group by keeps groups in the order they first appear. A sum or average of a value that isn’t a number fails the run and says which line; min and max compare numbers as numbers and dates as dates. Group by no columns to total every row into one.

Unpivot is “unpivot other columns”: the columns you keep stay, and every other column becomes rows, so when next month’s file adds a column (a new month, a new region) it is unpivoted too, without editing the recipe. Empty values are left out unless you choose to keep them as empty rows.

Sort rows sorts each column as its type: numbers as numbers (so 100 comes after 9), dates as dates, and text ignoring case. IDs such as 007 stay text. Values that don’t fit a typed column come after the ones that do, and empty values always go last, ascending or descending. Rows that tie keep their order. In the builder, click a column to sort by it, again for descending, and again to stop; the first column you choose decides first.

Fill down is for reports that print a group’s name only on its first row. It never fills across appended files: the first rows of each file start with nothing above them.

Replace values matches a whole cell by default, ignoring case, so n/a, N/A and N/a are all replaced. Leave Find empty to fill empty cells (for example with 0). Switch Match to Text inside cells to replace every occurrence within a value, and Case to Must match to compare exactly.

Split column splits at the first delimiters only, as many as it needs for the new columns, so the last new column keeps the rest of the value and nothing is lost. Smith, J, Jr split at , into Last and First gives Smith and J, Jr. A value with fewer pieces leaves the later columns empty, and the step notes how many. + Add a column adds another piece; clearing a third or later name removes it.

Merge with a recipe joins this table with another recipe’s result: a budget beside the trial balance, a customer’s region beside each order, last month beside this month. The other side is a recipe rather than a file, so it can read anything a recipe can and clean it with its own steps. Choose it with Merge with a recipe in Add step; VisiGrid keys on a column both tables have when there is one. In the step’s settings:

• Key: this table’s column and the other’s that must match. Add more for a key of several columns. Keys compare as their columns are typed: numbers by value (10 matches 10.0), dates by day, text exactly (so trim first); IDs such as 007 stay text and don’t match 7.
• Keep: every row here (the other’s columns are empty where nothing matched), only rows in both, every row of both (rows only in the other table get their key filled in), or only the rows here with no match: what’s missing.
• If the other table has a key twice: fail the run (the default; a match would quietly duplicate rows, the classic VLOOKUP mistake), or use the first.
• Bring: the other table’s columns to add; all of them by default. A name this table already has gets the other recipe’s name added, like Amount (budget).

Every run says how many rows matched, how many are only here and how many only in the other table, and a merge that matches nothing fails rather than loading empty columns. The first time, VisiGrid asks before reading, naming the other recipe and what it reads as well.

Each step also says what happens when a column it names is missing from the file: fail the run (the default), skip this step, or treat it as empty. Set types also says what happens to a value that doesn’t fit its type: fail the run (the default), keep the text, or leave the cell empty.

Excel, Parquet and DuckDB columns keep the types the file declares: numbers stay numbers, dates stay dates, date-times and times keep their time, and text stays text, exactly as when you open the file. A Set types step on a column replaces its type. From Excel, a recipe reads the values Excel saved; it never recalculates formulas, and dates formatted as dates in Excel come through as dates.

CSV columns you don’t declare a type for follow the same safe defaults as opening a CSV: IDs such as 007 keep their leading zeros, nothing becomes a date by guessing, and a value starting with = always stays text. A recipe never evaluates formulas.

Save and load into a Table

Save and load into Table (Ctrl+Enter) saves the recipe as a .recipe.toml file and opens the result as a Table named after the recipe. A strip above the sheet shows where the Table comes from:

Table orders · from recipe orders.recipe.toml · refreshed just now from export-2026-09.csv · 4 rows

Opening a .recipe.toml with Ctrl+O does the same: it runs the recipe and opens the result as a Table. If you edit the open workbook while the recipe runs, it is not replaced; save your work and open the recipe again.

Save the workbook as a .sheet file and it keeps the link to its recipe. A recipe in the workbook’s folder (or below it) is linked relative to the workbook, so you can move or share the folder and the link still works (0.48.0 and later). Workbooks with a recipe-linked Table use a newer Table format; releases before 0.45.0 open them read-only instead of dropping the link.

Refresh next month

Click Refresh on the strip, or press Alt+F5 with the cursor in the Table (elsewhere, Alt+F5 still refreshes the pivot under the cursor). Data → Refresh Table from Recipe works too.

Refresh reads the file again, runs every step, and replaces the Table’s records only if every check passes. The Table grows or shrinks to fit, the header row and first column stay where they are, and the formatting of the first record carries down to every record. One Ctrl+Z undoes the whole refresh.

Pick up next month’s file by itself

Monthly exports usually have a new name each month. In the builder, set Each refresh reads to Newest match: the recipe then reads the most recently modified file matching a pattern such as export-*-*.csv (each run of digits in the name becomes *), in the same folder. You can also write a pattern by hand; * matches any run of characters and ? one character.

Files that are still arriving are left alone: partial downloads (.crdownload, .part, .download, .tmp) and hidden files never match, and if the newest match changed in the last two seconds, Refresh asks you to try again in a moment instead of reading half a file.

Append a folder of files

Set Each refresh reads to All matching files to read every file the pattern matches, in name order, and stack them into one table (0.48.0 and later; CSV, Excel and Parquet, and JSON from 0.51.0). Columns line up by name. A file without one of the columns is empty there, and the builder notes which file. A Source file column says which file each row came from, so a later Group by can total per file. If a value doesn’t fit its type, the message names the file as well as the line.

For a one-off, Choose file… on the strip reads another file. A file the pattern already matches is read once without changing the recipe; any other file becomes the recipe’s source.

When a refresh is blocked

If the new file doesn’t pass, nothing in the sheet changes. A banner says why, and offers a fix where there is one:

• A column is missing. If a new column sits where the old one was, VisiGrid suggests it as a rename. Use Order Number (Ctrl+Enter) saves that into the recipe.
• Values don’t fit a type, for example a date written 10/03/2026 in a YYYY-MM-DD column. The banner lists the lines and values, and offers Keep as text or Leave blank for that step.

Each fix is saved into the recipe and the run is retried against the same copy of the file, so what you checked is what gets loaded. Edit recipe opens the builder; Read the file again reads the file from disk again.

To stop refreshing a Table, run Unlink Table from Recipe from the command palette. The records stay and it becomes an ordinary Table; Ctrl+Z relinks it.

Refresh refuses to grow a Table over cells that aren’t empty. It also refuses Tables with calculated columns, because the recipe replaces every column; keep calculations in columns next to the Table.

JSON files

A recipe can read a JSON export (0.51.0 and later): a .json file from an API or a download, or a JSON Lines file (.jsonl, .ndjson) with one record on each line. Each record becomes a row. For a walkthrough with a sample file and screenshots, see Import JSON Files.

Records at says where the records are in a .json file. When you make a recipe, VisiGrid finds them: the array at the top of the file, or else the largest array of records inside it, such as invoices in {"count": 44, "invoices": [...]}. The recipe saves that path, so next month’s export reads the same records even if another array in it grows larger; the run fails if a later file has nothing there. Press Enter on Records at to choose another of the file’s arrays, or Found by itself to look again on every run. A file that is a single object loads as one row. A JSON Lines file always has one record per line, and an error names the line.

• Nested objects become dotted columns: {"customer": {"name": "Acme"}} gives a column customer.name. Columns come in the order they first appear in the file, and a field some records leave out is empty in those rows.
• Numbers stay numbers when every value in the column is a JSON number. An ID too large to be exact as a spreadsheet number (above 9,007,199,254,740,992) keeps the column as text, so no digit is lost. Numbers written as strings, like "10.50", stay text as written; add a Set types step to make them numbers.
• true and false become TRUE and FALSE; null is an empty cell; a list such as [1, 2] is kept as its JSON text in one cell.
• Dates in JSON are strings, so they arrive as text. Add a Set types step (date:ymd for 2026-10-01) to make them dates.
• A key that appears twice in one object keeps its last value, and so does a column two fields both make ("a.b" and "a": {"b": ...}). The run report notes either one.

A .json file is read whole before its rows are built, so it needs more memory than the same data as CSV: about 1.6 GB for a 170 MB file of a million records. A JSON Lines file is read one record at a time and needs about as much as CSV (under 800 MB for the same million records). For very large exports, ask for JSON Lines if the system offers it.

VisiBooks reports

A recipe can read a report from VisiBooks instead of a file (0.50.0 and later): the trial balance, the general ledger (one row per journal line), or the AR or AP aging (one row per open invoice or bill), for one entity. It loads into a Table like any other source, the steps work the same way, and Refresh reads the books again.

1. In VisiBooks, create an API key with read access (Settings → API keys) and grant it the entities to read.
2. Save it in VisiGrid’s keychain from a terminal: vgrid visibooks key, then paste the key. It is stored in your system keychain, never in the recipe or the workbook. vgrid visibooks entities lists the entities it can read.
3. Run New Recipe from VisiBooks…. The source column has Report, Entity, the date (As of, or From and To for the general ledger), and Basis (the entity’s default, accrual or cash).

Dates move with the calendar: last month end, year start and the like, so the same recipe gives last month’s numbers every month. Amounts are exact to the cent; debits and credits are separate columns, and the trial balance adds Balance (debit minus credit). Account numbers stay text.

The key is tied to the server it was saved for: a recipe naming another server finds no key, so a shared recipe can’t send your key anywhere else. The first time a recipe reads VisiBooks, VisiGrid shows the server, the entity and the report and asks, as it does for files. Nothing is read when a workbook opens; the Table shows its last result until you refresh it. VisiGrid only reads: it never changes your books.

Recipes from other people

A recipe can name any file on your computer. The first time a recipe reads its source, VisiGrid shows the exact file it would read, its size and date, and asks before loading anything. Press Ctrl+Enter to load or Esc to cancel. The answer is remembered for that recipe and that file (or pattern), so later refreshes don’t ask again. Recipes you build or point at a file yourself don’t ask.

Recipes read only local files: network paths are refused, and so are anything other than ordinary files, such as pipes and devices. Sources can be up to 256 MB.

Run it unattended

The same recipe runs from the command line with the same results, for scheduled jobs and CI:

vgrid recipe run orders.recipe.toml -o orders.csv

If a check fails, nothing is written and the exit status is 70. See vgrid recipe. A VisiBooks recipe uses the key saved with vgrid visibooks key; on a server or in CI, set VISIBOOKS_API_KEY instead.

An AI agent connected through vgrid mcp can do the same with the run_recipe tool, and refresh a linked Table in an open window with refresh_table (0.48.0 and later). Both work only with a recipe whose source you have already approved in VisiGrid, run_recipe only writes new files, and a refresh waits while you’re reviewing a plan.

The recipe file

A recipe is a small TOML file. VisiGrid writes it, but you can read and edit it too (Open as text in the builder):

version = 1

[source]
kind = "csv"
path = "export-*-*.csv"      # relative to the recipe file; * and ? match the newest file
header_row = 3               # the line holding the column names; 0 means no header
columns = ["Order ID", "Customer", "Amount", "Order Date", "Notes", "Rep"]

[[step]]
op = "select"
columns = ["Order ID", "Customer", "Amount", "Order Date"]

[[step]]
op = "rename"
columns = { "Order ID" = "order_id" }

[[step]]
op = "trim"

[[step]]
op = "types"
columns = { order_id = "text", Amount = "number", "Order Date" = "date:ymd" }
on_error = "fail"            # fail | keep_text | blank

[[step]]
op = "filter"
column = "Amount"
is = ">"                     # = != < <= > >= contains not_contains starts_with empty not_empty
value = "0"

[[step]]
op = "dedupe"

[[step]]
op = "group"
by = ["Customer"]
totals = [
  { fn = "sum", column = "Amount", as = "Total" },     # sum average min max count distinct first last
  { fn = "count_rows", as = "Orders" },
]

Unpivot keeps some columns and turns the rest into rows:

[[step]]
op = "unpivot"
keep = ["Region"]
names_to = "Month"      # default "Attribute"
values_to = "Sales"     # default "Value"
drop_empty = false      # default true: empty values are left out

Sort, Fill down, Replace values and Split column:

[[step]]
op = "fill_down"
columns = ["Region"]

[[step]]
op = "replace"
columns = ["Amount"]    # omit for every column
find = "n/a"            # empty: replace empty cells
with = "0"
part = false            # default false: the whole cell must match; true: text inside cells
match_case = false      # default false: ignore case

[[step]]
op = "split"
column = "Rep"
by = ", "
into = ["Last", "First"]

[[step]]
op = "sort"
by = [{ column = "Amount", descending = true }, { column = "Region" }]

A VisiBooks report:

[source]
kind = "visibooks"
entity = "42"                 # from `vgrid visibooks entities`
report = "general_ledger"     # trial_balance | general_ledger | ar_aging | ap_aging
from = "last_month_start"     # general ledger; or YYYY-MM-DD
to = "last_month_end"
basis = "cash"                # accrual | cash; omit for the entity's default

The trial balance and agings take as_of instead of from/to (default today). Date words: today, month_start, month_end, last_month_start, last_month_end, year_start, year_end, last_year_start, last_year_end. server defaults to https://api.visiapi.com.

Merge with another recipe (its path is relative to this one):

[[step]]
op = "merge"
with = "budget.recipe.toml"
on = ["Account"]
right_on = ["Acct"]      # when the other table names its key differently
how = "left"             # left | inner | full | anti
columns = ["Budget"]     # omit to bring every column but its keys
duplicates = "fail"      # fail | first

A JSON file:

[source]
kind = "json"
path = "invoices-*.json"
records = "data.invoices"   # omit to find the records by itself; 0, 1, ... index into a list

For a .jsonl or .ndjson file, records is not used. For an Excel workbook, use kind = "xlsx", a path, sheet = "Orders" (empty means the first sheet) and header_row (rows above it are skipped; 0 means no header). For a Parquet file, use kind = "parquet" and a path. For DuckDB, use kind = "duckdb", a path, and table = "main.orders" (or a table name that is unique in the database).

Add combine = true to a CSV, Excel, Parquet or JSON source whose path is a pattern to append every matching file.

CSV source settings: path, delimiter (",", ";", "tab", "|"; detected when omitted), encoding (utf-8, windows-1252, utf-16; detected when omitted), header_row (default 1), decimal_comma (read 1.234,56 as 1234.56), and columns (the column names when the recipe was saved).

Every step takes missing = "fail" | "skip" | "blank". Types are text, number, auto, date:ymd, date:dmy and date:mdy. Filter numbers are written with a point and no thousands separators (1234.5).

Saving a fix from the app changes only what the fix changes: comments, key order and spacing you wrote by hand are kept (0.48.0 and later).