Do I need a PIM? An honest test for a catalogue that still lives in a spreadsheet

Do I need a PIM? Ask what the spreadsheet is failing at

A supplier’s file arrives with a product listed under the code 10-Dec. The supplier’s code is DEC10, a range prefix and a number. Somewhere between their system and your inbox a spreadsheet read it as a date, and nothing said so.

Do I need a PIM? You need one when the spreadsheet keeps failing at the jobs a product information management system exists to do: hold a fixed set of fields for each product type, refuse a value that does not fit, keep a history of each product, and send each channel its own version of the data. Size alone is not the reason, and neither is messy data. A PIM checks values against the rules it is given; it cannot tell you which value is right.

What a spreadsheet does well

Three things, and none of them is small.

Everyone can open it. Suppliers send spreadsheets and stores take them: Shopify’s instructions say “You can make changes to your CSV file in a spreadsheet program”, then import it.¹ Even Akeneo’s exports can produce CSV.² Whatever holds the catalogue, data usually travels in files a spreadsheet can open.

It changes in seconds. A price list is pasted in, a new attribute is a new column, and nobody waits for a developer.

It is big enough. An Excel worksheet holds “1,048,576 rows by 16,384 columns”.³ Google Sheets allows “Up to 10 million cells or 18,278 columns”, including files imported from Excel or CSV.⁴ A catalogue of 20,000 products with 60 columns is 1.2 million cells, well inside both. If a spreadsheet is failing you, it is almost never because it is full.

The limit that bites is the last row: Shopify’s advice for a product CSV that will not upload is to split it into smaller files.¹

Where a spreadsheet breaks

In five ways, and the first happens without anyone deciding it should.

It converts values without asking

Microsoft’s page on the subject opens with the rule: “Excel automatically converts data when you add data to a workbook, such as opening a .csv file or pasting data into a range of cells.”⁵ It lists four conversions: leading zeros removed, long numbers cut to 15 digits, digits around a letter E turned into scientific notation, and “a continuous string of letters and numbers” turned into a date.⁵ DEC10 is the last kind.

Excel for Microsoft 365 and Excel 2024 let you switch each conversion off, and a CSV brought in through Data, From Text/CSV can have its code columns set to text.⁵⁶ Neither repairs a file converted before it reached you: the damage is in the values, and formatting as text “will not change numbers that have already been entered”.⁶ And with the date conversion off, values such as JAN-1 “may still be treated as dates”.⁵ The identifiers post covers checking a damaged barcode.

It has no schema

A column means whatever the last person to type in it meant, so a weight column can hold 2.5, 2.5kg and 2,500 and nothing objects. On one of our jobs, a bathroom retailer’s file of 329 baths had twelve spellings in one column covering eight actual bath types: Single Ended and Single End Bath, Corner and Corner Bath, Freestanding and Freestanding Baths. Shoppers got twelve filter options for eight real things, and no cell was wrong by the spreadsheet’s standards, because it has none.

Validation is optional, and it does not travel

Both tools can validate. Excel’s validation can “restrict the type of data or the values that users enter into a cell, like a dropdown list”.⁷ Sheets rejects a value that “doesn’t match an item on the list”, unless the rule was set only to warn.⁸

But a rule covers only the cells someone set it on, and it belongs to the workbook.⁷ A CSV is text with commas between the fields, so no rule travels in one, and Excel warns on saving that the sheet “may contain features that are not supported by text file formats”.⁹

History is kept by file, not by product

Excel keeps versions of a workbook stored in OneDrive or SharePoint, under File, Info, Version History.¹⁰ In Sheets, anyone who can edit a file can browse earlier versions and see who changed one cell, though some changes, such as added or deleted rows, “might not show up”.¹¹ That is history by file and by cell. A catalogue owner’s question is by product: every change to this SKU since June, and who made it.

Several editors, several copies

Co-authoring works for a file in the right place: Excel needs the workbook on OneDrive or a SharePoint Online library, and when two people change the same thing, the last change saved generally wins.¹⁰ The supplier’s file arrives as an attachment instead, and every forward is a fork with its own edits.

What a PIM adds, and what it costs to set up

Akeneo’s documentation defines the category: a PIM “is aimed to centralize all the marketing data, to enrich, translate and prepare it for exports to multiple channels”.² In practice a PIM adds four things a spreadsheet does weakly or not at all.

A schema. In Akeneo a family “is a set of attributes that is shared by products belonging to the same family”, something like a product template.¹² Pimcore’s class definitions specify “which attributes (fields) an object has and what data types they use”.¹³ Inside the PIM, a code defined as text is held as text.

