Importing Product Maps with AI

⚠️ Read this before importing — for humans and AI agents alike

This page is a precise specification for an AI/LLM agent that converts a supplier’s CSV/Excel planogram into the two files this system imports. The mapping is easy to get wrong, and a wrong planogram silently misconfigures the machine. Two mistakes are the most common — both are called out in detail below:

1. Selection numbers are not MDB codes. The numbers printed on a visual planogram (e.g. 10, 11, 20, 50) are the customer’s front-panel labels. Our mdb_code is a 0-based physical coil address (tray*100 + column). They must be converted, not copied.
2. Motor couplings live in a separate file. Double/triple spirals occupy 2–3 coils that must be coupled. Couplings are never part of the product-map import JSON — they go in their own motor_couplings file / endpoint.

Always review the generated product maps and couplings against the physical machine before importing — the importer replaces the machine’s entire planogram.

How to use this page: click “Copy this guide for your AI agent” below (or “Copy for LLM” at the top of the page), paste it into your AI tool along with the supplier’s spreadsheet, and ask it to produce the import files. Then review and import them from the vending machine’s Product Map → Batch Import screen.

Audience: an AI/LLM agent that receives an arbitrary CSV or Excel product-map / planogram file for a vending machine and must emit the files the PPE system needs to import that planogram.

Your job: read the spreadsheet, map its columns onto the schema below, validate every row, and produce two output files (see §0). You are not calling any API — you produce the files; a human or a follow-up step consumes them.

0. What you must output — two files

Produce both of these, representing the same planogram. We keep both because we don’t always know how the result will be consumed:

1. product_maps.json — the exact request body the admin API accepts. Used when a script/agent imports directly via the API (headless).
2. product_maps.csv — the canonical CSV in the app’s own format (§6). Most likely a human will upload this through the admin UI (Vending Machine → Product Map → Batch Import Product Map (Upload CSV)), which parses it in the browser and calls the same API.

If the file also defines motor couplings (a Coupled MDB Code column, §8), the couplings travel inside the CSV as that column, and additionally as a separate motor_couplings.json for the direct-API path.

Both files must be byte-for-byte consistent in what they describe. One import targets exactly one machine; if the spreadsheet mixes machines, split by machine and emit one set of files per machine.

1. What a “product map” is

A product map is one selection slot on one vending machine: “MDB code N on this machine dispenses product X, holds up to cap units, was last refilled to restock units, and warns at low_stock_alert.” A planogram is the full set of these rows for a machine.

Import is whole-machine replacement, not a merge. The server marks every existing product map on the target machine inactive and inserts your rows as the new active set. Your output must therefore contain every slot the machine should have afterwards — any slot you omit is effectively removed. Never emit a partial/delta file unless the user explicitly confirms the input is already the complete final planogram.

The machine is identified by its vending_machine_uuid, which is a path parameter / a separate input — never part of the JSON or a CSV column.

2. Target JSON schema (product_maps.json)

{
  "product_maps": [
    {
      "product_name": "Nitrile Gloves Medium",
      "mdb_code": 1210,
      "capacity": 10,
      "restock_count": 10,
      "low_stock_alert": 2,
      "is_stackable_item": false
    }
  ]
}

product_maps is an array of row objects with exactly these fields:

Types matter. mdb_code, capacity, restock_count, low_stock_alert must be JSON integers, not strings. is_stackable_item must be a JSON boolean, not "YES"/"true". product_name is a trimmed string.

No leading zeros in JSON. mdb_code is a plain integer: a slot displayed as 0003 is written 3 in the JSON. (Leading zeros only matter for human-readable decoding — §3.)

3. The mdb_code (slot number) — what the digits mean

mdb_code is not an arbitrary ID. It is a positional code that encodes where the slot physically sits on the machine: which actuator, which tray, which column. Treat it as a 4-digit, zero-padded code and read it as three fields, all 0-based indices:

mdb_code (pad to 4 digits) =  A  T  C C
                              │  │  └─┴── column index  (0-based, last 2 digits)
                              │  └─────── tray index    (0-based, 1 digit)
                              └────────── actuator index(0-based, 1 digit)

