Convert an Excel Product Catalogue to MySQL INSERT for WooCommerce

Flat product staging table

A simple catalogue sheet becomes a MySQL CREATE TABLE plus a multi-row INSERT with backtick identifiers and a TINYINT(1) featured flag.

Excel (Sheet1):
 sku    | name        | price | featured
 WC-001 | Joe's Coffee| 12.50 | TRUE
 WC-002 | Tea Tin     | 8.00  | FALSE

Generated MySQL (table 'products'):
CREATE TABLE IF NOT EXISTS `products` (
  `sku` TEXT,
  `name` TEXT,
  `price` REAL,
  `featured` TINYINT(1)
);

INSERT INTO `products` (`sku`, `name`, `price`, `featured`)
VALUES
  ('WC-001', 'Joe''s Coffee', 12.5, 1),
  ('WC-002', 'Tea Tin', 8, 0);

Re-runnable load with INSERT IGNORE

The ON CONFLICT toggle renders as INSERT IGNORE in MySQL, so re-running the catalogue skips SKUs already present instead of erroring (requires a unique key on sku).

Options: MySQL, ON CONFLICT DO NOTHING

INSERT IGNORE INTO `products` (`sku`, `name`)
VALUES
  ('WC-001', 'Joe''s Coffee'),
  ('WC-002', 'Tea Tin');

-- second run: existing SKUs skipped, no error

Fix price columns to DECIMAL

Decimals infer REAL (floating point), which can drift on money. Hand-edit the DDL to DECIMAL(10,2) before loading prices.

Generated:
  `price` REAL

Edit before running (money-safe):
  `price` DECIMAL(10,2)

-- 12.50 then stores exactly, not 12.4999...

Drop supplier-only columns

Catalogue sheets often carry margin/supplier notes you don't want in the products table. Exclude them.

Exclude columns: margin, supplier_notes, cost

Result: only customer-facing columns (sku, name,
price, stock, featured) appear in the CREATE TABLE
and INSERTs.

Mapping toward wp_posts (manual)

If you must seed WordPress tables directly, name your sheet columns to match wp_posts and set the table name accordingly — but you own the column mapping; this is not automatic.

Sheet headers renamed to WP schema:
 post_title | post_status | post_type
 Joe's Coffee | publish    | product

Table name: wp_posts
→ generates INSERT INTO `wp_posts` (`post_title`, ...)
   You still handle wp_postmeta + term links separately.

WooCommerce products live across wp_posts + wp_postmeta with serialized meta and term relationships. This tool generates flat INSERTs from your columns — great for a staging/custom table, not a one-click wp_posts seeder. For standard imports, WooCommerce's own CSV importer maps to the WP schema for you.

Decimal prices infer REAL (floating point). 12.50 can store as 12.4999.... Edit the DDL to DECIMAL(10,2) before loading money columns, or store prices as integer cents.

WordPress meta like _product_attributes is PHP-serialized. The tool only emits scalar values; it cannot build serialized arrays. Construct those in PHP/WP-CLI, not here.

Product names like Men's Shoes are escaped by doubling the quote ('Men''s Shoes'), valid in MySQL. No manual cleanup needed for apostrophes.

INSERT IGNORE only skips rows that violate a unique/primary key. If the staging table has no unique key on sku, every row inserts (and duplicates accumulate). Add a UNIQUE(sku) constraint for the skip to work.

Excel dates infer INTEGER. WordPress expects datetime strings. Standardise the column to ISO text with the date standardizer first; MySQL accepts '2024-01-01 00:00:00'-style strings into datetime columns.

Only the first sheet is read. Product variations on a second tab are ignored. Generate each sheet separately, or combine with the sheet joiner first.

Code generation caps at 100 rows (Free), 1,000 (Pro), 10,000 (Pro+Media), unlimited (Developer). A 30,000-SKU catalogue is truncated below Developer; for very large loads, consider WooCommerce's CSV importer or a LOAD DATA into staging.

A SKU like 00123 in a number-formatted cell becomes 123. Format the SKU column as Text in Excel so it's read as a string and emitted as '00123'.