CSV import validation checklist: prevent silent data loss

A CSV upload can look successful while leaving the wrong data behind. When commissioning an import feature, the useful question is not just “Can this application import a spreadsheet?” It is “Can we see what will change, stop bad changes, and explain or recover from the result?”

Start with three requirements: a preview of the interpreted data, explicit rules for accepting or rejecting records, and a recovery plan with clear limits. A green success banner is not enough evidence.

This CSV import validation checklist is a planning framework, not a tested importer or a claim about a delivered client system. The examples are fictional and unrun. Use them to agree what a developer should demonstrate before you accept the feature.

Define the CSV format and business rules separately

CSV describes a text format, not your customer database. RFC 4180 documents a common comma-separated format with an optional header, consistent field counts, quoted fields for embedded commas and line breaks, and doubled double quotes inside quoted fields. It is an Informational RFC, not a complete application validation standard.[1]

A correctly quoted record can still contain an unknown product, an invalid date or a duplicate customer. A real export may also use a different delimiter or line-ending convention from the one you expected; RFC 4180 acknowledges differing implementations.[1]

Write down the supported input contract:

• Encoding, delimiter, header presence and quote rules.
• File-size and record limits, plus handling of blank records.
• Required fields, accepted value formats and permitted conversions.
• The response to unsupported or ambiguous input.

If several formats are supported, show the detected choice for confirmation. Reject unsupported input with an explanation instead of quietly guessing its meaning.

Use a CSV-aware parser rather than treating every physical line as a record: a quoted field may contain a line break.[1] Error reports should identify the affected data record even when it spans several file lines.

Preview column mapping and changes before confirmation

A useful preview shows more than the first few cells. Ask for:

• Each source column's destination, including ignored and unmapped columns.
• Raw values beside interpreted values where a conversion matters.
• Proposed creations, updates, unchanged records, warnings and rejected records, with clear counting rules.
• The duplicate and overwrite rules that will apply.
• Confirmation that preview has not changed live business records.

Decide whether an empty cell means “clear the existing value,” “leave it unchanged” or “reject this record.” Treat a missing column separately from an empty cell. Agree whether whitespace is meaningful: RFC 4180 treats spaces as part of a field, so trimming is an application decision, not a harmless format correction.[1]

Bind confirmation to the uploaded file and mapping the user reviewed. Also specify what happens if someone edits existing data between preview and confirmation. Require revalidation of affected records and a visible conflict result rather than an unnoticed overwrite.

If the wider workflow is still unclear, first map the trigger, people, information and exceptions for an internal tool. Those decisions help define who may confirm an import and what it is allowed to change.

Preserve identifiers, dates and text encoding

A customer code such as 00127 is not necessarily the number 127. Require identifier text to be preserved unless the specification explicitly says otherwise. Show original and proposed values in preview; do not silently remove zeros or normalize punctuation.

This protects only data present in the upload. If another tool already changed 00127 to 127 before export, the importer cannot reliably infer the missing zeros. Require a warning or corrected source rather than an invented repair.

For a date such as 03/04/2026, specify the accepted pattern or reject the ambiguity. Do not let a machine's locale choose a business date invisibly. PostgreSQL's COPY reference illustrates why configuration matters: DateStyle affects input/output, and ENCODING or client encoding determines how file text is interpreted.[3] These are PostgreSQL details, not evidence that every application behaves the same way.

Include accented names and non-Latin text in acceptance material. Ask the developer to demonstrate preservation of the declared encoding and a clear error for undecodable input instead of silent replacement.

Specify duplicate, overwrite and retry rules

“Skip duplicates” is incomplete. Define the matching key and check three boundaries:

• Within the file: two records use the same external customer ID.
• Against existing data: an uploaded ID already belongs to a stored record.
• Across retries: the same import is submitted again after an interrupted response.

Names alone are a risky proposed key because different people or products can share a name. If the business needs a composite key, specify its components and normalization rules.

For each match, state whether to update selected fields, leave the stored record unchanged or report a conflict. Identify fields that import must never overwrite. For duplicates within the file, explicitly define whether both are rejected or another documented rule applies; do not silently pick a winner.

After a timeout, the user needs a way to find out whether the original batch committed before trying again. Require a batch-status lookup and a documented retry policy. The related guide to safe retries for business applications covers the wider duplicate-action problem; this import brief still needs its own matching and reconciliation rules.

Choose whole-batch or partial acceptance deliberately

An atomic import accepts the defined batch as a unit or leaves that batch's database changes unapplied. A partial import accepts valid records while reporting rejected ones. Neither is automatically the right business choice.

A price-list replacement may need atomic behavior to stay consistent. Independent contact records may permit partial acceptance if rejects are visible and easy to correct. These are planning examples, not universal recommendations.