Worked decodes:

So 1210 = actuator #1, tray #2, column #10 (the 11th physical column, counting from 0).

Rules for the agent:

• In JSON, emit the plain integer (1210, 3). Do not zero-pad in JSON.
• To decode/validate a code, left-pad to 4 digits first, then split A | T | CC.
• If the source spreadsheet already gives codes in this positional form (or as separate Actuator/Tray/Column columns), compute mdb_code as actuator*1000 + tray*100 + column (with the 0-based indices). If it gives a single code column, pass it through as an integer.
• If the source provides only human labels like “Tray 3, Column 11” (1-based), convert to 0-based indices first (subtract 1), then compose.
• Sanity-check: after decoding, actuator/tray/column should be plausible for the machine (e.g. column index < number of columns on that tray). Warn on anything that looks off — a code that decodes to “column 87” is almost certainly a parse error.
• This 4-digit A T CC layout assumes ≤10 actuators, ≤10 trays, ≤100 columns. If a machine exceeds that (codes with 5+ digits, or the user describes different field widths), stop and confirm the encoding rather than guessing.

3.5 Visual spiral planograms — selection numbers ≠ MDB codes ⚠️ READ THIS

Many real inputs are not tidy one-row-per-slot tables. They are visual planogram grids (typical for snack / spiral machines): the sheet is drawn to look like the machine, with one horizontal band per tray, and inside each band three stacked rows:

 (top)     product name over each coil it occupies     Item A    |     Item A     |  Item B  | ...
 (middle)  capacity (the per-selection count)              8    8    |    8     8     |   14     | ...
 (bottom)  the customer's SELECTION number             [   10   ]   |   [   11  ]    |   13     | ...

Two hard traps live here — both are common failure modes; do not repeat them:

Trap A — the printed selection numbers are NOT our mdb_code

The bottom-row numbers (10, 11, 12 … 20, 21 … 50, 51) are the customer’s front-panel selection labels. In these files they typically encode <tray><position> with a 1-based tray digit (tray 1 → 10–16, tray 2 → 20–24, tray 5 → 50–53).

Our mdb_code is completely different: it is per physical coil, 0-based, tray_index*100 + column_index (§3). You must derive it from the coil’s physical position, and ignore the printed selection number as an address.

• Tray order top-to-bottom gives the 0-based tray index (top band = tray 0).
• Coil position left-to-right within the band gives the 0-based column index.
• So the first tray’s coils are 0000, 0001, 0002 …, the second tray’s are 0100, 0101 …, the fifth tray’s are 0400, 0401 … — regardless of whether the sheet labels them 10-16, 20-24, 50-53.

❌ Wrong: mdb_code: 10, 11, …, 50, 51 (copying the labels). ✅ Correct is mdb_code: 0, 1, 2 … 100, 101 … 400, 401 (physical coils, 0-based).

Trap B — one selection can span several coils (spiral width) → couple them

A “double spiral” / “triple spiral” selection dispenses one product from a slot that occupies 2 or 3 adjacent coils. Each of those coils is a separate MDB address, but they turn together, driven as one via a motor coupling. You detect the span from:

1. Merged cells on the selection-number cell (a selection merged across 2 columns = a double, across 3 = a triple). Most reliable.
2. The repeated capacity cells — the same count printed over 2 or 3 coils in a row.
3. The notes text — phrases like “8 count double spiral“, “4 count triple spiral“, “single 14 count spiral” state the width (and the count = capacity).

For each selection, produce exactly one product_map, placed at its LEFTMOST coil, and add a motor coupling when the span ≥ 2:

The coupled coil(s) do not get their own product_map row — they are driven by the main. capacity/restock_count = the selection’s single printed count (a double-spiral “8” means capacity 8 for the slot, not 16).

Algorithm (implement in code — do not eyeball)

This mapping is error-prone by eye. Write a script (openpyxl for .xlsx) that:

