Getting a catalog out of spreadsheets, and starting a record of what changes

Somebody on your team asks which file is the current one.

There's one with a date in the name. One that ends in _v2_final. One somebody downloaded on Tuesday to send a supplier a price list.

You know the answer, and that's exactly the problem, because nobody else does. The trouble was never how many files there are. It's that the only reliable copy of the truth is in your head.

This is one of the jobs we built our PIM to handle. Some of what follows is true of any tool you might pick, and the rest is specific to ours; this page says which is which as it goes.

The problem with spreadsheet catalogs

Ask anyone who's been meaning to move for a year, and the reason almost never turns out to be the import.

It's that the spreadsheet, for all its faults, is currently the only thing that remembers anything. There's a comment somebody left on a cell back in March. A row highlighted yellow, which means something precise to the two people who agreed what yellow means. Version history that can tell you who touched the file last Thursday, and it has settled an argument at least once.

So moving looks like trading a bad memory for no memory at all. That fear is reasonable, and it's also pointed at the wrong thing.

What can go wrong during an import

Two import behaviours cause most of the damage, and neither one is obvious until it has already cost you something.

Every tool has to make a decision about the first. On the second, tools differ a lot.

A blank cell means two opposite things

Leave a cell empty and you've said nothing at all. You've also said something quite specific. Which of the two it is depends entirely on what the file is for.

When the file is a patch, an empty cell means "I'm not telling you anything about this field, leave it alone." You exported forty columns, edited two, and you'd rather the other thirty-eight survived the trip.

When the file is a full snapshot, that same empty cell means the opposite: this value is empty now. Keeping yesterday's number would leave your catalog saying something the source no longer says, and that's how you end up advertising stock you don't have.

Same file. Same blank cell. Two correct answers that contradict each other.

A one-off bulk edit is usually a patch and a supplier feed is usually a snapshot, but frequency isn't what settles it. It depends on one thing: is this file a correction, or a full picture? A file you built by hand can perfectly well be a full picture.

Now apply that to what you're about to do. Your first real update will map a handful of columns, because that's the whole point of a bulk edit: fix the titles, set a supplier reference, correct the category on eight hundred rows at once.

Every field you chose not to map should be outside that update entirely. Which raises the next question, and it's the one worth asking out loud: does the system leave those columns alone, or does it recognise them on its own?

The file you exported isn't neutral when it goes back in

The obvious workflow is export, edit, import. Nothing changed except the two cells you touched.

Except the file is carrying column headers that the same system recognises, and some import workflows treat a recognised header as an instruction in its own right rather than asking you to map every field.

Where that happens, a round trip that looks like a two-cell edit can quietly change a setting on every row, because every row was carrying a column nobody thought to look at.

Settings tend to surface this before values do. They're easier to miss in a spreadsheet, and inventory tracking is the example most people meet first.

How our PIM handles partial updates

Before the answer, one distinction worth making, because "losing history" is really two different worries wearing the same coat.

The data. A partial update that quietly clears whatever it didn't mention. That's the one that costs money, and it depends entirely on how your tool reads the file.

The record. Who changed this, when, and what it said before.

The second one feels like a loss and is usually a gain. Not because spreadsheets have no history: modern ones can often tell you that a cell changed, when, and what it used to say. It's that their history is about the file. It describes edits to a document, rather than keeping one record per product that covers hand edits, imports, supplier feeds and connected channels in the same place.

The first worry is solved by a tool that refuses to guess.

In ours, the columns you mapped are the entire contract. A column you didn't map isn't read at all, whatever its heading says, and clearing a value is a separate mode you choose on purpose rather than something a blank cell does to you by accident.

So a partial update can add and it can change. What it can't do is quietly delete.

That's one sentence, and it's the sentence you should be able to get out of any vendor on your list.

What happens to your change history

Every change to a product, brand, category, tag or specification is saved as a version, and an import is saved the same way a hand edit is.

So the record starts the moment your catalog arrives. The first entry in it is the arrival itself.

Each version records who caused the change and how it reached the catalog: a person editing directly, a person running an import, a named supplier feed, a named connected channel, or the system acting on its own.

That distinction is where the value lives. An import that ran overnight isn't the same event as somebody editing a title at their desk, and a record that can't tell them apart will eventually tell you something untrue.

