The spreadsheet is not the obstacle
The spreadsheet is probably the most reliable record your company has. It has grown columns for years, it encodes rules nobody wrote down, and one person understands all of it. None of that is embarrassing. It is what makes the file such good raw material.
Retyping is not the blocker. The blocker is that there is no agreed content model: no shared answer to what a product is, what belongs to it, and what groups it. Until that exists, every import is a guess, and guessing is what makes the project feel impossible.
If the one person who understands the file is leaving, this stops being optional. The model has to come out of their head and onto paper before they go.
Reading structure out of a flat file
A flat file looks shapeless, but it is full of structure. Open the export this afternoon and look for three things.
Repeated values are categories. If the same dozen words appear in one column across hundreds of rows, that column is a reference to something, not a free text field. It wants to be its own concept with a list of allowed values.
Empty columns are dead concepts. Somebody needed them once. If a column is blank in most rows, the idea behind it has already been retired in practice, whether or not anyone said so.
Inconsistent columns are unresolved decisions. A column that holds "Navy", "navy blue" and "NVY" is not messy data. It is a decision about colour naming that was never made, and the file has carried that indecision for years.
Messy data is a modelling input. It shows you exactly where the model is undefined. Write each finding down, because those findings become the rows of your worksheet.
Design the hierarchy first
Before anyone opens an import tool, decide the hierarchy on paper. The question is how many levels you genuinely need, and the honest answer is usually fewer than the spreadsheet implies.
On the subscription apparel platform we delivered, the catalogue was designed as three levels before the import ran: a top level that changes on a cycle, a middle level that groups items by the occasion they are worn for, and the individual item at the bottom.
Depth is expensive. Every extra level is another choice an editor must make, another place a product can be filed wrongly, and another set of pages that needs a design. Four levels sounds more thorough than three and is usually worse. Where you are tempted to add a level, ask whether it is really a level or just an attribute: a colour, a size, a material. Attributes are cheap. Levels are not.
What to leave behind
Here is permission, in writing: not everything in the file comes across. A clean subset beats a complete mess, every time.
The objection will be about history. Somebody will say that the discontinued items, the old pricing columns and the supplier notes from years back might be needed one day. They might. So export the whole file, archive it somewhere read only, and leave it there. Archiving is how you keep history. Importing is not.
The harder part is making the decision stick. Defend it internally by recording each exclusion once: this column was dropped, for this reason, on this date, and the archive lives here. That record is your deliberate exclusion list. It is the difference between a decision and an argument that restarts every quarter.
Make the call before the import runs. Afterwards, dropping a column is no longer a row on your worksheet. It is a change to a live system.
What a programmatic import looks like
Nobody retypes anything. On the apparel platform the flat CSV export became structured data and was imported programmatically: one record per item, with its level and attributes named explicitly instead of implied by column position, created in the content system by a script. An image pipeline prepared and stored the product images in the same migration.
The import runs against a copy, so you see the result before anything is touched. When the first run gets something wrong, you fix the rule and run it again, rather than correcting records by hand, one at a time.
For the person who has to sign this off: the import reads from an export, never the live file. The same input gives the same output every time. The exclusion record is a plain document anyone can audit. The script is handed over with the rest of the code, so nothing about your data depends on us.
Where the spreadsheet still belongs
The spreadsheet does not die. It changes job. It stops being where product truth lives and becomes where product truth is prepared: the intake sheet for new items, the working surface for the person who knows the products best, and the record of how the structured import was decided.
That person keeps their role too. They are no longer the only one who understands the data, because the model is written down. They are still the one who understands the products. That is a better job than guardian of a file, and it survives their holiday.