1. Restrict to the planogram grid — ignore any “Notes” column and everything right of it; its illustrative numbers otherwise get mistaken for coils.
2. Find each tray band (a selection row = an integer row directly beneath a capacity row; the name row is directly above). Sort bands top→bottom = tray index 0,1,2,….
3. Walk each band’s selection cells left→right, maintaining a col_cursor starting at 0. For each selection: read its span (merged width, else gap to next selection); main_col = col_cursor, right_col = col_cursor + span − 1; emit the product_map at mdb = actuator*1000 + tray*100 + main_col; if span ≥ 2, emit a coupling main → actuator*1000 + tray*100 + right_col; then col_cursor += span.
4. Cross-check: after processing a band, col_cursor (total coils) should match the number of capacity cells in that band. If not, your span detection is wrong — stop and report.

Reference implementation (openpyxl) — adapt, then run

Use this as the core of your script. It reads merged-cell spans to expand each selection into coils and emit couplings. Adapt the band/notes detection to the actual sheet — do not assume this exact row layout.

import json, os, sys
from openpyxl import load_workbook

ACTUATOR = 0  # single-actuator machine; bump only for multi-actuator hardware

path = sys.argv[1]
ws = load_workbook(path, data_only=True).active
max_row, max_col = ws.max_row, ws.max_column
cell = lambda r, c: ws.cell(row=r, column=c).value

# width (in columns) of the merged range that STARTS at each (row, col)
merge_width = {(r.min_row, r.min_col): r.max_col - r.min_col + 1
               for r in ws.merged_cells.ranges}

# The planogram grid sits LEFT of the "Notes" column. Everything from Notes rightward is
# free-text / illustrations whose stray numbers must not be mistaken for coils.
notes_col = next((c for c in range(1, max_col + 1)
                  for r in range(1, min(6, max_row) + 1)
                  if isinstance(cell(r, c), str) and cell(r, c).strip().lower() == "notes"),
                 None)
GRID_MAX_COL = (notes_col - 1) if notes_col else max_col

def int_cells(r):  # {col: int} for one row, within the grid
    return {c: cell(r, c) for c in range(1, GRID_MAX_COL + 1) if isinstance(cell(r, c), int)}

# A tray band = a selection row (ints) directly under a capacity row (ints); name row above.
int_rows = [r for r in range(1, max_row + 1) if len(int_cells(r)) >= 3]
bands = sorted((r - 2, r - 1, r) for r in int_rows if (r - 1) in int_rows)  # (name,cap,sel)

product_maps, couplings = [], []
for tray, (name_row, cap_row, sel_row) in enumerate(bands):   # top→bottom = tray 0,1,2,…
    sel_cols = sorted(int_cells(sel_row))
    caps = int_cells(cap_row)
    col = 0                                                    # 0-based coil cursor
    for i, scol in enumerate(sel_cols):
        span = merge_width.get((sel_row, scol)) or (
            (sel_cols[i + 1] - scol) if i + 1 < len(sel_cols)
            else max(caps) - scol + 1)                         # last selection → to band end
        name = next((cell(name_row, cc).strip() for cc in range(scol, scol + span)
                     if isinstance(cell(name_row, cc), str) and cell(name_row, cc).strip()),
                    None)
        capv = int(caps.get(scol, 1))                          # per-selection count (NOT ×span)
        mdb  = ACTUATOR * 1000 + tray * 100 + col              # product_map at LEFT coil
        product_maps.append({"product_name": name, "mdb_code": mdb, "capacity": capv,
                             "restock_count": capv, "low_stock_alert": 0,
                             "is_stackable_item": False})
        if span >= 2:                                          # couple left → right END coil
            couplings.append({"main_mdb_item_number": mdb,
                              "coupled_mdb_item_number": ACTUATOR * 1000 + tray * 100 + col + span - 1})
        col += span
    # cross-check: coils walked must equal the number of capacity cells in the band
    assert col == len(caps), f"tray {tray}: span mismatch ({col} vs {len(caps)} cells)"

# validation
assert len({p["mdb_code"] for p in product_maps}) == len(product_maps), "duplicate mdb_code"
for p in product_maps:
    assert p["product_name"], p
    assert p["restock_count"] <= p["capacity"] and p["mdb_code"] != 9999, p

