Importing a product catalog: how to prepare data, images, and categories

An import almost always fails before it even starts, in the source file. The products go in, the counts add up, and then you notice that sizes have become separate products, that prices with a comma were read incorrectly, and that half the photos ended up on the wrong item. At that point, fixing it costs more than starting over.

Data preparation is the part of the job you can do without touching the website, and it's the part that determines the outcome. This guide helps you put it in order: what needs to be in the file, what to clean up first, how to test it on a few products, and what to check afterward.

The product code comes before everything else

Every row in the file needs a code that identifies that product and no other, stable over time. It's the key that future updates will be recognized by, that images will be linked to, and that will prevent duplicates. Without it, every subsequent import creates copies instead of updating.

• The code isn't the product name. Names change, get written in different ways, and contain accents and spaces.
• The code isn't the spreadsheet row number, and it must not change if the file gets reordered.
• If codes already exist in the business management system, use those: having two code systems is the most common cause of inventory that doesn't add up.
• If the product has variants, you need two levels: the product code and the code for each individual variant. Confusing them produces catalogs where the same t-shirt appears five times.

Categories, attributes, and required fields

The second step is to decide on the destination structure before adapting the data, not the other way around. Three things need to be fixed on paper.

1. Categories: a tree with few branches and names a customer would actually use. Every product belongs to one main category; other memberships are handled with filters, not by multiplying categories.
2. Attributes: the ones used to filter or choose (size, material, power, compatibility) are fields, not text inside the description. The ones that are just read can stay in the description.
3. Required fields: which pieces of data block publication if missing. Usually code, name, price, category, and at least one image. A product missing one of these shouldn't be let in halfway: it should stay out and appear in a rejected-items list.

The mess you always find

A catalog that's grown over the years contains the same things in every company. It's worth looking for them beforehand, because during import they become silent.

• Non-identical duplicates: the same product written two different ways, with two codes, maybe with two different prices. The most recently updated one wins only if someone decides that.
• Inconsistent formats: prices with a period and with a comma, measurements with and without units, dates in two formats, invisible trailing spaces on codes. Invisible spaces are the number one cause of images not being linked.
• Descriptions containing formatting copied from a text document: it drags along useless code that then shows up on the page.
• Images: file names that don't match any code, different formats, huge photos, products with zero images and products with twelve identical ones. Linking should be done by code, not by product name.
• Products that shouldn't go through: discontinued items, samples, articles used only internally. Excluding them is a business decision, not a technical one, and it needs to be made beforehand.

An example layout

Here's how to write the correction rule next to each field. The values are illustrative and meant to show the type of error, not to suggest a universal format.

Rows that don't follow a rule shouldn't be fixed by hand one at a time: fix the rule and regenerate the file. Only fix by hand what's genuinely a one-off case.

Testing on a sample, before the full import

The sample isn't "the first fifty products in the file": it's a set chosen because it contains the difficult cases.

1. A simple product, no variants, with a single image.
2. A product with many variants and different prices per variant.
3. A product with accents, apostrophes, and special characters in the name.
4. A product on sale, or with two prices, or with a price on request.
5. A product you know is messy: missing category, missing image, extremely long description.
6. A product that should stay excluded, to verify the filter works.

On the sample, you look at the published page, not the file: how the name appears, where the category ended up, whether the variants can be selected, whether the image is the right one and isn't distorted.

Checks after the import

You need numbers, not impressions. Compare the destination with the source, field by field, and keep track of the rejects.

• Count: how many products in the file, how many imported, how many rejected and for what reason. The sum has to add up exactly.
• Prices: the most expensive and cheapest product on the site should be the ones you expect. If the most expensive one costs a thousand times more than the others, it's a decimal separator read incorrectly.
• Variants: how many products have variants in the source and how many in the destination.
• Images: how many products with no image at all, listed by code.
• Excluded: the list of products that shouldn't have gone in, checked one by one because there are few of them.

What this guide doesn't cover

This guide covers the first catalog transfer: preparing the data, testing it, and checking it. Ongoing synchronization — stock that changes daily, prices updated from the business management system, products that get discontinued — is a different problem, with its own rules about which system has authority over which piece of data, and it's covered separately. Organizing variants and writing product pages also deserve their own discussion.