# Spreadsheet import

How an owner's old spreadsheet (CSV) becomes ledger entries: the mapping and preview on the device, the leased import job, RF10 duplicates and an undo that deletes nothing.

## What it does [#what-it-does]

Profile → Import spreadsheet (US42, RF43) brings an owner's history in from a CSV. The app reads the file on the device, detects which column holds the date, amount, currency, category, vendor, odometer, note and car, and previews the first lines with what is wrong with each. Import uploads the file and queues an Import Run of kind `ledger`; the leased import job (`imports.jobs.ts`) writes the readable lines as expenses. Undo reverses the whole batch.

## One reader for the preview and the job [#one-reader-for-the-preview-and-the-job]

`@autofolio/domain/spreadsheet` is client-safe and holds every rule, so the preview is what gets imported:

- `parseCsv`: RFC 4180 with the header line's delimiter (`,`, `;` or tab), a BOM and CRLF;
- `detectColumns`: header words in Portuguese, English and German, whole words first;
- `detectDateOrder` and `parseDate`: ISO dates, or day-first and month-first by the first day above 12, else the account's locale;
- `parseAmount`: `1.234,56`, `1,234.56`, `R$ 89,90` as integer minor units (RN02); blanks, zero and negatives are refused;
- `parseCategory`: the categories by their words, anything else is `other`;
- `readRows`: each line as a draft or its problems (`date`, `amount`, `currency`, `vehicle`, `odometer`, `duplicate`).

A line names its car by nickname, make and model, or model; a line without one takes the car chosen in the app. Its currency must be the car's (a foreign amount needs a rate, RF23). A line repeating an earlier one, or an expense the ledger already has with the same amount, date and vendor, is a likely duplicate (RF10, `findLikelyDuplicate`). Every line with a problem is skipped and reported, never imported silently.

## Procedures [#procedures]

| Procedure             | What it does                                                                                                      |
| --------------------- | ----------------------------------------------------------------------------------------------------------------- |
| `imports.startLedger` | Records an Import Run of kind `ledger` for an uploaded CSV (`fileId`) with its mapping; idempotent by request key |
| `imports.known`       | The expenses of some cars between two dates: what the preview's duplicate check reads                             |
| `imports.list`        | Recent runs of one kind (`todos` by default) with their counts, skipped lines and `undone`                        |
| `imports.undo`        | Reverses a finished ledger run; only its importer or an owner or admin of the household                           |

## The job [#the-job]

The run's mapping is validated again by `importLedgerOptions`. The job writes as the run's creator (`tenantStep`), on the cars they may log on: with an ownership period, and with an `editor` or `owner` grant unless they manage the household ([family garage](/docs/features/family-garage)). It reads the cars' standing expenses in the file's date range, then in one transaction inserts a `ledger_entry` per line (source `import`, `import_run_id` the run, the period holding its date) and its `expense` row, appends the odometer readings that are not lower than the one before their date (RN07), and marks the run succeeded with `inserted_count`, `skipped_count` and the first 200 skipped lines. At most 2,000 lines and 1 MiB per file.

## Undo [#undo]

Undo appends, for each expense of the run that still stands, a `correction` entry to zero with the reason, source `import` and the run's id, and points the expense at it. An expense whose current entry is such a correction no longer stands: M-Gastos, M-Historico and duplicate checks leave it out, and raw ledger readers that follow corrections count it as zero. The originals stay in the ledger (ADR-01); undoing twice changes nothing.

## Key files [#key-files]

- [`packages/domain/src/spreadsheet.ts`](https://github.com/CarlosZiegler/autofolio/blob/main/packages/domain/src/spreadsheet.ts)
- [`packages/api/src/features/imports/imports.ledger.ts`](https://github.com/CarlosZiegler/autofolio/blob/main/packages/api/src/features/imports/imports.ledger.ts)
- [`packages/api/src/features/imports/imports.contract.ts`](https://github.com/CarlosZiegler/autofolio/blob/main/packages/api/src/features/imports/imports.contract.ts)
- [`apps/mobile/src/features/imports/imports.screen.tsx`](https://github.com/CarlosZiegler/autofolio/blob/main/apps/mobile/src/features/imports/imports.screen.tsx)