out = os.path.dirname(os.path.abspath(path)) or "."
json.dump({"product_maps": product_maps}, open(f"{out}/product_maps.json", "w"), indent=2)
json.dump({"motor_couplings": couplings}, open(f"{out}/motor_couplings.json", "w"), indent=2)
# (also render product_maps.csv per §6 — canonical headers, Coupled MDB Code column)
print(f"{len(bands)} trays, {len(product_maps)} maps, {len(couplings)} couplings")

Reading the customer’s selection labels straight into mdb_code, or skipping the span >= 2 couplings, are exactly the two failures this replaces.

Worked result — 5-tray example planogram (the shape that was mis-converted)

5 trays × 10 coils. The customer’s selection labels 10-16 / 20-24 / 30-33 / 40-44 / 50-53 become coil codes 0000-0009 / 0100-0109 / 0200-0209 / 0300-0309 / 0400-0409. Example rows:

• Item A (double, cap 8): mdb 0000 + coupling 0000→0001; mdb 0002+0002→0003; mdb 0004+0004→0005.
• Item E (triple, cap 4): mdb 0200 + coupling 0200→0202; 0203+0203→0205; 0206+0206→0208.
• Item G (triple, cap 4): mdb 0400 + coupling 0400→0402; 0403+0403→0405; 0406+0406→0408.
• Item B / Item C (single): mdb 0006, 0007, 0008, 0009 etc., no coupling.

4. Column mapping from the spreadsheet (tabular inputs)

If the input is a visual planogram grid, use §3.5 instead of / in addition to this section — §3.5 governs how selection numbers become coil mdb_codes and couplings.

The canonical CSV the app exports/imports has these headers, in this exact order. Only a subset feeds the JSON — the rest are informational and ignored by import:

The canonical CSV the app exports/imports has these headers, in this exact order. Only a subset feeds the JSON — the rest are informational and ignored by import:

Handling arbitrary / non-canonical spreadsheets

Real user files won’t use these exact headers. Map by meaning, not exact string:

• product_name ← “Product”, “Item”, “Item Name”, “Description”, “SKU Name”, “产品名称”
• mdb_code ← “MDB”, “MDB Code”, “Selection”, “Slot”, “Coil”, “Motor”, “Position”, “Channel”, “选位/货道” (or separate Actuator/Tray/Column columns → compose per §3)
• capacity ← “Cap”, “Capacity”, “Max”, “Max Qty”, “Column Capacity”, “容量”
• restock_count ← “Restock”, “Fill”, “Fill To”, “Par”, “Loaded”, “Qty”, “Stock to Load”, “补货数量”
• low_stock_alert ← “Low Stock Alert”, “Low Stock”, “Reorder Point”, “Alert Level”, “Min”, “Threshold”
• is_stackable_item ← “Is Stackable Item”, “Stackable”, “Stack”

If you cannot confidently map a required field (product_name, mdb_code), do not guess — stop and ask which column is which. A wrong mdb_code maps products to the wrong physical slots.

Value normalisation

• Strings: trim whitespace (especially product_name).
• Booleans (is_stackable_item): TRUE/YES/Y/1 (case-insensitive) → true; everything else (blank, NO, FALSE, 0, -) → false.
• Integers: parse to whole numbers, strip thousands separators. Blank numeric cells fall back to defaults (capacity/restock_count → 1, low_stock_alert → 0). mdb_code blank → invalid (§5). Never emit null.
• Excel quirks: floats like 10.0 → cast to int; codes stored as text with leading zeros ("0003") → numeric value 3 in JSON (§3).
• Empty rows: skip fully blank / trailing empty lines silently.

5. Validation rules you must enforce

The server rejects the whole import (all-or-nothing, transactional) if any hard rule fails. Enforce them yourself and report problems per offending row instead of emitting files you know will be rejected.

Hard rules (server-enforced):

1. product_name non-empty after trimming. → “The product name cannot be empty”
2. restock_count ≤ capacity for every row. → “Restock count must be smaller or equal to the capacity [{mdb_code}]”
3. mdb_code unique across all rows. → “There are duplicate mdb_code”
4. product_maps non-empty. → “product maps is empty”

