`scripts/kravet-master-2026/build_csv.py`

36 lines
#!/usr/bin/env python3
"""Build /tmp/kravet_master_2026.csv from the GDrive 'Kravet Price list 1-20-26.xlsx'.
Authoritative Jan-2026 Kravet-family MAP master (8,191 rows; NEW MAP = NEW WHLS x 1.5).
Columns: Item | Brand | CURRENT WHLS | CURRENT MAP | Unit of measure | Collection |
         Use | NEW Wholesale (Incl Tariff) | NEW MAP (Incl Tariff) | effective date | Tariff%.
Rows can be ragged (short tuples) -> pad-guarded. Then run load.sh to push into dw_unified.
Fetch the xlsx first:  rclone copy 'gdrive:Kravet Price list 1-20-26.xlsx' /tmp/
"""
import openpyxl, csv, datetime, sys

SRC = sys.argv[1] if len(sys.argv) > 1 else "/tmp/Kravet Price list 1-20-26.xlsx"
OUT = sys.argv[2] if len(sys.argv) > 2 else "/tmp/kravet_master_2026.csv"

wb = openpyxl.load_workbook(SRC, read_only=True, data_only=True)
ws = wb["Sheet1"]
rows = [r for r in ws.iter_rows(min_row=2, values_only=True) if r and r[0]]

def g(r, i):       return r[i] if i < len(r) else None
def num(x):
    try: return float(str(x).strip())
    except Exception: return ""
def dt(x):
    if x is None: return ""
    if isinstance(x, (datetime.datetime, datetime.date)): return x.strftime("%Y-%m-%d")
    s = str(x).strip(); return s.split(" ")[0] if s else ""
def s(x):          return str(x).strip() if x is not None else ""

with open(OUT, "w", newline="") as f:
    w = csv.writer(f)
    w.writerow(["item","brand","cur_whls","cur_map","uom","collection",
                "use_category","new_whls","new_map","effective_date","tariff_pct"])
    for r in rows:
        w.writerow([s(g(r,0)), s(g(r,1)), num(g(r,2)), num(g(r,3)), s(g(r,4)).upper(),
                    s(g(r,5)), s(g(r,6)).upper(), num(g(r,7)), num(g(r,8)), dt(g(r,9)), num(g(r,10))])
print(f"wrote {OUT} — {len(rows)} rows")