# Writing and editing Text Tables

Produce files that CSV tools can still read and keep every declaration true for the rows it describes.

## Pick the shape

| Situation | Shape |
| --- | --- |
| You create the data (an export, a snippet for a person or a model) | inline: first line, table block, rows in one `.txtab` |
| Several related tables | one `.txtab` with a `table` per table and `tables=N` on line 1 |
| The CSV/TSV already exists and must not change (vendor dump, someone else's file) | a `.txtab` next to it with `table … reference=file.csv` and no rows. Never write into the referenced file. |
| Shipping data and its description together, or a wide table | `.txtab.zip` (see [archives.md](archives.md)) |

## A minimal correct file

```
# texttables=0.1 delim=comma quote=double
# table name=orders rows=2
# meta-grain one row per order
# meta-source shop_db.orders
# column name=id type=long role=id
# column name=amount type=decimal unit=USD role=measure agg=sum
# agg-sum ,750.50
id,amount
1,500.00
2,250.50
```

## Rules

- **Generate rows with code, never type them out.** Write rows with a CSV writer (Python `csv.writer(f, lineterminator="\n")`), and compute `rows=` and `agg-*` from the same list in the same script.
- **Line 1 is `# texttables=0.1`** plus the file's dialect. Line 1 is ASCII only — put non-ASCII text in `meta-*` lines.
- **Choose the prefix.** Use `#`. If any data record would start with `# ` (hashtags, issue numbers), pick another prefix such as `;;` and use it on every system line.
- **Every table starts with `P table … rows=N`.** `rows=` counts data records (not the header row) and must be exact — readers find the next table by counting. With more than one table, write `tables=N` on line 1.
- **Spell out the dialect.** If a value contains the delimiter or a quote, use `quote=double` and quote that value, doubling inner `"`. One record per line: replace embedded newlines with spaces.
- **Names are identifiers**: `[A-Za-z_][A-Za-z0-9_]*`, no hyphens, no spaces, no Cyrillic. When the header cell says something else (`Сумма (руб.)`), keep it as the header and add `title="Сумма (руб.)"` to the column.
- **Write a `column` for every column that matters**, with `type=`, plus `unit=`, `role=`, `agg=` where they apply. Write `meta-grain` whenever the row unit isn't obvious; `meta-as-of` for snapshots.
- **Money**: `unit=<ISO 4217>`; per-row currency → `unit=@cur` and leave money aggregates empty.
- **Relations**: `references=<table>.<column>` on the referring column; name the target table.
- **Compute `agg-*` from the rows you write**, one value per physical column, nulls excluded; leave a position empty when it doesn't apply. Never estimate.
- **SQL**: `sql-pre-data <dialect> CREATE TABLE …`, then keys and indexes as `sql-post-data <dialect> …`, one statement per line. Generic SQL is `ansi`. A foreign key goes in its own table's block.
- **Notes**: `note text="…"` with `key=` (when the table has a `role=id` column) rather than `row=`. A note about the whole file goes before the first `table` without an address. Keep notes few.
- **Comments**: `# -- text`. Hyphens, never underscores, in keywords and keys (`len-max`, `agg-count-distinct`).
- **Recommended order in a table block**: `table`, `meta-*`, `column`, `chart`, `note`/`link`, `sql-*`, `agg-*`.

## Editing an existing file

- **Rows added or removed, sliced or filtered** → set `rows=` to the new count and recompute or drop every `agg-*`. Never keep the old totals with new rows. Renumber `row=` in `note`/`link`; drop lines whose row is gone; `key=` lines need no change.
- **Column renamed** → header cell, `column name=`, every `formula=`, `chart` channel, `note`/`link` `col=`, `{old}` in `link=` templates, `references=` pointing at it. Fix SQL by hand.
- **Column added or removed** → header row, every record, every `agg-*` line, `columns=` if declared.
- **Table added or removed** → update `tables=` on line 1.
- **Unknown lines and attributes** → keep them unchanged.
- **Referenced files** → never edit them; materializing a column on such a table writes a new inline file.
