- A supplier catalogue opens perfectly in a spreadsheet.
- The headers look familiar. The rows appear complete.
- A generic CSV parser can still return rows.
- For recurring Medusa catalogue feeds, we built a preprocessing boundary that identifies the actual file shape before business mapping begins.
The client problem
A supplier catalogue opens perfectly in a spreadsheet.
The headers look familiar. The rows appear complete. Yet the file uses tabs instead of commas, starts with a two-line report title, contains a byte-order mark before the first header and includes product descriptions with delimiters that were never quoted.
A generic CSV parser can still return rows. That is what makes the failure dangerous. It may not crash; it may shift the description, price and every field after them into the wrong columns.
For recurring Medusa catalogue feeds, we built a preprocessing boundary that identifies the actual file shape before business mapping begins. It handles configured spreadsheets, report preambles, comma or tab headers, quoted multiline cells and one particularly common form of ragged supplier row. Only then does the canonical import pipeline validate and stage the data.
The goal is not to accept every broken file. It is to prevent format ambiguity from becoming commercial data.
The client problem: “CSV” describes a family, not a contract
Teams often use CSV to mean any table-like attachment.
In practice, suppliers send comma-separated files, tab-separated text, Excel workbooks and exports whose first rows contain a title, creation date or legal note. Headers vary in casing and punctuation. Some files begin with an invisible UTF-8 BOM. Descriptions may contain commas, tabs, quotation marks or line breaks.
Those differences are harmless to a person opening Excel. They are consequential to automation.
If the delimiter is wrong, the whole header may become one field. If leading report rows are treated as headers, no mapping matches. If a quote inside a product dimension is interpreted as the start of a quoted record, several physical lines can be swallowed. If a free-text comma shifts the trailing price, a syntactically successful import can write the wrong value.
The platform therefore needs an explicit file contract before it needs a product mapper.
Make the input format a supplier setting
The ingestion configuration records whether a feed is CSV-like or an Excel workbook and how many leading rows should be skipped.
This is more reliable than guessing every property from the filename at runtime. A .txt attachment may be a tab-separated catalogue. An .xlsx file needs workbook parsing, not text decoding. A supplier report may always place two presentation rows before its true header.
The setting is validated: input format is constrained to CSV or Excel, and the skip count must be an integer inside a bounded range. The attachment extension is checked against the configured format so an Excel binary is not accidentally passed into a text parser.
This creates a reusable source contract. Once a supplier's export is understood, recurring emails can follow the same preprocessing decision without asking an operator to repair the file each time.
Automation becomes dependable when source-specific knowledge is stored as configuration rather than rediscovered as a heuristic on every run.
Convert spreadsheets into a controlled intermediate file
Excel is a presentation and calculation format, not the format the catalogue workflow should interpret directly.
The preprocessor reads the first worksheet as rows, preserves empty cells with explicit defaults and keeps blank rows long enough for the configured preamble removal to remain accurate. After skipping the selected leading rows, it writes the remaining table as comma-separated UTF-8 text.
The converted file is stored privately through the file service with metadata that connects the original attachment to the prepared file. The import receives the prepared file ID and a CSV content type.
That boundary simplifies everything downstream. Header analysis, mapping, staging and row-level errors operate on one intermediate representation whether the supplier began with CSV or Excel.
It also preserves provenance. The source attachment still exists, while the generated import artefact records that it was rewritten and converted.
The system does not pretend an Excel workbook was always a CSV; it makes the transformation explicit.
Remove report preambles without breaking quoted rows
Skipping the first two “lines” sounds trivial until a quoted cell contains a newline.
The preprocessing scanner walks the CSV text while tracking whether it is inside a quoted value. It counts a row boundary only when a newline occurs outside quotes and handles escaped double quotes. This prevents a multiline description from being mistaken for another report row.
The same logic finds the first complete header record for preview and delimiter detection.
After the configured rows are skipped, the preprocessor strips a leading BOM from the new beginning of the file. If no data remains, it fails with a clear message instead of launching an empty import.
This is an example of a small technical decision protecting a business rule. “Skip two report rows” must mean two records, not two arbitrary newline characters.
Detect the delimiter from the header, then carry it forward
The header gives the cleanest view of a delimited file's structure.
The preprocessing layer reads the first complete record, removes the BOM and sniffs whether commas or tabs separate its fields. Quoted headers are parsed correctly, so a header such as SKU, Vendor remains one name rather than two.
The chosen delimiter is then passed into the Medusa product workflow. This hand-off matters because the streaming CSV library defaults to commas and does not automatically discover tab-separated input.
Detection is not useful if each downstream step guesses again. The file analysis, saved mapping and staging parser must agree on the same separator.
For the recurring feeds in this project, the supported contract is intentionally narrow: comma or tab. That covers the evidenced supplier formats without advertising a universal dialect detector that could make unsafe assumptions about semicolons, pipes or arbitrary encodings.
Preserve properly quoted complexity
A valid delimited record can contain the delimiter itself or span several physical lines.
The record reader therefore distinguishes a newline inside quotes from the newline that ends a record. Field splitting likewise keeps a quoted tab or comma inside its value. Escaped quotes are interpreted without ending the field prematurely.
This lets a product description retain paragraphs or embedded separators while the trailing SKU and price stay aligned.
The header names remain verbatim for the source mapping, with surrounding whitespace trimmed. Each yielded row is an object keyed by those headers, giving the later preset or field map the exact source vocabulary it expects.
Blank physical lines do not become products. A final record without a trailing newline is still processed.
These behaviours are not special commerce logic, but commerce depends on them. Before a price can be validated, the parser has to prove which token is the price.
Repair one known ragged-row pattern deterministically
Some supplier files are not valid CSV.
A free-text description contains commas without quotes. The clean fields before it—such as manufacturer and part number—remain stable. The clean fields after it—such as price—also remain stable. A naive split produces more tokens than the header count and shifts the trailing columns.
For update feeds where analysis or a saved mapping identifies that free-text column, the project can reconstruct the row deterministically.
It keeps the columns before the description anchored from the left. It keeps the expected trailing columns anchored from the right. Any overflow tokens are joined back together inside the one known text field using the original delimiter.
If the row already has the expected number of fields, it passes through unchanged. If no text column has been identified, the system does not guess where to collapse data.
This is a bounded repair for a known supplier defect, not a promise to heal arbitrary malformed files.
Why anchoring from both sides protects the price
Consider a four-column feed:
Manufacturer, Part Number, Description, Price
The row contains two unquoted commas inside Description. Splitting yields six tokens. The reconstruction knows that Manufacturer and Part Number occupy the first two positions and Price occupies the final position. It joins the three middle tokens back into Description.
The important property is not that the text looks nicer. The price remains the final business field.
This approach works only when the variable text column and the clean trailing columns are known. If two free-text columns can both contain unescaped delimiters, the ambiguity is real and the feed should be rejected or corrected at source.
Good ingestion engineering does not turn uncertainty into confidence. It repairs what the contract makes deterministic and stops where interpretation would become guesswork.
Validate headers before staging products
Once preprocessing has produced a reliable record stream, the catalogue workflow checks the columns required by the selected route.
A full catalogue preset may require all mapped source columns. A narrow updates feed may require only the supplier SKU plus whatever fields its saved update map controls. Header matching is case-insensitive, but missing required fields fail the import clearly.
The mapped row is then canonicalised. For review-oriented update feeds, the original source columns are preserved alongside canonical fields so an operator can compare what the supplier actually sent.
Rows with neither a title nor a SKU are ignored as empty. Other row-level problems, such as an invalid mapped price, are attached to the staged row rather than hidden by a parser default.
The parser's job ends at a trustworthy table. Product identity, field authority and catalogue reconciliation remain later decisions.
Stream ordinary files and bound the exceptional path
The normal catalogue staging path reads the file as a stream and buffers a fixed number of rows before bulk insertion. It does not require the complete catalogue in workflow memory.
The reconstructed-row path also processes records incrementally, keeping a bounded text buffer and using the same staged-row batches.
Spreadsheet conversion is different: the workbook is read into memory before being rewritten. That is why format selection matters and why it would be misleading to claim every acquisition path has identical memory characteristics.
The public lesson is broader. A system can preserve a scalable streaming core while isolating the formats that inherently require a different preprocessing strategy.
One implementation does not need to pretend every file type is the same. It needs clear boundaries and operationally appropriate limits.
Fail the import visibly when structure is unsafe
Malformed input should not leave an upload spinning forever.
The staging step wraps parsing and database work in a failure boundary. Missing required columns, unreadable files and parser errors update the durable upload session to a failed phase with a readable message. The product-import record is also marked failed where possible.
That gives the operator an actionable outcome instead of a silent partial import.
It is particularly important for background workflows. The HTTP request that started an import may already be complete when a later streaming error occurs. Failure state must live in the import record, not only in a rejected promise or server log.
An ingestion pipeline earns trust when it can explain both what it accepted and why it stopped.
A practical supplier-file checklist
Before mapping a supplier file into Medusa, ask:
- Is the source text-delimited or a real workbook?
- Does the extension match the configured format?
- Which worksheet is authoritative?
- How many presentation rows precede the header?
- Are blank preamble rows counted correctly?
- Is a UTF-8 BOM removed before header matching?
- Is the separator comma or tab?
- Are quoted headers parsed as complete fields?
- Can quoted values contain delimiters and newlines?
- Does the supplier emit unquoted delimiters in a known text column?
- Can the clean columns on both sides prove a deterministic repair?
- Are required headers validated for this specific import route?
- Are original source fields preserved for review where needed?
- Are blank rows ignored without hiding malformed business rows?
- Does a background parsing failure become a durable visible status?
If the parser cannot answer where the price went, the product mapper must never run.
The broader lesson
Medusa gives us a powerful product and workflow foundation. Supplier exports arrive with their own history, conventions and defects.
We placed a file-normalisation boundary between those worlds. Configured format and skipped rows handle recurring report shapes. Workbook conversion creates one controlled intermediate. BOM removal and delimiter detection establish the header contract. Quote-aware record parsing preserves valid complexity. A bounded reconstruction repairs one deterministic ragged-row pattern without pretending every broken file is safe.
Only after that boundary does the catalogue pipeline validate mappings and stage product data.
The value is easy to describe in merchant language: a comma inside a description must never become tomorrow's product price.