Spreadsheet skills: handling catalog files without damaging the data

A template is a contract, not a worksheet. Marketplaces take bulk product data through a template: a spreadsheet laid out the way their system expects. The platform reads the file by header: one or more header rows name each column, and a reference tab (one or two tabs, usually labelled data definitions and valid values) explains each field and lists its allowed values, the spellings it accepts. Never retype, reorder or delete the headers; a renamed column is ignored or rejected.

Save in the format the platform asks for. Most templates are Excel workbooks (.xlsx, sometimes macro-enabled .xlsm); some platforms take plain text instead (.csv, .tsv or tab-delimited .txt), which holds values and separators only, so formatting, other worksheets and formulas disappear on save. Plain text also carries an encoding trap: unless saved in the encoding the platform specifies (usually UTF-8, a text-encoding standard), accented letters, symbols and non-Latin characters arrive garbled.

Keep the original. Leave the downloaded template untouched; work in a renamed duplicate.

Spreadsheets guess, and guess wrong. A GTIN (the barcode number) beginning with 0 loses that digit, and a 12- to 14-digit code often displays in scientific notation. A SKU (stock-keeping unit, your own code for an offer) like 1-2 or MAR-1 turns into a date, as does a size written 3/4. Format identifier columns as text before you paste, or set the column type to text in the import dialog; a dropped leading zero can be padded back if you know the code length. A rounded-away digit is gone.

The invisible damage. Trailing and non-breaking spaces make two identical-looking codes different values. A word processor's auto-correct substitutes curly quotes, and a field expecting a straight inch mark (") can reject them.

Check a column before you upload. =LEN(A2) shows the length (a UPC, the North American code, is 12 digits; an EAN, 13). =TRIM(A2) strips ordinary outer spaces but not a non-breaking space; remove that with SUBSTITUTE(A2,CHAR(160),"") first. =TEXT(A2,"000000000000") pads dropped zeros back. A distinct count against the row count exposes duplicates.

Join by the identifier. Your source sheet meets a platform export (its current view of your items) or an error file (failed rows, with reasons). A lookup formula (XLOOKUP, VLOOKUP, or INDEX/MATCH) brings a column across: find this row's key in the other sheet and return the value beside it. Key on SKU or GTIN, never on names.

Three quiet failures.

• Approximate match. VLOOKUP and MATCH match approximately unless told otherwise (XLOOKUP defaults to exact); demand exact, or a near-miss returns a plausible wrong row.
• Duplicate keys. A key listed twice in the lookup sheet returns its first row every time; count distinct keys before merging.
• Blanks versus not found. A matched row with no value comes back blank or, in Excel, as a 0 easy to mistake for a real zero; #N/A means the key was absent. Check the export before treating a 0 as data or an #N/A as a blank.

Check the match count. Count matched and unmatched rows, and explain every miss before trusting the merge.

Filter, don't delete. Deleting rows you are not working on destroys context; filter to the ones that matter.

Keep a change log column. Add your own column, outside the template's, recording what changed and why ("capacity corrected against the specification"); remove it from the copy you upload, and from nowhere else.

Resubmit only the failed rows. If 40 of 900 rows failed, the next file holds those 40, corrected, not all 900 (a file the platform refused outright has no failed rows: fix it and resend); resubmitting successful rows can overwrite good data and clutters the next error file.

Count before you report. A pivot table (a summary that groups rows by a column's values) or =COUNTIFS gives counts by status or error reason. That is the counting behind the catalog sweep the platform tracks teach (Amazon 3P → Reports and catalog health): items live, items not live, and why.

Name versions by date and purpose, such as YYYY-MM-DD_bottles_setup_v2.xlsx, and never overwrite the previous version.

Run it every time, however small the file.

1. Start from the platform's current template. An old copy may lack a column or hold retired values.
2. Paste values only, identifiers as text. Text-format identifier columns first, then paste values, not formulas or formatting.
3. Validate required fields and allowed values against the reference tab. Every required column filled, every constrained field spelled exactly as listed.
4. Check identifier length and duplicates. LEN on every identifier column, TRIM where needed, a distinct count equal to the row count.
5. Save in the required format with a dated name. Its format, its encoding if plain text, date and purpose in the name.
6. Upload and keep the file with its processing result (its per-row report of what passed and failed); the next round starts from both.

Platform specifics: Amazon 3P → Listing creation (flat files, processing reports); Walmart 3P → Item setup (spec templates, Activity Feed); Walmart 1P → Content maintenance (the match key in bulk exports); Amazon 1P → Unavailable pages (the error sheet).