Soft rules (enforced by the app’s CSV UI — honour them):

1. mdb_code must not be 9999 — reserved sentinel value. Reject/flag.
2. mdb_code must be a non-negative integer (≥ 0). 0 is valid — it is the first coil 0000 (actuator 0 / tray 0 / column 0) and is expected for a visual planogram’s first slot (§3.5). The caveat is only for tabular inputs, where a 0 that came from a blank/unparseable selection cell (rather than a computed coil index) is almost certainly an error — in that case ask, don’t silently emit 0.
3. product_name must not contain comma ,, straight double-quote ", or curly quotes “ ” — they break the CSV round-trip and are rejected by the UI. Flag such names for the user to fix; don’t strip them yourself.

Advisory checks (warn, don’t block):

• restock_count, capacity should be ≥ 0.
• low_stock_alert above capacity never fires meaningfully — warn.
• mdb_code decodes to a plausible actuator/tray/column (§3).

If any hard/reserved rule can’t be satisfied from the data, surface the specific rows rather than emitting invalid files.

6. The CSV output (product_maps.csv)

Emit the canonical 13-column CSV (plus the optional 14th Coupled MDB Code column when couplings exist) so it uploads cleanly through the admin UI. Header row, exact strings, exact order:

Product Name,Manufacturer,Manufacturer Code,Supplier,Supplier Code,MDB Code,Current Stock,Cap,Restock,Average Daily Consumption,Low Stock Alert,Trackable Item Name,Is Stackable Item[,Coupled MDB Code]

Filling each column:

• Product Name, MDB Code, Cap, Restock, Low Stock Alert — from your mapped values.
• Is Stackable Item — write YES / NO (the UI reads YES/TRUE as true).
• Current Stock — you may set it equal to Restock (import ignores it anyway).
• Manufacturer, Manufacturer Code, Supplier, Supplier Code, Average Daily Consumption, Trackable Item Name — leave blank unless the source provides them. They are required as headers but ignored on import.
• Coupled MDB Code — include this column only if couplings exist (§8); blank/0 where a row has no coupling.

CSV formatting notes:

• The required headers must match exactly and in order, or the UI rejects the file.
• Because product names may not contain commas or quotes (rule 7), rows need no quoting — but keep every row to the same column count as the header.
• MDB Code in the CSV may be written as the plain integer (3, 1210). You may keep a zero-padded form for human readability, but plain integers are safest.

7. Worked example

Input spreadsheet (messy, non-canonical):

Reasoning: map Item→product_name, Slot→mdb_code, Max→capacity, Fill To→restock_count, Reorder→low_stock_alert, Stackable→is_stackable_item. "yes"→true, blank→false. 0003→3 in JSON. All restock ≤ cap ✅, codes unique ✅, no 9999 ✅, names clean ✅.

product_maps.json:

{
  "product_maps": [
    { "product_name": "Nitrile Gloves M", "mdb_code": 1210, "capacity": 10, "restock_count": 10, "low_stock_alert": 2, "is_stackable_item": false },
    { "product_name": "Safety Glasses",   "mdb_code": 1211, "capacity": 6,  "restock_count": 6,  "low_stock_alert": 1, "is_stackable_item": true  },
    { "product_name": "Ear Plugs (pair)", "mdb_code": 3,    "capacity": 20, "restock_count": 15, "low_stock_alert": 4, "is_stackable_item": false }
  ]
}

product_maps.csv:

Product Name,Manufacturer,Manufacturer Code,Supplier,Supplier Code,MDB Code,Current Stock,Cap,Restock,Average Daily Consumption,Low Stock Alert,Trackable Item Name,Is Stackable Item
Nitrile Gloves M,,,,,1210,10,10,10,,2,,NO
Safety Glasses,,,,,1211,6,6,6,,1,,YES
Ear Plugs (pair),,,,,0003,15,20,15,,4,,NO

8. Motor couplings

