CSV Import/Export Spec
Every internal tool converges on the same feature request: "can I just upload a spreadsheet?" And every naive implementation of it converges on the same incident: someone uploads 2,000 rows, row 847 has a date typed as "next tuesday", and now either the whole import died with a stack trace nobody can read, or, worse, 846 rows imported and nobody knows which ones. The person doing the upload is not a developer. They have a spreadsheet and a deadline, and the tool's job is to tell them exactly what's wrong in language that names their rows, not your exceptions.
The design that survives contact with real spreadsheets has three properties. Validate everything before writing anything: an import is all-checked-then-applied, never check-as-you-go. Default to dry run: the first pass produces a report ("1,994 rows OK, 6 problems, nothing imported yet"), and applying is a separate, deliberate step. And report errors per row, per column, in a file they can open in Excel, fix, and re-upload, because that is the actual workflow: upload, fix six cells, upload again. That loop happening twice is normal. That loop being pleasant is the feature.
CSV itself will fight you more than the database will. Excel exports smuggle in a byte-order mark, Windows line endings, commas inside quoted fields, dates in whatever format the machine's region settings felt like, and at least one column renamed by a helpful human. The parsing rules below exist because each one is a support ticket I am saving you from filing. Hand the spec to your agent whole; the boring parts are the load-bearing parts.
# CSV import/export spec: [tool name] — [entity, e.g. contacts]
Build import and export for [entity]. Import is validate-everything-first,
dry-run by default, with a per-row error report. Export round-trips: an
exported file re-imports cleanly with zero changes.
## Column contract (the whole feature hangs on this table)
| CSV header | Required | Type / rules | Maps to |
|---|---|---|---|
| email | yes | valid email, unique within file AND against DB | users.email |
| full_name | yes | 1..[120] chars | users.display_name |
| joined_on | no | date, accept [YYYY-MM-DD and MM/DD/YYYY], store ISO | users.joined_on |
| tags | no | semicolon-separated from allowed set: [a; b; c] | users.tags |
| [..] | [..] | [..] | [..] |
- Headers matched case-insensitively, whitespace trimmed.
- Unknown extra columns: warn and ignore (spreadsheets accumulate barnacles).
- MISSING required column: fail the whole file immediately with a message
naming the missing header. Do not guess which column they meant.
## Parsing rules (non-negotiable, all learned the hard way)
- Encoding UTF-8; strip a leading BOM (Excel adds one; it corrupts the
first header and "email" mysteriously fails to match).
- Accept \n and \r\n. Handle quoted fields with embedded commas/newlines
with a real CSV library. Never str.split(','). Never.
- Trim whitespace on every cell. Treat "" and whitespace-only as empty.
- Row numbers in all reports = spreadsheet row numbers (header = row 1,
first data row = 2), so the user's eyes and the report agree.
## Import flow
1. Upload → parse → validate EVERY row. Collect all errors; do not stop
at the first. The user fixes one pass, not twenty round trips.
2. DRY RUN (default): report only. "[1,994] rows valid: [1,200] new,
[794] updates. [6] rows have problems. Nothing has been imported."
3. Error report, downloadable CSV, one line per problem:
```
row,column,value,problem
847,joined_on,"next tuesday","not a date; use YYYY-MM-DD"
912,email,"","required"
1044,email,"amy@example.com","duplicate of row 210"
```
4. APPLY (explicit second step, only enabled when the user has seen the
report): upsert keyed on [email]. Existing row → update; new → insert.
Upsert makes re-importing the same file safe (idempotent) instead of
a duplicate factory.
5. Apply runs in ONE transaction: all valid rows or none.
[Alternative, decide now: import valid rows and report the failures.
Default to all-or-nothing; partial imports are how "which rows made
it in?" becomes a support ticket with no answer.]
6. Result screen: created [N], updated [N], link to the error report if
any. Log who imported what file and when.
## Export flow
- Same columns, same headers, same order as the contract table.
- Dates as YYYY-MM-DD. UTF-8. Proper quoting (library, again).
- Respects the user's current filters, and says so in the filename:
[entity]-[filter]-[YYYY-MM-DD].csv
- Excludes soft-deleted rows unless explicitly requested.
- THE ROUND-TRIP RULE: export, then import the same file back = zero
errors, zero changes. This is the invariant that keeps the two halves
honest, and it makes export double as backup and as bulk-edit.
## Limits and safety
- Max file size [10 MB] / [50,000] rows; reject over with a clear message.
- Importing is permission-gated: [which roles]. An importer can overwrite
every record it touches; treat the permission accordingly.
- If any column is personal data, the export respects the same access
rules as the screens (§6.5). Export is the easiest data-leak in any
internal tool: one click, whole table, someone's Downloads folder.
## Acceptance tests
- [ ] Excel-exported file (BOM, \r\n) imports cleanly
- [ ] Quoted field containing a comma and a newline survives round-trip
- [ ] File with 6 bad rows: dry run reports all 6 with correct row numbers
- [ ] Apply-then-apply-again changes nothing (upsert idempotency)
- [ ] Missing required column fails fast, names the column
- [ ] Export → re-import → zero diffsAdaptation notes:
- Multiple entities: one contract table per entity, but build the machinery (parse, validate, dry-run, report) once and generically. The second import feature should cost a table definition, not a second codebase.
- Very large files: stream instead of loading into memory, batch the upserts, and make apply a background job with progress. The user-facing contract (dry run, per-row report, all-or-nothing) does not change, only the plumbing under it.
- If users need to map their own column names to yours, add a mapping step between upload and validate. Skip it until someone asks; it doubles the UI for a need most tools never have.
- The mistake: validating on the way in, so the import dies at the first bad row with 846 already written. All-check-then-apply exists to make that state unrepresentable. If your agent implements the loop version anyway, this is the line of the spec to point at.
- Semicolons for multi-value cells are deliberate: commas inside cells are legal CSV but a human editing in Excel will get the quoting wrong. Boring separators reduce support tickets.