实现健壮的 CSV 文件导入
设计流式解析、逐行校验、批量写入、重复导入防护和错误报告,确保大文件导入可追踪且不会产生重复数据。
完整提示词
<context>
Imports built in an afternoon break on real files: the whole file is read into memory, then the request times out; a file exported from a European spreadsheet uses semicolons and decimal commas; a UTF-8 byte-order mark ends up in the first header name; quoted fields contain newlines and the code splits on commas; spreadsheet software has turned long ids into scientific notation and dropped leading zeros; headers arrive as "E-mail" instead of "email"; the same file uploaded twice creates every record twice; a failure at row 40,000 leaves half the data in and no record of which half; the error report only says "invalid file"; and the downloadable error report, opened in a spreadsheet, runs a formula a user typed into a cell. A good import streams, validates every row, tells the user exactly which rows failed and why, and is safe to run again.
</context>
<task>
Implement an import for this target:
<target_model>
{{target_model}}
</target_model>
Stack: {{stack}}
Maximum data rows per file: {{max_rows}}
Error policy: {{error_policy}}
1. Inspect the code: the target models and their constraints, existing upload handling and file storage, the background job system, how progress is shown to users elsewhere, and any existing import code to reuse. If there is no natural key to upsert on and none can be inferred, or a rule in the target model is ambiguous, ask and stop.
2. Write the column contract: for each column, the canonical header and accepted aliases (compared case-insensitively, ignoring spaces, dashes and underscores), required or optional, type and format (dates, decimals and booleans, with the locales accepted), length limits, the lookups it needs, and the natural key used for upserts.
3. Intake: accept the file through the existing upload path, check size and type early, store it, create an import record (status, file hash, uploader, counts) and process it in a background job, never in the request. If the same file hash was already imported successfully, warn the user or reuse the earlier result instead of silently importing again.
4. Parse with a mature CSV library in streaming mode; never split lines on commas. Detect or accept the delimiter, strip a byte-order mark, handle quoted newlines, and decode as UTF-8 with a clear error (or a documented fallback) for other encodings. Read spreadsheet files only through a streaming reader for that format, and only if the target model or the existing code calls for them. Stop with a clear message once {{max_rows}} data rows are exceeded.
5. Validate every row and collect all of its errors, not just the first, each with row number, column, the offending value (truncated) and a human-readable message. Also detect duplicates of the natural key within the file, and check any file-level rules the target model states (totals that must balance, a required set of rows, a header-row date) after the last row, reporting them separately from row errors.
6. Write in batches inside transactions, upserting on the natural key so re-running the same file creates no duplicates. With skip-invalid-rows, write valid rows and record the invalid ones. With all-or-nothing, run a full validation pass first, then write everything in one transaction or through a staging table, and write nothing if any row fails.
7. Report progress: update rows processed, created, updated and failed on the import record at a sensible interval, and expose it through the app's existing pattern (polling endpoint, websocket or page).
8. Generate the error report as a CSV of the original rows plus an error column. Neutralise cells starting with `=`, `+`, `-`, `@`, tab or carriage return so they cannot run as formulas when opened in a spreadsheet. Serve it only to the user who ran the import or to admins.
9. Write tests with small fixture files: the happy path; a semicolon-delimited file with a byte-order mark; quoted newlines; invalid rows with several errors each; duplicate keys within the file; a file over the row limit; re-importing the same file (no duplicates); the error policy behaviour; and formula neutralisation in the error report. Add one generated large file to show memory stays flat, if the stack makes that practical. Run them and report the real result.
</task>
<constraints>
- Never load the whole file into memory, and never process the file inside the web request.
- Do not log full row contents if rows can contain personal data; log row numbers and counts.
- Keep the column contract in one place in code, so the parser, the validation and any downloadable template all use it.
- Use existing libraries in the project before adding new ones, and ask before adding a dependency.
{{> guardrails/scope-discipline}}
{{> guardrails/verify-before-done}}
</constraints>
<output_format>
## Column contract
Table: column, aliases, required, type and format, rules.
## Design
Intake, job, parsing, batching and upsert, progress and error report, in a few bullets.
## Changes
One line per file.
## Tests
One line per test and the real result of the run.
## Limits and follow-ups
Known limits (encodings, formats, size) and anything left for later.
</output_format>可替换内容
- {{target_model}}
- {{stack}}
- {{max_rows}}
- {{error_policy}}
- {{> guardrails/scope-discipline}}
- {{> guardrails/verify-before-done}}
分类与标签
编程开发、CSV 导入、数据校验、后台任务、批量处理、错误报告