Edits made in a short burst collapse into one version, named after whoever wrote last, with a count of how many changes it covered. That's what keeps a busy catalog readable, and it's a real trade: the record answers "who last touched this", not "who touched it at 14:32".

Products, brands, categories, tags and specifications are all versioned this way.

What you can and cannot restore

You open a product, look at its history, put two versions side by side and see which fields differ. If the older one was right, you restore it. Seeing the history needs view access and restoring needs edit access, which are separate permissions on purpose.

Restoring puts you back where you were, except where the world has moved on in the meantime. A name somebody else has taken since. A barcode now in use by another product. A category hierarchy that no longer has the same shape.

A restore doesn't stop for any of that. It applies everything it can and hands you a list of what it skipped, which is exactly the behaviour you want at the end of a bad afternoon.

How long it lasts. History is kept for thirty days per item, capped at two hundred versions each. Whatever else expires, the most recent version of every live item always stays. That last one moves forward every time you edit, so it's a picture of where the product is now rather than somewhere to go back to; it exists so an item is never left with no stored version at all.

So be precise about what this protects. It protects what you do from now on, inside a thirty-day window.

Change something today, discover next week that it was wrong, and the previous state is sitting there waiting. If the version you want is from four months ago, it's gone. Not thinned out, gone: nothing is kept in its place, so the record can't return you there and can't tell you the change ever happened.

Which is an argument for looking inside the window rather than after it, and a migration is exactly the kind of work where that matters.

Three things it isn't, said plainly:

It isn't a compliance audit trail. It answers "what did this product look like and who changed it" for the people who run the catalog. If you need immutable retention for an auditor, ask us before assuming this is that.

There's no "undo this whole import" button. Restore works one item at a time. If an import that updated two thousand products goes badly, the record tells you what changed and lets you put any of them back, but it won't reverse the job in one click. And if the import created products you didn't want, restore is the wrong tool entirely: a product that was just born has no earlier state, so those have to be removed separately.

A deletion is recorded, and it isn't a way back. The history tells you something was deleted and who did it, and that entry isn't a restore point. It's also the one place with no permanent floor: once an item is deleted, its whole history ages out at thirty days.

How to test the migration safely

Start by picking the master file. Not the newest and not the most complete, but the one your team actually works in day to day, because that's the one whose habits your catalog has picked up.

Then import a small but deliberately varied sample out of it. A few dozen products that are genuinely different from each other. One with variants. One with a long description full of whatever formatting your old file has accumulated. One with attributes that only apply to a couple of categories.

Open them afterwards and read them the way a customer would.

What you're hunting for is the small disagreements. A measurement that lost its unit. A description that arrived carrying stray markup. A product with no brand, because that brand didn't exist yet and you'd told the import to carry on rather than stop.

Resolve those before the real run, in the source file or as a repeatable transformation, so that production isn't the first place the cleanup happens.

The loading is usually the quick part. The time goes into deciding what your attributes actually mean, which is the question the spreadsheet has been letting you avoid.

What stays behind in the spreadsheet

The comments, the cell colours and the file's own version history don't normally come across in a catalog import. They describe the working file rather than the product model.

Most advice tells you to run both systems in parallel for a week or two. Running the new one alongside the old one, so you can check it against something you trust, is sensible and worth the time. What we'd avoid is letting both of them stay editable.

Two editable catalogs is the exact problem you're leaving, reintroduced with your blessing. Somebody will edit the wrong one and nothing will tell you.

Cut over one product family at a time. Once a family has moved, stop editing those rows in the spreadsheet and let the new system be its only working home.

Keep the file, though. Not as a working copy and not as somewhere people still edit, but archived where you can open it. It settles the one question the new system can't answer, which is what the old one used to say.

Common questions

Two questions are worth asking every vendor on your list, ours included. What happens to a column I didn't map? And what can you tell me about this product four weeks from now?

Those two are what our PIM is built around for this job, which is why the specifics above could be written down rather than promised.

It's worth naming what the far side looks like, because it answers the question this page opened with. Spreadsheets go back to what they're good at, which is moving data in and out. The catalog gets one working home. And nobody has to be the person who knows which file is right, because the answer has stopped living in somebody's head.