Shopify Product CSV Importer

PythonE-commerce

Cleans a messy supplier sheet into Shopify's product-import CSV format, grouping size variants and sending bad rows to a rejects file instead of guessing.

Run it

cd samples/shopify-importer
python to_shopify.py   # standard library only

Files: supplier_products.csv · shopify_import.csv · rejects.csv · to_shopify.py

Supplier sheet (input)

Product Name	SKU	Size	Price	Stock	Brand	Category	Tags	Description	Image
canvas tote bag	tb-001		$24.00	40	Maple & Co	bags	canvas; eco	Sturdy cotton canvas tote.	https://example.com/img/tote.jpg
Merino Toque	mt-s	s	32,50	12		accessories	winter;wool	Soft merino wool toque.	https://example.com/img/toque.jpg
merino toque	mt-m	m	32.50	8		accessories	winter;wool
Merino Toque	MT-L	L	32.50	n/a		accessories	winter;wool
Ceramic Mug	cm-01		14.5	100	Maple & Co	kitchen	mug	12 oz stoneware mug.	https://example.com/img/mug.jpg
Ceramic Mug Blue	cm-01		14.50	30	Maple & Co	kitchen	mug
	nb-9		9.00	20		stationery
Linen Notebook	nb-10		call for price	15		stationery	paper

Shopify import file

Handle	Title	Body (HTML)	Vendor	Type	Tags	Published	Option1 Name	Option1 Value	Variant SKU	Variant Inventory Qty	Variant Price	Variant Requires Shipping	Variant Taxable	Image Src	Status
canvas-tote-bag	Canvas Tote Bag	<p>Sturdy cotton canvas tote.</p>	Maple & Co	Bags	canvas, eco	TRUE	Title	Default Title	TB-001	40	24.00	TRUE	TRUE	https://example.com/img/tote.jpg	active
merino-toque	Merino Toque	<p>Soft merino wool toque.</p>	House Brand	Accessories	winter, wool	TRUE	Size	S	MT-S	12	32.50	TRUE	TRUE	https://example.com/img/toque.jpg	active
merino-toque								M	MT-M	8	32.50	TRUE	TRUE
merino-toque								L	MT-L	0	32.50	TRUE	TRUE
ceramic-mug	Ceramic Mug	<p>12 oz stoneware mug.</p>	Maple & Co	Kitchen	mug	TRUE	Title	Default Title	CM-01	100	14.50	TRUE	TRUE	https://example.com/img/mug.jpg	active

Rejected for review

row	product name	sku	price	stock	brand	category	tags	reason
7	Ceramic Mug Blue	cm-01	14.50	30	Maple & Co	kitchen	mug	duplicate SKU
8		nb-9	9.00	20		stationery		missing title
9	Linen Notebook	nb-10	call for price	15		stationery	paper	bad price

Code

"""Converts a messy supplier product sheet into a Shopify product-import CSV (one row per variant),
and writes rejects.csv listing rows that need a human. Run: python to_shopify.py   (standard library only)
Columns follow Shopify's documented product CSV format (Handle, Title, Option1 Name/Value, Variant SKU, Variant Price...)."""
import csv, re
COLS=["Handle","Title","Body (HTML)","Vendor","Type","Tags","Published","Option1 Name","Option1 Value","Variant SKU","Variant Inventory Qty","Variant Price","Variant Compare At Price","Variant Requires Shipping","Variant Taxable","Image Src","Status"]
def handle(t): return re.sub(r"[^a-z0-9]+","-",t.lower()).strip("-")
def price(s):
    s=(s or "").replace("$","").replace(",",".").strip() if (s or "").count(",")==1 and "." not in (s or "") else (s or "").replace("$","").replace(",","").strip()
    return f"{float(s):.2f}" if re.fullmatch(r"\d+(\.\d+)?",s) else None
out=[]; rej=[]; seen_sku=set(); first={}
for i,r in enumerate(csv.DictReader(open("supplier_products.csv",encoding="utf-8-sig")),start=2):
    r={k.strip().lower():(v or "").strip() for k,v in r.items()}
    title=" ".join(r["product name"].split()).title(); p=price(r["price"]); sku=r["sku"].upper()
    why=[w for w,bad in [("missing title",not title),("bad price",p is None),("missing SKU",not sku),("duplicate SKU",sku in seen_sku)] if bad]
    if why: rej.append({"row":i,**r,"reason":"; ".join(why)}); continue
    seen_sku.add(sku); h=handle(title); size=r["size"].upper() or "Default Title"
    qty=r["stock"] if r["stock"].isdigit() else "0"
    row=dict.fromkeys(COLS,""); row.update({"Handle":h,"Option1 Value":size,"Variant SKU":sku,"Variant Inventory Qty":qty,"Variant Price":p,"Variant Requires Shipping":"TRUE","Variant Taxable":"TRUE"})
    if h not in first:  # product-level fields only on the first row of each handle
        first[h]=True; row.update({"Title":title,"Body (HTML)":f"<p>{r['description']}</p>" if r["description"] else "","Vendor":r["brand"] or "House Brand","Type":r["category"].title(),"Tags":", ".join(t.strip().lower() for t in r["tags"].split(";") if t.strip()),"Published":"TRUE","Option1 Name":"Size" if r["size"] else "Title","Image Src":r["image"],"Status":"active"})
    out.append(row)
w=csv.DictWriter(open("shopify_import.csv","w",newline=""),fieldnames=COLS); w.writeheader(); w.writerows(out)
if rej:
    w=csv.DictWriter(open("rejects.csv","w",newline=""),fieldnames=list(rej[0])); w.writeheader(); w.writerows(rej)
print(f"{len(out)} variant rows across {len(first)} products -> shopify_import.csv; {len(rej)} rows -> rejects.csv")