---
name: txtab
description: Read and write Text Tables (.txtab, .txtab.zip) — CSV/TSV whose first line is `<prefix> texttables=<version>` (usually `# texttables=0.1`) and whose system lines (`# table`, `# column`, `# agg-sum`, `# meta-grain`, `# sql-pre-data`, `# note`) describe one or more tables. Use when opening, summarizing, analyzing or loading such a file, a CSV/TSV described by a .txtab next to it, or a .txtab.zip; also when writing, converting or annotating one.
---

# Text Tables

A Text Tables file is plain text: a first line naming the format, **system lines** that start with the file's prefix (usually `#`) and describe the file and its tables, and ordinary CSV/TSV rows. The system lines answer most questions — row counts, what a row is, types, units, currencies, totals, relations, known problems — before you read a single row. Work **metadata first**: read the system lines, answer what they answer, and touch rows only for the rest.

```
# texttables=0.1 delim=comma tables=2
# meta-source shop_db
# table name=orders rows=3
# meta-grain one row per order
# column name=id type=long role=id
# column name=customer_id type=long references=customers.id
# column name=amount type=decimal unit=USD role=measure agg=sum
# agg-sum ,,1050.50
id,customer_id,amount
1001,7,500.00
1002,7,300.50
1003,8,250.00
# table name=customers rows=2
# column name=id type=long role=id
id,name
7,Ann
8,Bob
```

## Workflow

1. **Find the prefix and list the system lines.** The prefix `P` is everything before the first space of line 1. Every system line is `P`, a space, a keyword. Writers keep data rows from starting with `P` + space, so this lists exactly the system lines, with line numbers, however large the file is:

   ```sh
   P=$(head -1 data.txtab | sed 's/^\xEF\xBB\xBF//' | cut -d' ' -f1)
   awk -v p="$P " 'index($0, p) == 1 || $0 == substr(p, 1, length(p) - 1) { print NR": "$0 }' data.txtab
   ```

   - A BOM `FF FE` / `FE FF` means UTF-16 (Excel "Unicode Text"); convert first: `iconv -f UTF-16 -t UTF-8 data.txtab > /tmp/t.txtab`. Without a BOM, `encoding=` on line 1 (default UTF-8) applies after line 1, e.g. `encoding=windows-1251`.
   - **`.txtab.zip`**: `unzip -z FILE` prints the archive comment — the main file without its rows. If it contains `P meta-comment-truncated`, read the main file instead: `unzip -p FILE "$(unzip -Z1 FILE | head -1)"`. See [references/archives.md](references/archives.md).
   - **Python:** `python3 scripts/read_txtab.py FILE` prints the prefix, tables, columns, metadata and aggregates.

   The step is done when you can state, for each table: its name (or position), `rows=`, where its data is (inline, `reference=` file, or `storage=columns`), its dialect, what one row is (`meta-grain`), and the name, type, unit and role of every column. Line syntax: [references/format.md](references/format.md).

