# todo.import-csv
Reads todos from CSV text, as todo.export-csv writes it or as a spreadsheet
saves it, and checks every row. It returns the todos in file order, each one
valid by todo.item's `validateTodo`, or throws on the first problem with the
row it is in.
## The file format
CSV as in [RFC 4180, Common Format and MIME Type for Comma-Separated Values
(CSV) Files](https://www.rfc-editor.org/rfc/rfc4180). The same format is
written by todo.export-csv and read by todo.import-csv.
- A header row, then one row per todo. todo.export-csv writes these 13 columns
in this order:
`id,title,notes,done,priority,due,tags,recurrenceFrequency,recurrenceInterval,recurrenceAnchor,createdAt,completedAt,order`
- `done` is `true` or `false`. `tags` are joined with single spaces. The
repeat takes three columns, all empty when the todo does not repeat.
`order` and `recurrenceInterval` are decimal integers. A null field
(`notes`, `due`, `completedAt`) is empty.
- A field is quoted with double quotes exactly when it contains a comma, a
double quote, CR or LF, and a double quote inside it is doubled. Nothing
else is quoted, and spaces are kept as they are.
- Every row, the last one included, ends with CRLF.
**Caveat for spreadsheets.** Nothing is escaped against formula injection: a
title such as `=1+1` or `=HYPERLINK(...)` is written as it is, because
prefixing it with `'` would change the title when the file is read back. If
your app offers the file for opening in Excel, LibreOffice or Google Sheets,
warn people, or write a separate spreadsheet export that escapes cells
starting with `=`, `+`, `-` or `@`.
## What it accepts beyond that
Files come back from spreadsheets and other tools, so reading is a little
wider than writing:
- CRLF or LF line endings (CR or LF inside a quoted field is kept as it is),
an optional UTF-8 byte order mark at the start, and an optional line break
after the last row. Empty lines are skipped.
- The 13 columns in any order, each exactly once and nothing else. Names are
matched exactly (`Title` is not `title`).
- Any field may be quoted, needed or not.
- Tags are split on spaces and normalised with todo.normalise-tags, so
`#Home Work,` imports as `home work`.
- `order` and `recurrenceInterval` accept leading zeros and a leading `-`
(so a negative order is reported by `validateTodo`'s own message).
Empty `notes`, `due` and `completedAt` fields become null.
## Errors
Rows are records, counted from 1 for the header, so the first todo is row 2.
A quoted field with line breaks is one row, and skipped empty lines are not
counted. Rows are read in order and the first problem is thrown:
- `the CSV has no header row` (empty text, or only empty lines)
- `the header must have the columns id, title, notes, done, priority, due, tags, recurrenceFrequency, recurrenceInterval, recurrenceAnchor, createdAt, completedAt, order: `
then `unknown column "colour"`, `column "title" appears twice` or
`missing column "order"`
- `row N: a quoted field is not closed`
- `row N: text after the closing quote of a field`
- `row N: a field with a double quote in it must be quoted`
- `row N: a CR outside quotes must be followed by LF` (old Mac line endings)
- `row N: expected 13 fields, found 12`
- `row N: done must be true or false, found "yes"` (case-sensitive)
- `row N: fill in all three recurrence columns or leave them all empty`
- `row N: recurrenceInterval must be a whole number, found "1.5"` and the same
for `order`: ASCII digits, an optional leading `-`, at most 15 digits (no
`+`, spaces, decimals or exponents)
- `row N: <field>: <message>`, the first of `validateTodo`'s messages in
field order, e.g. `row 2: title: Remove the spaces around the title.`
- `row N: duplicate id "t1"`
## Why it is shaped this way
All or nothing: a half-imported list is harder to fix than a file, so one bad
row refuses the whole file and says where to look. The row number counts
records rather than lines because that is what a spreadsheet shows, and a
quoted note with line breaks is one spreadsheet row.
Unknown columns are refused rather than ignored, so a misspelt header
(`compltedAt`) is caught instead of silently dropping data.
## New in 1.1.0: every problem at once
`validateTodoCsv(csv)` reads the same file and returns every problem instead
of throwing the first, so an import screen can list them all and the person
can fix the file in one go:
```json
{"valid": false, "rows": 4, "errors": [
{"row": 2, "field": "done", "message": "done must be true or false, found \"yes\""},
{"row": 3, "field": "title", "message": "Enter a title."},
{"row": 5, "field": "priority", "message": "Choose a priority: none, low, medium or high."},
{"row": 5, "field": "due", "message": "Enter the due date as a real date, YYYY-MM-DD."}
]}
```
- `row` counts records with the header as row 1, as importCsv does. `rows`
is how many todo rows were read (the header not counted).
- `field` is the todo field `validateTodo` names (`title`, `due`,
`completedAt`...) or the CSV column that could not be read (`done`,
`recurrenceInterval`, `order`; `recurrence` when only some of the three
repeat columns are filled; `id` for a duplicate). It is null when the row
as a whole is wrong (the wrong number of fields, a quoting problem) and for
header problems.
- `message` is importCsv's message without the `row N:` prefix, and without
the `field:` prefix for `validateTodo`'s messages.
- Within a row, problems come in the order importCsv checks them: the field
count, `done`, the repeat columns, `order`, then `validateTodo`'s messages
in field order, then a duplicate id. So the first problem in the list is
exactly what importCsv throws, and the file is valid exactly when importCsv
accepts it.
- **No knock-on messages.** A column that cannot be read is reported once:
an unreadable `done` does not also blame `completedAt`, and an unreadable
interval does not also give the "repeat every 1 to 999" message.
- A duplicate id is reported even when the row has other problems, and every
row's id counts, so fixing the first problem does not reveal a new one.
Rows without an id say so and are not duplicates of each other.
- **What stops the check:** a header that is not exactly the 13 columns (one
problem, row 1), no header at all, and a record that cannot be split into
fields (an unclosed quote, text after a closing quote, a bare quote or CR).
After that the rows cannot be found reliably, so the list ends there.
`importCsv` is unchanged: it now reads the file through the same code and
throws the first problem, word for word as 1.0.0 did, and every 1.0.0 vector
is kept. 1.0.0 was one file; 1.1.0 is a group of two (see
`spec/AUTHORING.md`, Groups), so `import { importCsv } from
"#fune/todo.import-csv@^1"` keeps working.