Excel SKU Matrix Bulk Import & Export
For manufacturing enterprises maintaining dozens or hundreds of industrial dimensions, entering variants one-by-one in a browser form is painfully slow.
Appsuite features a dedicated Excel 1688-style Matrix Sync Engine. Export sales reps and merchandisers can update pricing, stock, and parameters in Excel and package product photos in a .zip archive for single-click synchronization.
1. Download Practice Pack
Section titled “1. Download Practice Pack”To follow this tutorial with zero setup, download the official sample pack created for the VD Rubber Mount:
- 📥 Download Official Template: VD-Sample.xlsx
(Pre-loaded with 10 sample SKUs and spec dictionaries) - 📦 Download Matching Image Archive: VD-Sample-Images.zip
(Contains actual component photos:vd-hero.jpg,vd-thread.jpg, etc.)
2. Exporting the Current Offering Matrix
Section titled “2. Exporting the Current Offering Matrix”To export an existing product matrix to Excel:
- In Payload CMS, open any multi-variant offering (e.g.,
/admin/collections/offerings/5). - Locate the variant manager header and click Export SKU Matrix (.xlsx).
- The system generates and downloads a pre-formatted
.xlsxfile tailored to that product’s active spec columns.
3. The Two-Tab Excel Architecture
Section titled “3. The Two-Tab Excel Architecture”Opening the .xlsx file reveals two synchronized worksheets:
Sheet 1: SKUs (Variant Master Sheet)
Section titled “Sheet 1: SKUs (Variant Master Sheet)”Each row represents an orderable, itemized SKU:
| Column | Standard Header | Requirement | Specification & Format |
|---|---|---|---|
| Col A | SKU | Required (Unique string) | Factory part number (e.g., VD8*8 M3*6, VD20*15 M6*18). Must be unique. |
| Col B | Price | Required (Number) | Unit base price (numeric only, e.g., 0.16 or 0.45). Do not include $ or currency symbols. |
| Col C | Stock | Optional (Integer) | Available quantity (e.g., 9999). Empty defaults to standard stock. |
| Col D | Weight (kg) | Optional (Kilograms) | Unit weight in kilograms (e.g., 0.015 represents 15g). Feeds sample air freight rate calculations. |
| Col E | Image Filename / URL | Optional (String) | Filename (e.g., vd-hero.jpg matching an asset in your ZIP) or full external CDN URL (https://...). |
| Col F+ | Label [key] (Dynamic Specs) | Per Column Rules | Header Syntax: Label [key] (e.g., subType [subType], dimensions [dimensions], thread [thread], hardness [hardness]). Brackets [...] hold the internal database key. |
Sheet 2: Dictionary (Attributes & Multilingual Option Labels)
Section titled “Sheet 2: Dictionary (Attributes & Multilingual Option Labels)”Maintains multilingual labels for fixed enum options and optional image Alt overrides:
| Column | Standard Header | Purpose | Example Value |
|---|---|---|---|
| Col A | Attribute Key | The attribute key matching Sheet 1 brackets (or image-alt) | subType, dimensions, thread, hardness, image-alt |
| Col B | Option Value / Code | Internal database value/code (or image filename) | damper, Ø8*8mm, M3*6, vd-hero.jpg |
| Col C | Label (EN) | Display label rendered on English storefronts | Rubber Damper, Ø 8×8 mm, M3 × 6 mm |
| Col D | Label (ZH) | Display label rendered on Chinese storefronts | 橡胶减震器, 直径8×高8毫米, M3×6 螺纹 |
[!TIP] Native Dropdowns & Automatic Key Mapping: When exporting an Excel from CMS, Sheet 1 columns automatically link to native Excel data validation dropdowns fed by Sheet 2. This prevents typographical errors. During import, the engine extracts the key inside square brackets
[key]to map columns with zero ambiguity.
4. Packaging Images & Uploading
Section titled “4. Packaging Images & Uploading”- Prepare ZIP: Place all referenced photos into a standard folder and compress directly into a
.zipfile (do not nest an extra parent directory inside). - Open Import Dialog: In Payload CMS, click Import SKU Matrix.
- Attach Files:
- Excel File: Select your completed
.xlsx. - Images Archive (ZIP): Select the matching
.zip(optional if no new images are required).
- Excel File: Select your completed
- Select Import Mode:
| Import Mode | Behavior & Recommended Use Case |
|---|---|
| Replace | • Overwrites and wipes all existing variants, rebuilding strictly from the sheet. • Use case: Initial catalog onboarding or complete product restructuring. |
| Merge | • Matches existing items by sku.• Existing SKUs: Updates price, stock, weight, and specs. • New SKUs: Appended to the end. • Omitted SKUs: Preserved intact without deletion. • Use case: Quarterly price updates or adding seasonal sizes. |
- Click Submit Import:
- The server validates rows, extracts and assigns images, and reports progress.
- Upon completion, a summary modal confirms processed SKUs and created images.
- The page automatically reloads to display the updated matrix.
5. Triple Image Resolution Safeguards
Section titled “5. Triple Image Resolution Safeguards”Appsuite resolves image references in Column E through three redundant methods:
- Direct ZIP Archive Extraction: Unpacks images matching the filename, creates Payload media entries, and links them to the variant.
- Remote URL Ingestion: Downloads external HTTP/HTTPS image URLs directly into secure storage.
- Existing Media Reuse: If an image with that filename already exists in the media library, Appsuite reuses it without creating duplicates.
6. Common Error Diagnostics
Section titled “6. Common Error Diagnostics”- Error: Missing required column: SKU
- Fix: Ensure the first row contains column headers and Column A is named
SKU.
- Fix: Ensure the first row contains column headers and Column A is named
- Error: Price must be a valid number
- Fix: Verify all price cells contain raw numbers without currency symbols or text.
- Images not showing up?
- Fix: Check that file extensions (
.jpgvs.png) in Column E match the exact casing of files inside the ZIP archive.
- Fix: Check that file extensions (