A coupling links two coils so a wide slot dispenses from a paired MDB code. Couplings come from two possible sources — use whichever the input provides:

1. A visual spiral planogram (§3.5) — double/triple spirals imply couplings you derive from the spiral width. This is the common case for snack/spiral machines and is easy to miss entirely. double → main c → coupled c+1; triple → main c → coupled c+2.
2. A tabular file with a Coupled MDB Code column — emit a coupling for each row where that value is present and > 0.

Output shape (both a CSV column and a JSON file):

• In the CSV, the 14th column Coupled MDB Code carries the coupled coil’s code on the main slot’s row; blank where a row has no coupling.
• For the direct-API path, also emit motor_couplings.json (a separate payload POSTed to a different endpoint after the product-map import succeeds):

{
  "motor_couplings": [
    { "main_mdb_item_number": 0, "coupled_mdb_item_number": 1 },
    { "main_mdb_item_number": 200, "coupled_mdb_item_number": 202 }
  ]
}

Rules:

• main_mdb_item_number = the slot’s own (leftmost-coil) mdb_code; coupled_mdb_item_number = the paired coil’s code (right coil for a double, right end coil for a triple).
• The main coil has a product_map row; the coupled coil(s) do not (§3.5).
• This payload is authoritative/replacing: it upserts the listed couplings and removes any others on the machine. If the input has no couplings at all, omit couplings entirely (existing couplings on the machine are left untouched — don’t send an empty replace unless you intend to wipe them).
• Both members of a coupling must be valid coil codes; the coupled code must not also appear as a product_map mdb_code. Warn on any violation.

9. Reference: where this maps in the system (context, not required to convert)

• Import endpoint: POST /api/v2/vending-machines/{vending_machine_uuid}/product-maps/import with body { "product_maps": [ ... ] }.
• Couplings endpoint: POST /api/v2/vending-machines/{vending_machine_uuid}/update-motor-couplings with body { "motor_couplings": [ ... ] }.
• Backend row DTO: App\ApiResponse\V1ImportProductMapInfo.
• Import logic & validation: App\Http\Service\ProductMapService::importProductMaps() (marks old maps current=0, inserts new, auto-creates missing products by name, syncs stock to the machine via MDB).
• Controller: App\Http\Controllers\AdminVendingMachineServiceController::adminVendingMachineServiceImportProductMaps().
• CSV parsing / upload UI: frontend/src/vending_machine_page/product_map_page/upload_product_map_csv.tsx (header spec) and upload_product_map_csv_modal.tsx (CSV→JSON + couplings).

Per the repo’s API-change workflow, this contract originates in vendingontrack/api; do not hand-edit gen/ or ApiResponse/ to change fields.

10. Output contract — checklist

Before returning, confirm:

• Two files emitted for each machine: product_maps.json and product_maps.csv (plus motor_couplings.json if couplings exist), all describing the same planogram.
• JSON top-level key product_maps (array); each row has product_name (non-empty), mdb_code (unique non-negative integer — 0 is valid — not 9999), integers as numbers, is_stackable_item as boolean.
• mdb_code in JSON is a plain integer (no leading zeros); if the source used Actuator/Tray/Column, it was composed correctly per §3.
• If the input was a visual planogram (§3.5): mdb_codes are derived from physical coil position (0-based tray*100+column), NOT copied from the printed selection numbers. First tray’s coils are 0,1,2…, second tray 100,101….
• Spiral spans handled: every double/triple selection produced one product_map at the left coil plus a motor coupling (double c→c+1, triple c→c+2); coupled coils have no product_map row; capacity is the single printed count (not multiplied).
• Per-band coil count equals the number of capacity cells (span detection cross-check).
• restock_count ≤ capacity on every row.
• Array/CSV is the complete planogram (import replaces the whole machine).
• Product names preserved verbatim (trimmed only); no forbidden characters , " “ ”.
• CSV has the exact required headers in order; Is Stackable Item as YES/NO; ignored columns left blank.
• Any unsatisfiable hard/reserved rule reported as specific rows, not emitted as invalid files.
• Machine UUID kept out of the files (it’s a separate path parameter).