← back to Dw Yolo Loop
Ingest GDrive vendor price sheets into dw_unified.vendor_price_sheets (19,797 rows)
1856ee2dcd2a8253ca10c50164c01635a721cfb7 · 2026-06-15 10:24:01 -0700 · Steve Abrams
Pulled + parsed 6 GDrive price lists (Schumacher 2023 master 15,177, Missoni/MH 3,994,
DW Exclusives 512, Ralph Lauren 114) into a new staging table. Keyword header/column
auto-detect + explicit Schumacher map (SKU col0, mid-year price col12, unit col7).
Carries unit_of_measure (84% Schumacher = YARD fabric). Staging only; resolving to
shopify_products.cost is a separate gated step (must carry unit).
Files touched
M .gitignoreA scripts/price-sheets/load-price-sheets.py
Diff
commit 1856ee2dcd2a8253ca10c50164c01635a721cfb7
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Mon Jun 15 10:24:01 2026 -0700
Ingest GDrive vendor price sheets into dw_unified.vendor_price_sheets (19,797 rows)
Pulled + parsed 6 GDrive price lists (Schumacher 2023 master 15,177, Missoni/MH 3,994,
DW Exclusives 512, Ralph Lauren 114) into a new staging table. Keyword header/column
auto-detect + explicit Schumacher map (SKU col0, mid-year price col12, unit col7).
Carries unit_of_measure (84% Schumacher = YARD fabric). Staging only; resolving to
shopify_products.cost is a separate gated step (must carry unit).
---
.gitignore | 2 +
scripts/price-sheets/load-price-sheets.py | 137 ++++++++++++++++++++++++++++++
2 files changed, 139 insertions(+)
diff --git a/.gitignore b/.gitignore
index c5c86d0..ee1c4e7 100644
--- a/.gitignore
+++ b/.gitignore
@@ -19,3 +19,5 @@ data/google-feed/*.csv
data/google-feed/*.md
data/google-feed/report.json
data/kravet-cost/
+data/schumacher-cost/
+data/thibaut-cost/
diff --git a/scripts/price-sheets/load-price-sheets.py b/scripts/price-sheets/load-price-sheets.py
new file mode 100644
index 0000000..11252ae
--- /dev/null
+++ b/scripts/price-sheets/load-price-sheets.py
@@ -0,0 +1,137 @@
+#!/usr/bin/env python3
+"""
+load-price-sheets.py — ingest every GDrive vendor price sheet into
+dw_unified.vendor_price_sheets (staging). Keyword-detects the header row + the
+SKU / wholesale / retail / unit columns per sheet (formats vary wildly: header in
+row 1 or 2, Italian 'Listino', multi-sheet workbooks). Per-file vendor hint.
+
+READ→STAGE only. Resolving staging → shopify_products.cost is a separate gated step
+(and must carry unit, per the YARD/ROLL lesson). Idempotent per source_file (deletes
+prior rows for that file before reload).
+"""
+import openpyxl, glob, os, re, json, subprocess, sys
+
+SKU_KEYS = ['sku','item','articolo','pattern number','js number','pattern_number','mfr','style']
+WHLS_KEYS = ['new wholesale','new cost','wholesale','whls','trade price','listino wholesale','cost of goods','current cost','mid year price','current price','cost','net','price']
+RETAIL_KEYS= ['new map','new retail','map','listino retail','current retail','retail','msrp']
+UNIT_KEYS = ['unit of measure','unit','uom']
+VENDOR_COL_KEYS = ['vendor','brand']
+
+# per-file vendor hint (None => read a Vendor/Brand column per row) + sheets to skip
+VENDOR_HINT = {
+ 'Kravet Price list 1-20-26.xlsx': None, # already in kravet tables -> skip below
+ 'Designer Wallcoverings Exclusives Price Increase 2023.xlsx': 'Designer Wallcoverings',
+ 'MH 2023 PRICE LIST USD LANDED USD RETAIL.xlsx': 'MH-2023-UNIDENTIFIED',
+ 'MIssoni Price .xlsx': 'Missoni',
+ 'Mini Modern - Wallpaper price list.xlsx': None, # has per-row Vendor/Brand
+ 'Ralph Lauren Price Sheet.xlsx': 'Ralph Lauren',
+ 'Schumacher_2023 Master Price Increase List- MD 7.18.xlsx': 'Schumacher',
+}
+SKIP_FILES = {'Kravet Price list 1-20-26.xlsx'} # redundant w/ kravet_authoritative_pricing
+
+def norm(s): return re.sub(r'[^a-z0-9]','',str(s or '').lower())
+def num(v):
+ if v is None: return None
+ try:
+ x=float(str(v).replace('$','').replace(',','').strip())
+ return x if x>0 else None
+ except: return None
+def matchcol(headers, keys):
+ hl=[str(h).lower().strip() if h is not None else '' for h in headers]
+ # pass 1: exact header == key (so a column literally 'SKU' beats 'Item Description')
+ for k in keys:
+ for i,h in enumerate(hl):
+ if h==k: return i
+ # pass 2: header starts with key
+ for k in sorted(keys, key=len, reverse=True):
+ for i,h in enumerate(hl):
+ if h.startswith(k): return i
+ # pass 3: contains (longest key first to prefer 'new wholesale' over 'wholesale')
+ for k in sorted(keys, key=len, reverse=True):
+ for i,h in enumerate(hl):
+ if k in h: return i
+ return None
+
+def find_header(rows):
+ # the row (within first 8) that contains a SKU-like keyword AND a price-like keyword
+ for ri in range(min(8,len(rows))):
+ cells=[str(c).lower() if c is not None else '' for c in rows[ri]]
+ has_sku=any(any(k in c for k in SKU_KEYS) for c in cells)
+ has_price=any(any(k in c for k in WHLS_KEYS+RETAIL_KEYS) for c in cells)
+ if has_sku and has_price: return ri
+ return None
+
+def best_sheet(wb):
+ best=None
+ for name in wb.sheetnames:
+ ws=wb[name]
+ rows=[]
+ for i,r in enumerate(ws.iter_rows(values_only=True)):
+ rows.append(r)
+ if i>=12: break
+ hr=find_header(rows)
+ if hr is not None:
+ # count data rows
+ n=sum(1 for _ in ws.iter_rows(values_only=True))
+ if not best or n>best[3]: best=(name,hr,rows,n)
+ return best
+
+def load_file(f):
+ base=os.path.basename(f)
+ if base in SKIP_FILES: return (base,'SKIP (redundant)',0)
+ wb=openpyxl.load_workbook(f, read_only=True, data_only=True)
+ bs=best_sheet(wb)
+ if not bs: return (base,'NO HEADER FOUND',0)
+ name,hr,_,_=bs
+ ws=wb[name]
+ it=ws.iter_rows(values_only=True)
+ for _ in range(hr): next(it)
+ headers=list(next(it))
+ ci_sku=matchcol(headers,SKU_KEYS); ci_w=matchcol(headers,WHLS_KEYS)
+ ci_r=matchcol(headers,RETAIL_KEYS); ci_u=matchcol(headers,UNIT_KEYS)
+ ci_v=matchcol(headers,VENDOR_COL_KEYS)
+ if ci_sku is None or (ci_w is None and ci_r is None):
+ return (base,f'sheet={name} unmappable (sku={ci_sku} w={ci_w} r={ci_r})',0)
+ vhint=VENDOR_HINT.get(base)
+ rows_out=[]
+ for r in it:
+ if not r or ci_sku>=len(r): continue
+ sku=r[ci_sku]
+ if sku is None or str(sku).strip() in ('','None'): continue
+ w=num(r[ci_w]) if ci_w is not None and ci_w<len(r) else None
+ rt=num(r[ci_r]) if ci_r is not None and ci_r<len(r) else None
+ if w is None and rt is None: continue
+ unit=(str(r[ci_u]).upper() if ci_u is not None and ci_u<len(r) and r[ci_u] else None)
+ vendor=vhint or (str(r[ci_v]) if ci_v is not None and ci_v<len(r) and r[ci_v] else 'UNKNOWN')
+ rows_out.append((vendor,str(sku).strip(),norm(sku),w,rt,unit,base))
+ return (base,f'sheet={name} hdr@{hr+1} sku@{ci_sku} w@{ci_w} r@{ci_r} u@{ci_u}',rows_out)
+
+def psql(sql, stdin=None):
+ return subprocess.run(['psql','-d','dw_unified','-v','ON_ERROR_STOP=1','-c',sql] if stdin is None
+ else ['psql','-d','dw_unified','-v','ON_ERROR_STOP=1','-c',sql],
+ capture_output=True, text=True)
+
+total=0; report=[]
+for f in sorted(glob.glob('/tmp/price-sheets/*.xlsx')):
+ base,info,rows=load_file(f)
+ if isinstance(rows,int) or not rows:
+ report.append(f' [skip] {base}: {info}'); continue
+ # idempotent reload for this file
+ subprocess.run(['psql','-d','dw_unified','-c',
+ f"delete from vendor_price_sheets where source_file = $${base}$$;"],
+ capture_output=True, text=True)
+ # bulk insert via COPY
+ import csv, io
+ buf=io.StringIO(); w=csv.writer(buf)
+ for row in rows: w.writerow(list(row[:-1])+[base])
+ buf.seek(0)
+ p=subprocess.run(['psql','-d','dw_unified','-c',
+ "copy vendor_price_sheets(vendor,mfr_sku,mfr_sku_norm,wholesale,map_or_retail,unit_of_measure,source_file) from stdin with csv"],
+ input=buf.getvalue(), capture_output=True, text=True)
+ ok = 'COPY' in (p.stdout+p.stderr)
+ n=len(rows)
+ total += n if ok else 0
+ report.append(f' [{"OK " if ok else "ERR"}] {base}: {n} rows | {info}' + ('' if ok else ' | '+p.stderr.strip()[:120]))
+
+print('\n'.join(report))
+print(f'\nTOTAL loaded into vendor_price_sheets: {total}')
← 18fc285 Kravet under-MAP reprice: 73 variants lifted to MAP (live, v
·
back to Dw Yolo Loop
·
Resolve staged price-sheet costs into shopify_products.cost 8965036 →