# Skill: excel_to_counters **Convert raw store-list Excel files → Counter Management upload CSV** Callable two ways: - **Interactive** (`/excel_to_counters` in Claude Code) — full human-in-loop pipeline - **API mode** (`claude -p --system "$(cat excel_to_counters.md)"`) — JSON-in / JSON-out for PWA proxies --- ## INTERACTIVE PIPELINE (when invoked as a slash command) ### Stage 0 — Get File If `$ARGUMENTS` contains a file path, use it. Otherwise ask: ``` 📂 Drop the Excel file path (or paste from VPS): ``` Validate the path exists. If not found, say so and stop. ### Stage 1 — Parse Excel Run: ```bash python3 /var/www/360lm/.claude/helpers/excel_parser.py "$FILE_PATH" ``` Read the JSON output. If `error` key is present, report it and stop. Pick the primary data sheet: the sheet with the most rows (`row_count`). Skip sheets named `screenshots`, `summary`, `chart`, `dashboard`. Show the user a compact summary: ``` 📊 File: Sheet: ( rows) Header detected at row Columns mapped: name=, address=, city=, pincode=, phone=, gstin= Rows needing AI extraction: Filename hints: brand=, state= ``` ### Stage 2 — Confirm Batch Metadata Ask the user to confirm/correct these fields that apply to the **entire batch**: ``` ✏️ Confirm batch metadata (press Enter to accept detected value, or type a new one): Brand code []: State []: Territory [blank]: Counter type [dealer]: e.g. dealer / sub_dealer / distributor / modern_trade / standalone / other Channel [general_trade]: e.g. general_trade / modern_trade / institution / e_commerce / other Distributor [blank]: Distributor company name (optional) ``` Store the confirmed values as batch defaults. Every output row will inherit these unless overridden per-row. ### Stage 3 — Normalize Rows (AI extraction + Google MCP geocoding) Process rows in batches of 15. For each batch: 1. **Apply normalization rules** (see NORMALIZATION RULES section below) using your own language intelligence. 2. **Structural hints**: For rows with missing city/pincode, run: ```bash python3 /var/www/360lm/.claude/helpers/pincode_lookup.py "" ``` This returns any 6-digit pincode and state abbreviation found in the text. 3. **Geocoding for low-confidence city**: When city extraction confidence is low AND you have a partial address, use the Google Maps MCP proxy (already configured at VPS, free via server-side key): - The MCP tool `mcp__google-maps__geocode` can resolve `"
, , India"` to a structured city/pincode. - Use this only when regex + AI extraction both fail. It costs an API call. 4. **Collect ambiguous rows** (see AMBIGUITY HANDLING below). After each batch, show a one-line progress: `✅ Batch 1/N done — clean, flagged` > **Note on outbound internet from VPS**: Public APIs like api.postalpincode.in are blocked by the VPS firewall. All external lookups must go through the VPS MCP proxy (Google Maps) or be pure AI inference. ### Stage 4 — Human-in-Loop for Ambiguous Rows For each flagged row, show: ``` ⚠️ Row — My best guess: display_name : proprietor_name : address_line1 : city : (confidence: ) pincode : Accept? [Y] / Edit [E] / Skip [S]: ``` - **Y**: accept the guess as-is - **E**: user types corrected values in the shown fields - **S**: row goes to `needs_review` CSV ### Stage 5 — Write Output Build two files: **`/var/www/360lm/uploads/counters_.csv`** — ready to upload: ``` display_name,legal_name,proprietor_name,address_line1,address_line2,city,state,pincode,gstin,pan,fssai,drug_lic,counter_type,channel,customer_group,territory,distributor_name,notes,brand_codes,primary_contact_name,primary_contact_phone ``` (All mandatory fields filled; batch defaults applied.) **`/var/www/360lm/uploads/counters_needs_review_.csv`** — rows skipped or still uncertain: Same schema, plus two extra columns at the end: `_reason` (why flagged), `_raw_original` (original cell content). Show final summary: ``` ✅ Done! Ready to upload : rows → /var/www/360lm/uploads/counters_.csv Needs review : rows → /var/www/360lm/uploads/counters_needs_review_.csv Upload the ready CSV at Counter Management → ⬆ Upload CSV. ``` --- ## NORMALIZATION RULES *(These rules apply in BOTH interactive and API mode.)* ### R1 — Header Row Detection Skip initial rows that are: entirely empty, pure numeric, or contain only logo/banner text. The first row with 3+ non-empty cells AND 2+ string cells is the header. ### R2 — C/o Pattern (Name + Contact splitting) When a name cell matches `, C/o ` or ` C/O `: - `display_name` = the entity after "C/o" (store/outlet) - `proprietor_name` = the entity before "C/o" (person) - Flag as `_has_co_pattern = true` for human confirmation if confidence < high. When the cell is `\nC/o ` (reverse order): - `display_name` = the entity before `\n` - `proprietor_name` = entity after "C/o" ### R3 — Name + Address Merged (`\n`-delimited) When `name` column contains a newline and `address` column is blank: - First line → name candidate - Remaining lines → address candidate - Apply R2 on the name candidate after splitting. ### R4 — City Extraction from Address When city column is blank, use Claude's own language intelligence on the address text (priority order): 1. Pattern: `, - ` or `-` (common in Indian addresses) 2. Pattern: `, [state-abbrev]` — e.g. "JOGANI PLAZA, SIKAR (RAJ.) 332001" → city=Sikar 3. Pattern: `Near/Opp , ` or ` Road` 4. Pattern: Look for a proper noun immediately preceding or following the 6-digit pincode 5. If Claude cannot determine city with medium confidence: use Google Maps MCP geocode tool (`mcp__google-maps__geocode`) with the full address string — it returns a `city` field. Mark as `city_from_geocode`. 6. If geocode also fails: mark `_city_confidence = low`, leave blank for human review. ### R5 — Pincode Extraction from Address When pincode column is blank, extract using: ```regex \b([1-9][0-9]{5})\b ``` First 6-digit number that is NOT a phone number (phone numbers are 10 digits). If multiple 6-digit numbers found, prefer the one adjacent to a state abbreviation. ### R6 — Phone Normalisation - Strip non-digit characters except `/` and `,` (which indicate multiple numbers) - If multiple numbers: take the first 10-digit number as `primary_contact_phone`; put the rest in `notes` - Reformat to plain 10 digits (drop leading 0, drop +91) ### R7 — GSTIN Validation GSTIN must match `[0-9]{2}[A-Z]{5}[0-9]{4}[A-Z]{1}[1-9A-Z]{1}Z[0-9A-Z]{1}`. If present but doesn't match: move to `notes` with prefix `[GSTIN?]`. ### R8 — Duplicate Detection Within the same batch, flag rows where `display_name + city` duplicates an earlier row. Add `[DUPLICATE?]` to notes. ### R9 — State from Batch Metadata If state is still blank after all extraction, use the batch-level state confirmed in Stage 2. ### R10 — Minimum Viable Row A row is "ready to upload" only when ALL of these are non-blank: - `display_name` - `city` - `state` - `pincode` Rows that still fail after all rules → `needs_review` file. --- ## AMBIGUITY HANDLING (when to flag for human confirmation) Flag a row for human-in-loop when ANY of these is true: - `_has_co_pattern = true` AND extracted name is not clearly a business name (single word or all-caps person name) - `_city_confidence = low` - `display_name` is blank after applying R2 + R3 - Pincode is 6 digits but India Post returns "not found" - Name cell is longer than 80 chars (likely merged name+address+notes) - Row is a continuation row (`_is_continuation = true`) --- ## API MODE — JSON IN / JSON OUT *(Used by PWA proxies calling `claude -p`. No human-in-loop. Returns JSON.)* ### Input format — RAW ROWS (preferred, column auto-detection) Send SheetJS rows exactly as parsed — original column header names, no pre-mapping. You must detect which column holds name, address, city, pincode, phone, etc. ```json { "mode": "api", "batch_metadata": { "brand_codes": "HP", "state": "Punjab", "territory": "North", "counter_type": "dealer", "channel": "general_trade", "distributor_name": "" }, "raw_headers": ["S.No.", "Store Name ", "Store Address", "City", "Pin code", "State", "Partner Name", " Phone #"], "raw_rows": [ { "S.No.": "1", "Store Name ": "Narang Computers", "Store Address": "56 & 57, Nehru Shopping Complex, Hall Bazaar", "City": "Amritsar", "Pin code": "143001", "State": "Punjab", "Partner Name": "Ajay Narang", " Phone #": "9914424025" } ] } ``` **Column detection rules** (apply in order): 1. Examine ALL column names in `raw_headers`. Use both the header text AND sample values to identify roles. 2. Role → regex hints (case-insensitive, strip spaces/punctuation): - `name`: store|outlet|counter|dealer|shop|business|firm|counter_name - `address`: address|addr|location|street|area|locality|store_address - `city`: city|town|district - `state`: state|province - `pincode`: pin|pincode|zip|postal - `phone`: phone|mobile|contact|cell|tel|mob - `gstin`: gst|gstin - `contact_person`: partner|proprietor|owner|person|contact_name 3. If `name` and `address` share one column (values contain `\n`): detect as `name_address_merged`. 4. If city is absent but address values contain city patterns: set `city` to `null` (extract from address per R4). 5. Return all detected mappings in `suggested_col_map`. Keys are role names, values are ORIGINAL header strings. ### Input format — PRE-MAPPED ROWS (legacy, still accepted) ```json { "mode": "api", "batch_metadata": { "brand_codes": "HP", "state": "Punjab" }, "rows": [ { "_row": 4, "raw_name": "Sanwar Mal Saini, C/o R G COMPUTERS", "raw_address": "G-01, RAMESHWAR COLONY, SIKAR", "raw_city": "", "raw_state": "", "raw_pincode": "", "raw_phone": "9414012345", "raw_gstin": "", "raw_contact": "" } ] } ``` ### Output format ```json { "mode": "api", "suggested_col_map": { "name": "Store Name ", "address": "Store Address", "city": "City", "state": "State", "pincode": "Pin code", "phone": " Phone #", "contact_person": "Partner Name" }, "results": [ { "_row": 1, "_status": "ready", "_confidence": "high", "_flags": [], "display_name": "Narang Computers", "proprietor_name": "Ajay Narang", "address_line1": "56 & 57, Nehru Shopping Complex, Hall Bazaar", "address_line2": "", "city": "Amritsar", "state": "Punjab", "pincode": "143001", "gstin": "", "counter_type": "dealer", "channel": "general_trade", "territory": "North", "brand_codes": "HP", "primary_contact_name": "Ajay Narang", "primary_contact_phone": "9914424025" } ], "summary": { "total": 1, "ready": 1, "needs_review": 0, "flagged": 0 } } ``` `_status` values: `ready` | `needs_review` | `flagged` `_confidence` values: `high` | `medium` | `low` `_flags` values (array of strings): `co_pattern_extracted` | `city_from_address` | `city_from_pincode_api` | `pincode_from_address` | `duplicate_candidate` | `gstin_invalid` `suggested_col_map`: always included (empty `{}` if input was pre-mapped). Keys = role, values = original Excel header string. For `name_address_merged` case: set both `name` and `address` to the same column name, and add `"name_address_merged": true` to `suggested_col_map`. --- ## OUTPUT SCHEMA REFERENCE (Counter Management CSV v2) | Column | Mandatory | Source | |---------------------|-----------|-------------------------------| | display_name | ✅ | R2/R3 → store name | | legal_name | — | usually same as display_name | | proprietor_name | — | R2 → person before C/o | | address_line1 | — | R3 → first address line | | address_line2 | — | R3 → second address line | | city | ✅ | R4/pincode API | | state | ✅ | batch default or R9 | | pincode | ✅ | R5 or direct column | | gstin | — | R7 | | pan | — | blank (rarely in Excel) | | fssai | — | blank | | drug_lic | — | blank | | counter_type | — | batch default | | channel | — | batch default | | customer_group | — | blank | | territory | — | batch default | | distributor_name | — | batch default | | notes | — | remarks column + overflow | | brand_codes | — | batch default (e.g. HP) | | primary_contact_name | — | contact_person column or R2 | | primary_contact_phone | — | R6 first number | --- ## DEMO RUN (to test the skill on sample files) Run this to verify the parser works on all 4 reference files: ```bash for f in \ "/var/www/360lm/uploads/Brand-HP-For Punjab Dispatch.xlsx" \ "/var/www/360lm/uploads/Brand -HP -Address for Rajasthan Dispatch.xlsx" \ "/var/www/360lm/uploads/Brand -HP-Q3'26 - T3 Store List for Visibility drive.xlsx" \ "/var/www/360lm/uploads/26 06 17 Liebherr Branding - Punjan.xlsx"; do echo "=== $f ===" python3 /var/www/360lm/.claude/helpers/excel_parser.py "$f" | python3 -c " import json,sys d=json.load(sys.stdin) h=d.get('filename_hints',{}) for sn,s in d.get('sheets',{}).items(): print(f' sheet={sn} rows={s[\"row_count\"]} header_row={s[\"header_row\"]} needs_ai={s[\"needs_ai_extraction\"]}') print(f' col_roles={s[\"col_roles\"]}') print(f' hints={h}') if s[\"rows\"]: r=s[\"rows\"][0] print(f' row1: name={r[\"raw_name\"][:40]!r} city={r[\"raw_city\"]!r} pin={r[\"raw_pincode\"]!r} co={r[\"_has_co_pattern\"]}') " done ```