Grow · Intermediate
Product data from Excel to the website: one source, no retyping
In many companies the real product database is a spreadsheet, and the website is a second copy that someone types by hand. The copy is always a little behind. An import removes the copy.
Photo: Ibrahim Sharif, public domain (CC0)
In this guide
Short answer
Keep the product data in one table and let the website read it. Nobody types a product twice. The sheet needs one row for each variant, one column for each property and an article number that never changes; the import matches on that number, so a second import updates products and does not duplicate them.
What does a usable product sheet look like?
A usable sheet is a plain list: one header row, then one row for each variant, and nothing else. What makes a sheet pleasant for a person to read, such as merged cells, group headings between the rows and notes typed into a value, stops a program from reading it.
| Part of the sheet | Readable | Not readable |
|---|---|---|
| Rows | One row for each variant | One row for a product, variants listed in one cell |
| Columns | One property in each column | “Size / weight” together in one cell |
| Article number | Stored as text, never changed | Reformatted, reused or renumbered |
| Values | 2.5 in one column, bar in the next | “approx. 2.5 bar (max.)” |
| Pictures | File name in a column | Picture pasted into the sheet |
Two rules matter most. First, one row for each variant: a valve in six sizes has six rows that share the product name and differ in size and article number. Second, numbers and units apart. A cell that contains “2.5 bar” is text and cannot be filtered or sorted.
How does the import work, and why does a second import not duplicate products?
The import reads the sheet row by row and looks up each article number: if the product exists it is updated, if not it is created. That is why the article number must never change, and why matching on the product name is fragile: one corrected spelling creates a second product.
- ExportSave the sheet in the agreed format, usually CSV or an Excel file, with the header row unchanged.
- CheckThe import compares the header with the columns it expects and refuses a file with missing or renamed columns.
- MatchEach row is matched to a product and variant by its article number.
- Create or updateNew article numbers become new products. Known ones receive the new values. Agree in advance what an empty cell means, usually “leave unchanged”.
- ReportThe import lists what was created, what was updated and which rows were skipped, with the reason.
The same route should work in the other direction. An export of the website’s catalogue in the same column layout gives you a current sheet at any time. That is how our own product catalogues are maintained: export, change, import again.
What goes wrong with product data from Excel?
Almost every failed import has one of a few causes, and all of them are in the file, not on the website.
| What you see | Cause | What to do |
|---|---|---|
| Article number 00417 arrives as 417 | The cell is a number, so leading zeros are dropped | Format the column as text before entering data |
| A long code shows as 4.01235E+12 | Excel displays long numbers in scientific notation | Store codes as text |
| A size such as 3-4 becomes a date | Excel recognises a date in the entry | Text format for that column |
| 2,5 is read as 25 or as text | Decimal comma in one place, decimal point in another | Agree one notation and tell the import which |
| All data lands in one column | CSV separated by semicolons, import expects commas | Set the separator in the import, or use an Excel file |
| Umlauts and accents are broken | File saved in an older character encoding | Save as CSV UTF-8 |
| Products appear twice | Article number changed, or has a space at the end | Never change numbers; remove spaces |
The decimal comma deserves attention in Europe. With German, French, Polish or Croatian regional settings, Excel by default writes decimals with a comma and separates CSV columns with a semicolon. With British settings it uses a point and a comma.
How much time does an import save?
The saving grows with every change. The chart is an illustrative example, not a measurement: new values for 300 products.
Typed into the website at two minutes for each product, that is 600 minutes, or 10 hours, and another 2 hours to check for typing errors: 12 hours. From the sheet, the values are changed with a formula or a paste (1 hour), imported (half an hour) and spot-checked (1 hour): 2.5 hours.
Illustrative example: hours of work to change 300 products
When is a sheet no longer enough?
A sheet reaches its limit when several people change it at once, or when the same data already lives in another system. Typical signs: prices are copied from the ERP system into the sheet by hand, or nobody is sure which file is the current one.
At that point the source should be the ERP system or a product information management system (PIM), and the website should receive the data through a scheduled import or an API connection. The rules stay the same: one source, a stable article number, one row or record for each variant. A tidy sheet is therefore not wasted work.
How we do it: before we build anything we ask for the sheet as it is today. We read it, list what an import could not understand, and agree the column layout with the people who maintain it. The first full import is a test, checked together against the sheet.
Questions we hear often
Want this sorted without doing it yourself?
Tell us what you are working on. A person replies within one working day.