Design a CSV import people can trust

Describe the file contract

Publish an example with column names, accepted values, and the meaning of empty cells. Explain whether an empty value clears existing information or leaves it unchanged. That distinction matters when a file updates records rather than creating them.

Define how records are matched. Names and email addresses can change or be duplicated; a stable source identifier is often easier to reconcile. Tell the uploader what happens when the same file or record appears again.

Separate checking from applying

Offer a preview that reports how many rows would be created, updated, skipped, or rejected. Show understandable errors with row references and enough context to fix the source file.

Decide whether valid rows can be accepted when others fail. If partial import is allowed, produce a durable result that distinguishes those outcomes. If the entire file must pass, explain that before processing so the user does not expect partial progress.

Keep the result available

Give the import a reference and record who initiated it. Let the uploader return to the outcome after leaving the page, especially when the file takes time to process.

Plan how to correct an import that was structurally valid but contained the wrong information. A backup may help recover data, but the operational procedure also needs to account for any notifications or downstream actions already triggered.

Test with actual exported files after removing sensitive values. Include unusual characters, blank rows, and values that resemble numbers but are identifiers. The goal is a predictable transfer that people can explain and correct, rather than a parser that accepts only the demonstration file.

Separate parsing, validation, and applying changes.

A file can be valid CSV and still contain invalid business records. Quoting and delimiters belong to parsing; a missing customer reference belongs to validation; updating the correct account belongs to the apply stage. Keeping those stages distinct makes errors easier to explain and allows a preview before anything changes.

1. ParseRead the file using its agreed format.
2. ValidateCheck records, references, and duplicates.
3. PreviewExplain proposed changes and rejected rows.
4. ApplyCommit the accepted operation and report its result.

The Python CSV documentation describes the standard reader and the importance of opening CSV files with the appropriate newline handling. A minimal inspection step might look like this:

import csv

with open("customers.csv", newline="", encoding="utf-8-sig") as source:
    reader = csv.DictReader(source)
    required = {"customer_ref", "name"}
    if not required.issubset(reader.fieldnames or []):
        raise ValueError("Missing required columns")
    for record_number, row in enumerate(reader, start=1):
        # Validate and stage; do not write business records here.
        inspect_record(record_number, row)

This is illustrative code: inspect_record stands for application-specific validation. A record number is not necessarily a physical line number when quoted fields contain newlines. The example intentionally stages data rather than saving it while parsing, so a later error does not leave the user guessing which rows already changed the application.

Make the result reconcile with the input.

These outcomes must add up to the parsed records. If the user fixes rejected rows and uploads them again, explain how the service recognizes already applied records. For an update import, also specify whether a blank cell clears a value or leaves it unchanged; that small ambiguity can cause large unintended changes.

Bind the final apply action to the reviewed file and mapping. If the source data or target records change between preview and confirmation, detect the relevant conflict or repeat validation. Preserve a concise import receipt with counts, input identity, and failure details so the receiving team can investigate without relying on a screenshot of the final screen.