Validation. In Akeneo, a simple select attribute makes users pick from a list instead of typing a thirteenth spelling, a unique value “must be different for each product”, and a GTIN check ensures a value “has 8, 12, 13, or 14 digits and passes the checksum”.¹⁴ Completeness sits on top: “a product is considered as complete if all its required values are filled in”, shown as a percentage per channel and family.¹⁵

Channels. “A channel in Akeneo defines a selection of products and information to export”, such as a website, a print catalogue or a mobile application.¹⁶ An attribute with a value per channel holds the web and marketplace descriptions as two values of one attribute, not two columns.¹⁴

Versions. Pimcore “versions all documents, assets, and data objects automatically”, and its data objects include products.¹⁷¹³ Each version carries the user who saved it, and a Versions tab compares and restores them.¹⁷ That is history by product, with one caution: the same page shows how to switch versioning off for bulk imports, so ask whether supplier imports will be versioned.¹⁷

The set-up cost starts before any software. Akeneo’s documentation says that when a PIM project starts, “the first task to do is to build the structure of the data”.² That means families, attributes, types and channels, decided by someone who knows the range. Running it yourself is the other cost: Akeneo PIM “only works on Linux servers” and needs a web server, PHP, MySQL and Elasticsearch alongside it.¹⁸ Both vendors also list hosted editions.¹²¹⁹

Akeneo’s documentation calls Akeneo PIM open source, in Community and Enterprise editions.² Pimcore calls itself “an open core platform”, under the Pimcore Open Core License.¹⁹ Read either licence before assuming open means free. Neither is named here as a recommendation.

The signals that it is time

No page cited here names a product count at which a spreadsheet stops working, and this post offers none. In our judgement the count matters only through the work it brings: more suppliers, refreshes, editors and places to sell. These are the signals.

1. Two destinations want different values for the same field. Akeneo’s help centre says you might need a new channel “if and only if you need different content for the same attribute”, or a different set of required attributes.¹⁶ In a spreadsheet each destination grows its own description column, and they drift.
2. Several people edit the same products, and you need to know who changed what. History by file and by cell answers that one version or one cell at a time.
3. Supplier refreshes undo corrections. A full file that replaces the last one has to be matched row by row against the corrections made since, or they are lost. Shopify shows the edge: with Overwrite products with matching handles selected, a blank non-required column means “the matching value in the product list is overwritten as blank”.¹ When the same fix is made after every import, the source was never corrected. Supplier data onboarding covers that rule.

Clean data first, whichever you choose

A PIM validates data on the way in: Akeneo’s import job has “to map and validate this data into products”.² Validation is a gate, not a repair. It checks that a value fits the attribute, not that it is true: 10-Dec is valid text, a weight in the wrong unit is still a number, and a GTIN with a correct check digit can belong to another product. Completeness then counts each as done.¹⁵

Nor does a PIM stop the mess arriving. Akeneo’s help centre offers a way to merge select options because, as a catalogue grows, “it’s common to end up with duplicate or redundant options in a simple or multi-select attribute”.¹⁴

So the file has to be cleaned first, whether it is heading for a PIM or a store. That is the step RefynData’s product data enrichment service does: the supplier’s spreadsheet goes in as it arrived, each product is matched to the real product online, gaps are filled, units and spellings are made one per field, non-values such as N/A are cleared, and every value keeps a link to its source.

The output, a Google Sheet or Excel file with one tab per category, is ready to import into a store or a PIM. It is the step before either, not a third option, and one part of what product data enrichment covers.

How to decide, step by step

Six steps, in the spreadsheet you already have, before anyone buys anything.

1. List the destinations, the editors and the refresh cycle. These are where the three signals show.
2. Search the file for the four conversions. Filter every code column for values containing E+, values that look like dates, and codes shorter than their neighbours.
3. Use the spreadsheet’s own controls. Bring supplier CSVs in through Data, From Text/CSV with code columns set to text, rather than double-clicking them. Put a validation list on every column that is a choice. Keep one shared copy in OneDrive or Drive and stop emailing the file.
4. Write the data model down. Product types, the attributes each has, and each attribute’s type, allowed values and unit. It is the first task of a PIM project anyway, and a spreadsheet run to one behaves better.
5. Clean the file to the model. One spelling per value, one unit per field, every identifier as text, every image link fetched, blanks that are blank rather than N/A.
6. Then decide. If the controls from step 3 hold through a full supplier refresh and no signal is true, stay on the spreadsheet. If the signals are there, the model from step 4 is your PIM configuration and the file from step 5 is its first import.

What to do next

Open the supplier file you have and do step 2 first, because a spreadsheet makes those conversions without telling anyone. Then write the model in step 4, whatever you decide. If the signals are there, budget for the data model and the hosting as well as any licence. Either way, the file that goes in has to be clean, so start there.