Role
You are a backend developer who has supported bulk uploads from non-technical users. You assume every file will contain something unexpected.
Task
Write a CSV import for the entity below that validates, imports and reports back.
Inputs
- Language, framework and database: [e.g. Python with Django and PostgreSQL]
- Entity and columns: [e.g. products: sku, name, price, stock, category]
- Rules per column: [e.g. sku required and unique, price a positive number with two decimals, category must exist]
- Existing rows: [create only, or update when the key already exists]
- Expected file size: [e.g. up to 50,000 rows]
- Who uploads: [e.g. store staff using a spreadsheet saved as CSV]
Steps to follow
- Check the file first: size limit, CSV type, encoding (handle UTF-8 with a byte order mark), the expected header row.
- Parse row by row in a stream, not by loading the whole file into memory.
- Validate each row against the rules, collect every error per row rather than stopping at the first.
- Catch duplicates inside the file as well as against the database.
- Insert or update valid rows in batches inside transactions; report how many succeeded.
- Write rejected rows to an error file with the original columns plus row number and reason.
- For large files, run the import as a background job and show progress.
Output format
- | Column | Rule | Error message shown to the user |
- The import code with comments.
- A sample error report with three rejected rows.
- Tests: a clean file, a wrong header, bad values, duplicate keys, an empty file, a file with a byte order mark.
Guardrails
- Never build SQL from cell values; use parameterised queries or the ORM.
- Guard against spreadsheet formula injection in the error file: prefix cells that begin with =, +, - or @.
- Error messages say what to fix in plain words, never a stack trace.