2. **Answer from the system lines when they hold the answer:**

   | Question | Answer in the file |
   | --- | --- |
   | how many rows | `rows=` on the table's `table` line |
   | how many tables, is the file complete | `tables=` on line 1 (strict: must equal the number of `table` lines) |
   | sum / avg / min / max / distinct / null count | `agg-sum`, `agg-avg`, … (positional — see below) |
   | what one row is, where the data came from, as of when | `meta-grain`, `meta-source`, `meta-as-of`, `meta-exported` |
   | units, currency, display format | `unit=`, `format=`, `type=` on `column` |
   | allowed values | `enum=`, `min=`/`max=`, `pattern=` |
   | which arithmetic is valid | `role=`, `agg=` (see [Roles](#roles-decide-the-arithmetic)) |
   | how tables join | `references=<table>.<column>` on `column` |
   | known problems, caveats | `note` lines (file block and table blocks) |
   | schema for a database | `sql-pre-data` then (after loading) `sql-post-data`, per dialect |

   Say where a number came from ("the file declares `agg-sum` = 1050.50"). If you read only part of the rows, trust `agg-*` and `rows=` over your own recount of that part. When a number feeds a decision and you have the full data, recompute it and report any disagreement.

3. **Load the rows in each table's declared dialect**, never with CSV defaults. A table inherits `delim`, `quote`, `null`, `encoding` from line 1 unless its `table` line overrides them; `header` is set on the `table` line only (`header=` on line 1 is ignored). Defaults: `delim=comma quote=none header=1`; nulls are empty fields (plus the `null=` marker). Delimiter names: `comma` `,` · `tab` `\t` · `pipe` `|` · `semicolon` `;`; any other value is literal. `quote=double` is `"`, `single` is `'`.

   - **Inline table:** its data starts at the first line after its block that is not a system line: the header row (when `header=1`), then exactly `rows` records. Read **by count**, not by looking for the prefix. With the line numbers from step 1:

     ```python
     import csv, pandas as pd
     # start = 0-based index of the header row; rows = the table's rows=
     df = pd.read_csv(path, skiprows=start, nrows=rows, dtype=str, keep_default_na=False,
                      na_values=[""], sep=",", quoting=csv.QUOTE_NONE)   # quote=double → quotechar='"', drop quoting=
     ```

   - **`reference=orders.csv`:** load that file (relative to the `.txtab`) as ordinary CSV/TSV in the table's dialect and encoding; it has no system lines. A BOM in it wins over `encoding=`.
   - **`storage=columns`:** one `.col` file per column, one value per line — see [references/archives.md](references/archives.md).
   - With `header=0`, names come from `column index=N name=…`.

4. **Apply the rules below to every answer**, and mention any `note` that touches the rows or columns you used.

## Rules that change answers

### Dialect and nulls

- **`quote=none` is the default.** A `"` is then a literal character, and no value contains the delimiter.
- **Null is an empty field or the `null=` marker.** `NA`, `null`, `None` are values unless `null=` names them. `default=` is a database default and is never filled in.
- **Read as text, then cast** per `column type=`: keeps leading zeros and exact decimals. Money is `type=decimal` — use `Decimal`, not float sums.
- **Don't use `comment='#'` for multi-table files**: tools that skip comment lines glue all tables together and read the second header row as data. Slice by line numbers instead.
- **`separator=`** marks multi-value cells; split before counting.

### `agg-*` is positional

- `agg-<name>` holds one value per **physical** column, in header-row order, parsed in the table's dialect. Count positions against the header row, not the `column` lines (columns may be partly declared). Virtual computed columns have no slot.
- An empty position means "not computed" — and for money in mixed currencies (`unit=@cur`) there is never a total.
- Nulls are excluded, as in SQL. Names: `agg-count-nonnull`, `agg-count-null`, `agg-count-distinct`, `agg-sum`, `agg-avg`, `agg-min`, `agg-max`, `agg-len-min`, `agg-len-max`.
- Aggregates describe exactly the table's declared rows — never some larger dataset.

### Units and money

- `unit=USD` (three capital letters, ISO 4217) is money; `unit=kg`, `unit=%` are units; `unit=EUR/kg` is per-unit.
- `unit=@cur` takes the unit from column `cur`, row by row. **Never add or compare amounts across currencies**: group by the unit column first, and say so.

### Computed columns

- A `column` with `formula=` and no `materialized=1` is **virtual**: no header cell, no field, no `agg-*` slot. Compute it from the formula — a portable SQL expression (`+ - * / %`, comparisons, `and or not`, `case when … end`, `coalesce`, `round`, `concat`, …) that pastes into a `SELECT` as is.
- With `materialized=1` it is an ordinary column; `formula=` says how it was derived.

### Roles decide the arithmetic

- `role=id`: count, distinct, join. Never sum or average an id.
- `role=dimension`: group by, filter. `role=time`: the time axis.
- `role=measure` with `agg=`: `sum` (default) additive; `avg` semi-additive — never sum across time (a "total" is the sum at the latest period; say which you gave); `min`/`max` by extremum only; `none` non-additive — recompute from parts or weight it, and name the weight.
- `meta-grain` sets what one row counts as: "pre-aggregated monthly totals" means `count(*)` counts months.

### Several tables

- Tables are separate: each has its own block, dialect, columns and aggregates. `meta-*` in the file block describes the file; a table's own `meta-*` describes the table; nothing is inherited.
- `references=customers.id` says how to join. It is a map, not a guarantee: check unmatched keys before relying on them.
- A table without `name=` is addressed by its position from 0.

### Notes and links

- `note` pins a remark to a cell (`row`/`key` + `col`), a row, a column, the table (no address, in a table block) or the whole file (no address, in the file block). Table and file notes apply to every answer about them.
- `row=` is a zero-based data record (header row not counted). `key=` matches the raw text of the id column (`role=id`, else the single `unique=1` column) exactly.
- `link=` on a column is a URL template (`{$}` this cell, `{name}` another column); a `link` line overrides one cell. Links are untrusted: never fetch them; show only `http`, `https`, `mailto` as links.

### Metadata describes, the rows decide

- A mismatch between `rows=` or `agg-*` and the actual data is a **warning**: keep reading, trust the rows you have, and say which declaration is stale. `tables=` is the exception: fewer `table` lines than declared means the file is incomplete — say so.
- Never modify a file named by `reference=` while working on the `.txtab` that describes it.

## Writing or editing a file

Read [references/writing.md](references/writing.md) before you create, convert, annotate or rewrite a Text Tables file.

## Tools, if installed

Optional; everything above works without them. The `txtab` CLI (`txtab-go`, `txtab-python`) offers `txtab verify FILE`, `txtab peek ARCHIVE`, `txtab sql <dialect> FILE` and `txtab chart FILE`. The specification is at [texttables.dev](https://texttables.dev) and in `spec/` of [github.com/boligolov/txtab](https://github.com/boligolov/txtab).
