Make CSV imports safe for everyday operators

A spreadsheet is convenient because staff can prepare many changes offline. That convenience also makes a mistaken column mapping expensive to discover after import. Design the import around an operator who understands the business but does not know database commands. The screen should explain what changes, what fails, and what remains untouched.

Publish a contract for the file

Provide a downloadable template with named columns and plain examples. State accepted encoding, separators, identifier formats, and date interpretation. Define whether a blank cell means no change, an empty value, or removal. These meanings must not depend on the operator guessing how a spreadsheet saved the file.

Keep validation messages close to the affected row and column. Explain the expected value and whether the operator can repair the file or needs catalog maintenance. Preserve leading zeros in business identifiers by defining them as text throughout the import path.

Stage first, commit second

Use a staging area, meaning temporary records that do not yet affect live business data. Parse the file and show proposed creations, updates, unchanged records, and errors. Require confirmation against that exact preview rather than silently importing a newer file.

PostgreSQL COPY fails on processing errors by default, while configurable options can change error behavior.[2] Choose the intended product behavior explicitly. A strict all-or-nothing import suits interdependent records. Partial acceptance may suit independent rows, but operators need a precise result report and a safe way to retry rejected rows.

Hypothetical example: updating a location directory

A hypothetical workplace coordinator imports room capacities. One room code contains a leading zero, and another refers to a retired location. The preview preserves the first code and flags the second row. No capacity changes occur until the coordinator confirms the accepted scope.

For this directory, the proposed rule rejects the whole file when any location is unresolved. The coordinator fixes the reference and resubmits. This choice delays valid rows, but it keeps the directory update coherent. Another workflow could justify partial acceptance with different controls.

Design the second attempt now

A retry should have a defined relationship to the earlier attempt. Recommend an import identifier and stable row keys, plus a result that distinguishes already applied changes from new ones. If a timeout hides the final result, let staff retrieve that result instead of blindly submitting again.

Bulk permissions matter too. OWASP recommends checking authorization on every request.[4] Apply the relevant business permissions when the import commits, not only when the upload screen opens. A valid file must not become a shortcut around restrictions on ordinary edits. Check the import outcome before continuing.

Let a new operator rehearse the complete correction and resubmission path using a harmless test file before granting live import access.

Error downloads can repeat untrusted input. OWASP describes formula injection when spreadsheet software interprets that input as formulas.[9] Prefer a workbook with untrusted cells explicitly stored as text and formula inference disabled. If CSV is required, use a treatment tested with supported applications, formula-leading characters, delimiters, and quotes. Save and reopen files during testing. Quoting alone is not a universal defense.[9] Keep transformed display values separate from original correction data.

• Define blank-cell and duplicate-row behavior.
• Preview creates, updates, and rejected references separately.
• Choose atomic or partial acceptance deliberately.
• Provide error downloads with row identifiers and tested spreadsheet formula safeguards.
• Show import identity, final outcome, and retry behavior.