How to validate a supplier CSV before it changes your business records

Decide what this import is allowed to change

A supplier spreadsheet becomes a business operation when its rows can create or overwrite products, prices, stock, or customer records. The first decision is therefore the effect of an import, not the appearance of its upload button. Write down the receiving record type, the business owner, and whether a row may create a new record, update an existing one, or only be proposed for review. A product catalogue import, for example, might accept descriptions and supplier references while leaving published prices unchanged until a buyer approves them. That boundary gives the team a way to judge every field and every exception instead of treating a syntactically valid file as permission to alter the shop.

Choose a stable matching key before coding the import. A product name is convenient to read but can be translated, corrected, or reused; a supplier SKU may be useful only within that supplier. If an existing record has no trustworthy key, stage the row for human matching rather than guessing from a near name. Document what an empty cell means for an update: leave the existing value, set it to empty, or mark the row invalid. Those meanings must not vary between fields by accident. Preserve the original file and the reviewed mapping as evidence of what was submitted, subject to the business retention policy and access controls.

Parse the file before trusting its columns

CSV is a format with quoting rules, not a text file that can safely be split at every comma or newline. A product description may contain a comma, a quote, or a line break inside a quoted field. Use a maintained CSV parser with the agreed encoding and delimiter, reject malformed input, and set size and row limits before expensive processing. PostgreSQL documents how CSV quoting works and notes that an unquoted empty value and a quoted empty string can represent different things in its COPY command. Your application need not copy PostgreSQL semantics, but it does need an explicit rule for blank, missing, and literal empty values so an import does not silently erase information.

Show the detected headers to the operator and require a deliberate mapping to the receiving fields. Treat duplicate headers, unknown columns, and missing required columns as explicit decisions. A saved mapping can speed up recurring imports, but it should be tied to the supplier and file version, then reviewed when the header set changes. Do not map by column position alone: a newly inserted column can shift every value while leaving the file parseable. Before applying changes, display a small sample with the interpreted destination fields and a count of records proposed for creation, update, rejection, and manual review. The preview is a decision aid, not proof that every row is sound.

Validate business meaning as well as syntax

Field rules should match the operation. Require the agreed identifier, define accepted date and number formats, reject impossible quantities, and enforce allowed categories or status values. Compare related fields too: an end date should follow a start date, and a sale price should not be interpreted in a different currency simply because the number looks plausible. OWASP distinguishes syntactic validation from semantic validation and advises checking the server-side representation actually used for processing. Browser hints may help an operator correct a file, but they cannot be the only guard. Authorization is a separate check: a perfectly valid file submitted by the wrong staff account must not modify the catalogue.

The file itself is untrusted input. Limit accepted file types to what the workflow needs, check the content rather than trusting its extension or declared MIME type, and avoid storing it under an operator-supplied filename in a public web directory. OWASP also recommends limits on upload size and separate authorization for the uploader. Once parsed, treat every cell as data: parameterize database writes and encode values for the screen where they are displayed. Reject a file cleanly if its encoding is broken or its structure cannot be parsed. A partial import should happen only under a documented policy with row-level reasons; otherwise staff cannot tell whether a successful-looking run left a half-updated catalogue.

Walk through a small supplier update

Imagine a shop receiving a file with supplier SKU, English name, Chinese name, unit cost, currency, and availability. The supplier has added a promotion note column since the previous month. The importer should notice the new header, preserve the existing mapping for known columns, and ask what to do with the note rather than shifting fields by position. A row with an existing SKU becomes a proposed update; a new SKU becomes a proposed creation; a blank SKU goes to rejection or review according to the agreed rule. If currency is blank, do not assume Hong Kong dollars unless the file contract explicitly supplies that default. This is a fictional rehearsal, not an observed customer outcome.

Now put three awkward rows in the rehearsal: a name containing a comma, a quoted multiline description, and a duplicate SKU with conflicting prices. A correct parser keeps the first two rows intact. The third requires a policy: reject both, keep one only after human confirmation, or apply an explicitly agreed supplier precedence rule. Let the operator compare a before-and-after preview for a few high-impact fields. Run the same file twice in a test environment and verify that the second run is classified as already applied or as the same proposed changes, not as a fresh wave of duplicate products. Record the file identity, mapping version, import status, and row decisions without exposing unnecessary personal data in logs.

Make approval, commit, and recovery visible

Separate parsing and preview from the final write. The final confirmation should identify which records will change and which will remain pending. Where the database and workflow permit it, commit an approved set atomically so a failure does not leave an unexplained mixture of old and new values. If the business deliberately accepts valid rows while holding invalid ones, state that policy on the preview and provide an exportable list of rejected rows with reasons. Avoid silently coercing invalid dates or prices into a different value. An error report should point to the source row and destination field, but redact sensitive values if it is shared outside the authorized team.

Plan recovery before the first real import. Keep a restorable snapshot or change journal suited to the system, define who can reverse an approved batch, and test reversal on a rehearsal batch. A simple “undo” label is misleading if later staff edits depend on imported values; in that case a reviewed corrective import may be safer than a blind rollback. Give the operator a final receipt with counts and the time of the run, and retain enough provenance to answer which supplier file produced a disputed value. Start with one low-risk supplier, compare selected records against the source, and expand only when parsing, review, repeat-run, and recovery behavior are understood.

One useful approval rule is to make the importer explain its planned changes in the language of the business. “Update 42 rows” is less useful than identifying the affected supplier, the fields changing, the number of price changes, and any records that could not be matched. The operator should be able to stop, download a correction report, repair the source file, and restart without losing track of the earlier attempt. If the source supplier sends a corrected version, identify which version supersedes which; do not simply append both. After the approved run, sample records from the beginning, middle, and end of the file, plus every unusual category flagged in preview. Compare stored values with the source and with the planned mapping. This review is especially valuable when data types happen to accept a wrong but plausible value, such as a cost placed in a price field. Only then should the team treat the mapping as ready for a wider supplier rollout.

Treat the import template as a contract that can change. Publish a small sample file with required headings, accepted encodings, field meanings, and a clear example of a blank update value. Tell suppliers which changes require a new template version, such as adding a mandatory field or changing a SKU rule. When an operator receives an unfamiliar version, the safest workflow is to preview and approve a new mapping, then save that mapping with its version rather than silently fitting the file to last month’s layout. This also helps support staff distinguish a supplier format problem from a system bug. A well-defined template does not remove the need for validation, because a supplier can still send an out-of-range price, a duplicate key, or a malformed quoted field. It makes the discussion about what the row means much faster and more precise.

For the first live run, name one person who approves the preview and another who can answer supplier questions during the change window. Record any manual override with its reason and the affected row. If staff repeatedly override the same rule, revise the documented import contract instead of normalizing an undocumented exception.