# CSV import ## What this builds A way for an app's customers to bring their own data in from a spreadsheet. The person uploads a CSV file, says which column is which, sees what would happen before anything is saved, and gets a report of every row that has a problem. Read `spekkr.inputs.json` first. The pages call the thing being imported a `record_noun`. A file has at most `max_rows` rows. `update_existing` changes R5, and `undo` decides whether R7 exists. The spec imports into one example table, `contacts`, described as data in `design.md`. To import something else, change that description and nothing else. This spec needs the app to know who is signed in. It does not build sign-in. ## Out of scope - Excel files. The upload page says how to save a sheet as CSV. - Imports that run on a schedule or from an address. - Records that span several rows. ## Requirements Each requirement has an id. Tests name the id they cover. ### R1. Read the file - A file is at most 20 MB (20,000,000 bytes) and `max_rows` rows, not counting the header. A larger one is refused before it is read to the end. - The file is UTF-8, with or without a byte order mark, or UTF-16 with one. Any other encoding is refused with a message that says how to save as UTF-8. - The separator is whichever of comma, semicolon and tab splits the first 10 lines into the same number of columns, more than one. If none does, the person is asked. - Fields follow the usual rules: a field in double quotes can hold separators, line breaks and doubled quotes. The cases in `design.md` come out exactly as given. - The first row is the header. Empty rows are skipped. A row with more or fewer fields than the header is an error for that row, not for the file. Accept when: every case in the table in `design.md` parses as given, and a file of `max_rows` plus one rows is refused. ### R2. Map the columns - The person sees the header and the first 5 rows, and for each field of the record a list to choose a column from. - Each field starts on the column whose header matches its name or one of its other names, ignoring case, spaces, hyphens and underscores. - A required field must be mapped before the person can go on. A column can be left out. - The choice is remembered for that person, and offered again for a file with the same header. Accept when: a header of `E-mail Address` is matched to `email` unasked, and going on without a required field is refused. ### R3. Dry run - Every row is checked against the rules of the fields it maps to: required, the form of an email address, a number, a date, a longest length. - Dates are read as `YYYY-MM-DD`, or in the one other form the person picks from a list on the mapping page. A date that fits neither is an error. - The result says how many rows would be created, updated, skipped and refused, and shows the first 20 problems with row number, column and reason. - The person can download every refused row as a CSV file: the original columns plus one, `Error`. - Nothing is saved to the record table during a dry run. Accept when: the counts add up to the number of rows, the downloaded report holds exactly the refused rows, and the record table is unchanged. ### R4. Import - After the dry run the person imports the valid rows, or cancels. - The import runs in the background, 500 rows to a database transaction, and the page shows how far it has got. - An import that is interrupted carries on from the last finished batch when it is started again. No row is saved twice. - A person can cancel a running import. Batches already saved stay saved, and the result says so. - One person runs one import at a time. Accept when: an import killed after any batch and started again ends with the same records as one that ran straight through. ### R5. Rows that match - A record is recognised by its key field. For contacts that is `email`, compared without regard to case. - Two rows in the file with the same key: the first counts, and each later one is refused with "same email as row N". - A row whose key matches an existing record: if `update_existing` is on, the record's mapped fields are updated and fields that are not mapped are left alone. An empty cell does not erase a value. If it is off, the row is skipped and counted. Accept when: a file with a repeated key creates one record and reports the other row, and an update with an empty cell keeps the old value. ### R6. Safe reports - In any file the app writes, a cell that starts with `=`, `+`, `-`, `@`, a tab or a carriage return gets a single quote in front, so a spreadsheet does not run it as a formula. Accept when: a refused row whose name is `=HYPERLINK("http://x","y")` appears in the report with a quote in front. ### R7. Undo Only if `undo` is on. - For 24 hours an import can be undone. Records it created are deleted. Records it updated get their old values back. - A record that someone changed after the import is left as it is and listed in the result of the undo. Accept when: undoing leaves the table as it was before the import, apart from records changed since, which are listed. ### R8. History - A page lists a person's imports: file name, when, counts, state, and links to the error report and, if `undo` is on, to undo. - Uploaded files and reports are deleted after 30 days. The history line stays. Accept when: one person never sees another person's imports or files. ### R9. Pages - Four steps: upload, map, review, result. The person can go back from review to mapping without uploading again. - Progress is announced to screen readers, not only drawn. Accept when: each page works with a keyboard alone and at a 360 pixel wide screen. ## Definition of done - Every acceptance check above has an automated test, and all tests pass. - A README explains how to describe another record in place of contacts, and how to run the tests.