- A spreadsheet used by operations is a business interface, not a throwaway file.
- An empty cell must have one documented meaning for every field.
- Preview changes and warnings before writing to the catalogue.
- Test that export followed by import preserves business meaning.
- Give operators a report and a recovery path after every import.
The client problem behind the CSV screens
The client was not asking for a generic upload button. Its teams needed to exchange brands, suppliers, collections, categories, tags, translations and products, each with different identifiers, relationships and deletion risks. Medusa provides extensible workflows and product data, but it cannot decide what an empty translation cell or a category missing from a spreadsheet means for this organisation.
We implemented separate import and export contracts for those catalogue domains, with field-specific validation and row-level reporting. We also added a round-trip check that exports and re-imports each domain to compare active-row counts and reported errors. Recovery remains an explicit operator action rather than a hidden promise attached to every upload.
Why a familiar spreadsheet can be dangerous
Merchandising teams often manage thousands of products through CSV files because spreadsheets are fast, familiar and easy to exchange. That convenience hides a serious risk: a CSV row carries instructions for a live catalogue. A missing value may mean ‘leave the current value alone’, ‘remove the value’ or ‘the source system does not know’. If the importer guesses, a routine update can erase descriptions, prices, images or classifications across hundreds of products.
The first design decision is therefore not how to parse commas. It is to define the contract between the spreadsheet and the shop. Which columns are required? Which values identify an existing product? What does an empty cell mean for each field? Can a row create, update, archive or delete? Who is allowed to perform each action? Once a file changes production data, those questions deserve the same care as a customer-facing form or an API.
A simple example of data loss
Imagine exporting 10,000 products, changing only the category column and importing the file again. The description column is empty because the team did not intend to edit descriptions. If the importer interprets empty as ‘replace with nothing’, every description disappears. Row counts still look perfect: 10,000 rows went out and 10,000 came back. A test that checks only counts would declare success while the catalogue has been damaged.
This is why we compare meaning, not merely volume. Before export, we create a normalised picture of the important product data. After importing the exported file into a controlled environment, we create the same picture again. Product identity, variants, prices, options, metadata and relationships should match according to documented rules. Differences must be expected and explained, such as a generated timestamp—not silently ignored.
Show the consequences before changing anything
A mature import process should begin with a preview. It validates headers, formats, identifiers, currencies and references, then reports what would happen: products created, products updated, values cleared, rows skipped and errors found. Destructive actions deserve their own visible count. ‘Import 2,450 rows’ is not enough information if 600 images will be removed or 80 products archived.
Preview and import should share the same interpretation rules. Fingerprinting the approved plan against the file prevents a modified spreadsheet from borrowing an earlier approval. For large files, downloadable errors with row numbers, stable codes and plain explanations let the team correct the source instead of hunting through application logs.
Define empty, missing and explicit removal
A column can be absent from the file, present with an empty cell or present with a special removal value. Those situations should not accidentally mean the same thing. A practical default is: an omitted column leaves the field unchanged; an empty cell follows a field-specific rule; and an explicit removal marker clears a value only where removal is allowed. Prices and identifiers may reject emptiness entirely, while an optional subtitle may allow removal.
The rule belongs in documentation and in the preview. An operator should see ‘subtitle will be cleared’ rather than infer it from a blank cell. Relationships need extra care. An empty category list might mean remove all categories, but an unknown category should normally stop the row rather than delete existing links. The safest behaviour is specific to the business field, so a universal ‘empty means null’ switch is usually too crude.
What a round-trip test actually proves
A round-trip test exports known catalogue data, imports that file and compares the result with the starting point. It answers a powerful question: can the system read what it writes without changing the commercial meaning? We include difficult examples—multiple variants, regional prices, accented text, commas and line breaks, missing optional values and relationships to categories or sales channels.
The comparison ignores harmless presentation differences such as column order, but it does not ignore business differences. A price in euros must remain a euro price. A variant must remain attached to the correct product. A zero must not become ‘missing’. The test also checks side effects: did the import trigger the expected catalogue events, avoid duplicate records and leave search or ERP synchronisation in a consistent state? A green parser test alone cannot answer those questions.
Design for correction, not perfection
Even with good validation, imports can fail halfway because of infrastructure, permissions or an unexpected record. The system needs a clear policy: either apply the whole approved change as one controlled operation, or record progress so a retry does not repeat successful mutations. Every run receives an identifier and a report showing the file, rule version, operator, start and end time, successes, skips and failures.
For high-risk changes, keep an export of the affected records before mutation and define how to restore them. Recovery should not depend on manually editing the database. It may be safer to correct and re-import a file than to offer a giant ‘undo’ button that cannot reverse downstream effects. The important point is that the team knows the recovery procedure before uploading the file, not after discovering a damaged catalogue.
A checklist for the team approving an import
Before approval, ask: Is this the expected template version? Are identifiers unique? Which rows create, update, clear or archive data? Are currencies and amounts valid? Are referenced categories, variants and channels known? Does the preview show every destructive action? Has the file been tested on a representative copy of the catalogue?
After execution, compare the report with the preview, sample changed products in the storefront and verify downstream systems that consume catalogue events. Keep the run report long enough for support and engineering to investigate later. The objective is not to make CSV sophisticated for its own sake. It is to let a business team make large changes quickly without turning one ambiguous cell into a production incident.
Common questions
No. The same number of rows can contain different prices, relationships or empty values. Compare a normalised snapshot of the business fields before and after the round trip.
Only when the contract for that specific field says so and the preview makes the removal explicit. Omitted, empty and explicit removal should be distinguishable.
A durable report containing the file and rule version, preview totals, actual totals, errors by row, warnings, operator identity and a documented correction or recovery path.