Automation and Repeat
VisiData's automation tools let you record any interactive workflow and replay it on new data without retyping commands. The CommandLog captures every action. Macros bind sequences to single keys. Batch mode runs workflows headlessly in scripts.
Learning Focus
The accountant's workflow: do it once interactively → save CommandLog as .vdj → next month, run vd --play on the new file. This replaces Excel macros (VBA) with plain, readable JSON logs.
CommandLog — Recording Workflows
Every command you type is recorded automatically. View and save the log:
vd ~/github/practice-folder/visidata/04-cleaning/01-dirty_orders.csv
# Perform data cleaning:
# 1. Cast amount to float: %
# 2. Filter nulls: z| → amount is None → gd
# 3. Normalize status: gs → g* → active<Tab>ACTIVE
# 4. Create audit column: = 'PASS' if amount > 0 else 'FAIL'
# Save the CommandLog:
Shift+D
Ctrl+S
# Enter: ~/github/practice-folder/visidata/04-cleaning/clean_orders.vdj
Batch Replay with --play
# Replay recorded workflow on a new file — no UI opens
vd --play ~/github/practice-folder/visidata/04-cleaning/clean_orders.vdj \
--batch ~/github/practice-folder/visidata/04-cleaning/01-dirty_orders.csv \
-o /tmp/orders_cleaned.csv
Flags:
--play <file.vdj>— path to the CommandLog to replay--batch— headless mode, no interactive UI-o <output>— where to save the result (format from extension)--replay-wait <seconds>— pause between commands (for demos)
CommandLog Structure (.vdj Format)
The .vdj file is newline-delimited JSON — human-readable and editable:
{"sheet": "01-dirty_orders", "col": "amount", "keystrokes": "%", "input": ""}
{"sheet": "01-dirty_orders", "col": "", "keystrokes": "gs", "input": ""}
{"sheet": "01-dirty_orders", "col": "status", "keystrokes": "g*", "input": "active\tACTIVE"}
{"sheet": "01-dirty_orders", "col": "", "keystrokes": "Ctrl+S", "input": "cleaned.csv"}
You can edit the .vdj directly to:
- Change a column name reference
- Modify an expression value
- Add or remove steps
Macros — Bind Sequences to a Key
m <key> start recording macro (press same key to stop)
gm open Macro Index (view/delete macros)
Record a Monthly Report Macro
vd ~/github/practice-folder/visidata/04-cleaning/01-dirty_orders.csv
# Record macro bound to 'r' key
m r
# Perform steps:
# Cast amount: %
# Open Describe Sheet: Shift+I
# Return: q
# Frequency by region: move to 'region' → Shift+F
# Return: q
m r # stop recording
# Now pressing 'r' on any orders sheet repeats these steps
View All Macros
gm
# Shows: key | commands | source file
# Press d to delete a macro
# Press q to close
.visidatarc Custom Functions
Define reusable Python functions that are available in all expressions:
# ~/.visidatarc
from datetime import date, timedelta
# Business days calculation
def biz_days(start, end):
days, curr = 0, start
while curr < end:
if curr.weekday() < 5:
days += 1
curr += timedelta(days=1)
return days
# Tax calculation
def gst_inclusive(amount, rate=0.09):
return round(amount * (1 + rate), 2)
# Period from date
def period(d):
return f"{d.year}-{d.month:02d}"
Use in VisiData expressions:
=
# Enter: gst_inclusive(amount)
=
# Enter: period(order_date)
=
# Enter: biz_days(order_date, delivery_date)
Reusable Formula Templates (.visidatarc)
Store commonly used expression strings as Python variables for quick access:
# ~/.visidatarc
AGING_BUCKET = """
'current' if days_overdue <= 0 else
'1-30 days' if days_overdue <= 30 else
'31-60 days' if days_overdue <= 60 else
'90+ days'
"""
ORDER_TIER = """
'enterprise' if amount > 10000 else
'mid-market' if amount > 1000 else
'small'
"""
While these strings cannot be directly pasted into VisiData's expression prompt, they serve as copy-paste templates when building complex derived columns.
Full Monthly Report Pipeline
# Monthly close process — run this every month
# Step 1: Record the workflow once
vd ~/github/practice-folder/visidata/04-cleaning/01-dirty_orders.csv
# ... perform all analysis steps ...
Shift+D
Ctrl+S
# Enter: ~/github/practice-folder/visidata/04-cleaning/monthly_close.vdj
# Step 2: Next month, just run:
vd --play ~/github/practice-folder/visidata/04-cleaning/monthly_close.vdj \
--batch ~/github/practice-folder/visidata/04-cleaning/01-dirty_orders.csv \
-o /tmp/monthly_report_$(date +%Y%m).csv
Sheet-to-Sheet Replay
Replay different parts of a workflow on different sheets:
# CommandLog with multiple sheets:
vd ~/github/practice-folder/visidata/03-join/01-servers.csv \
~/github/practice-folder/visidata/03-join/02-roles.csv
# Work interactively on both sheets
Shift+D
Ctrl+S
# → saves commands for all sheets
# Replay:
vd --play ~/github/practice-folder/visidata/04-cleaning/clean_orders.vdj \
--batch ~/github/practice-folder/visidata/03-join/01-servers.csv \
~/github/practice-folder/visidata/03-join/02-roles.csv \
-o /tmp/output.csv
Quick Automation Patterns
| Goal | Approach |
|---|---|
| Monthly clean + export | Save CommandLog → --play --batch |
| Audit every new file | Macro bound to key → runs validation |
| Standard pivot report | CommandLog with pivot steps → replay |
| Normalize all CSVs | --play in a shell loop |
Shell Loop — Process All CSV Files
# Apply the same cleanup workflow to all CSVs in a folder
for f in ~/github/practice-folder/visidata/06-append/*.csv; do
basename="${f%.csv}"
vd --play ~/github/practice-folder/visidata/04-cleaning/clean_orders.vdj \
--batch "$f" \
-o "${basename}_clean.csv"
done
Hands-On Practice
vd ~/github/practice-folder/visidata/04-cleaning/01-dirty_orders.csv
# 1. Perform 3 operations:
# - Cast amount to float: %
# - Select all: gs
# - Normalize a value: g* → active<Tab>ACTIVE
# 2. View CommandLog: Shift+D
# Verify your 3 steps appear as rows
# 3. Save workflow: Ctrl+S → ~/github/practice-folder/visidata/04-cleaning/practice.vdj
# 4. Press q → return to source sheet
# 5. Record a macro:
m p # start recording, bound to 'p'
Shift+I # open Describe Sheet
q # return
m p # stop recording
# 6. Test macro: press p → Describe Sheet opens and returns
# 7. Batch replay (in a shell):
# vd --play ~/github/practice-folder/visidata/04-cleaning/practice.vdj \
# --batch ~/github/practice-folder/visidata/04-cleaning/01-dirty_orders.csv \
# -o /tmp/practice_output.csv
Quick Reference Card
TEXT MATH & LOGIC LOOKUP
─────────────────────── ──────────────────────── ──────────────────────────
LEFT(str,3) = str[:3] col_a + col_b = a+b VLOOKUP → join (&)
RIGHT(str,3) = str[-3:] SUM(col) + sum INDEX/MATCH → join (&)
MID(str,2,3) = str[1:4] AVERAGE(col) + mean UNIQUE → Shift+F
LEN(str) = len(str) COUNT(col) + count SORT → [ or ]
TRIM(str) = s.strip() MAX/MIN + max/min FILTER → | then "
UPPER(str) = s.upper() IF(c,x,y) = x if c else y SEQUENCE → i
LOWER(str) = s.lower() ROUND(n,2)= round(n,2) TRANSPOSE → T
SUBSTITUTE = s.replace ABS(n) = abs(n)
TEXTJOIN = ', '.join MOD(n,12) = n % 12 DATES
FIND = s.find() SUMIF/COUNTIF = z| then + ──────────────────────
SPLIT = s.split() = z| + count TODAY = date.today()
YEAR = d.year
MONTH = d.month
DAY = d.day
WEEKDAY = d.weekday()
DAYS(diff) = (d1-d2).days