PostgreSQL describes a transaction as an all-or-nothing group of database steps, with ROLLBACK canceling updates before commit.[4] Its COPY reference separately documents default failure behavior and ON_ERROR handling for errors converting input values to column data types.[3] A database option does not replace the application's full business validation policy or catch every error category.

Ask where the atomic boundary sits: the whole upload, one chunk or one record. Do not describe a chunked importer as whole-file atomic if earlier chunks can remain committed. Also specify when emails, webhooks or other external effects occur; do not assume a database rollback reverses an external action.

Use a small input table to expose hidden decisions

Assume a fictional customer import with customer_id, renewal_date and note. Its proposed contract requires a unique text ID and a date in YYYY-MM-DD form. These are example business rules, not RFC requirements. The table describes field values, not a literal ready-to-upload CSV file.

Record A checks parsing and identifier preservation even if the duplicate policy prevents acceptance. Records B through D check business rules. Record E checks a later output boundary. Keeping these checks separate makes a failed demonstration easier to diagnose.

Review CSV error reports for formula injection

Rejected records often come back in a downloadable error report. That report can contain the same untrusted text as the upload.

OWASP describes CSV or formula injection when spreadsheet applications interpret untrusted CSV cells as formulas. Its guidance covers formula-leading characters, separators and quotes, warns about saving and reopening files, and states that no universal CSV sanitization strategy is safe for every spreadsheet application and downstream consumer.[2]

Do not accept “we put quotes around everything” as the whole security explanation. Ask the developer to document the export strategy and test it in the spreadsheet products, versions and locales your users actually use, including save-and-reopen behavior. Include success exports, error reports and audit downloads—not just the original upload.

Keep any raw evidence within an appropriately controlled application workflow; an unsafe export should not be the only record of what arrived. Agree who can download reports, which sensitive fields are omitted or masked, and how long uploads and reports are retained. This checklist is not a privacy or compliance guarantee.

Distinguish rollback from recovery after commit

A pre-commit rollback and a later “undo this import” button are different features. PostgreSQL's transaction tutorial describes ROLLBACK for an open transaction, not arbitrary reversal of committed business history.[4]

For post-commit recovery, agree what evidence must be retained: batch ID, actor, timestamps, mapping and rule version, affected record identifiers, and the before/after values required for the recovery scope. Limit access and retention rather than keeping every uploaded personal detail forever.

Then ask:

• What if someone edited a record after import?
• What if an imported record now has related orders?
• Can recovery remove newly created records and restore overwritten fields, or only one of those?
• Which notifications or other external effects cannot be reversed?

Require a conflict-aware recovery plan, not a promise that every import can be undone. Backups are a separate mechanism: ask for restoration scope and a demonstrated process. Restoring a whole database to reverse one batch could affect later work, so it is not interchangeable with batch-specific recovery.

Put these acceptance cases in the project brief

Use synthetic data in a non-production environment. These are proposed demonstrations, not tests performed for this article:

1. Preview the table above. Verify exact ID preservation, the ambiguous-date and blank-ID errors, and detection of the A/C duplicate pair. Demonstrate correct parsing of the quoted comma independently of record acceptance.
2. Add a quoted multiline note and an embedded double quote. Verify each remains in the intended field and errors still locate the right data record.
3. In atomic mode, place an invalid record after valid records. Verify no business-record changes in the defined batch are committed on rejection.
4. In partial mode, reconcile every input record to an accepted or rejected outcome. State whether unchanged records are included in accepted totals so they cannot be counted twice. Correct and retry rejects without duplicating earlier accepted records.
5. Interrupt the confirmation response after processing starts. Demonstrate batch-status lookup and safe retry behavior, including a retry after the first batch has committed.
6. Edit an existing record between preview and commit. Verify the agreed conflict rule protects the newer edit or makes the overwrite explicit.
7. Run export-security cases in the agreed spreadsheet versions and locales, including save-and-reopen. Record cell behavior without dangerous external-action formulas.
8. Commit a batch, edit one affected record, then attempt recovery. Demonstrate documented conflict handling and identify effects that cannot be reversed.

Record application/database versions, configuration, input files, expected results and actual results. Choose representative file sizes and performance thresholds in the project brief; there is no universal row limit or completion time asserted here.

Turn “CSV support” into a reviewable scope

A useful import brief says what the file means, what users see before confirmation, what may change, and what happens after failure, retry or recovery. Ask for evidence against those requirements rather than relying on a success message.

Planning a website or application that needs imports? Use this checklist to describe the records, matching keys and recovery boundaries you need, then visit CodeCanvasDigital to discuss the scope. The site's brief helper lets you request an email draft; it does not confirm app opening, sending or receipt.

Sources

[1] https://www.rfc-editor.org/rfc/rfc4180.txt

[2] https://owasp.org/www-community/attacks/CSV_Injection

[3] https://www.postgresql.org/docs/current/sql-copy.html

[4] https://www.postgresql.org/docs/current/tutorial-transactions.html