mattrguerra.comHouston area
Matt Guerra
  1. 01AI & Automation
  2. 02Photography
  3. 03Design
  4. 04About
  5. 05Notes
  6. 06Contact
Back to the systems

mattrguerra/meridian-skillsmainPrivate on GitHub, published here in full

thanksio-postcard-formatter/

Thanks.io Postcard Sheet Formatter

Transforms raw 21-column XLSX order exports from Plinth, Meridian Display Co.'s order system, into the 29-column CSV format required by Thanks.io's postcard shipping import. Handles guest-shipping cases (orders shipped to a hotel or convention center on behalf of the recipient), normalizes address and contact data, and validates the result before save.

When to run

Trigger when:

  • A new .xlsx file appears in Thanksio-Post-Card-Sheet-Formatter/01 - New/ (or wherever Matt has dropped it).
  • Matt asks to "process the postcard sheet," "clean up postcard addresses," "format for Thanks.io," or any similar phrasing.

What it does

Reads the XLSX (rows 1-2 are report title and blank; row 3 is headers; row 4+ is data), applies address-selection logic to choose between shipping and billing fields, applies nine cleaning rules, runs four validation checks, and writes a UTF-8 CSV named cleaned-postcard-addresses-YYYY-MM-DD.csv into a sibling 02 - Formatted/ directory.

How to run

cd ~/.claude/skills/thanksio-postcard-formatter/scripts
python3 format.py <path-to-input.xlsx>

By default the output lands in <input parent>/../02 - Formatted/. To archive the input after a successful run:

python3 format.py <path-to-input.xlsx> --archive

This moves the input into 02 - Formatted/99 - Archive/ once the CSV is written.

To override the output directory:

python3 format.py <path-to-input.xlsx> --output-dir /some/other/path

Input columns the skill reads

The Plinth order export names its columns in Plinth's snake_case style. Of the 21 columns, the skill reads these:

Block Columns
Shipping ship_name, ship_company, ship_street, ship_street2, ship_city, ship_region, ship_postcode, ship_country
Billing bill_name, bill_company, bill_street, bill_street2, bill_city, bill_region, bill_postcode, bill_country
Shared email, phone

The remaining columns (order reference, dates, totals) are ignored.

Address-selection logic

Default: shipping address. The skill switches to the billing address fields if either of these is true:

  1. ship_name starts with Guest: (case-insensitive). This indicates the order is being shipped to someone on behalf of the recipient (e.g., a hotel reservation under a guest name).
  2. ship_company contains any of: hotel, suites, marriott, hilton, hyatt, sheraton, resort, motel, lodge, convention center, expo center, fairground, conference center, AND the billing street address (bill_street) differs from the shipping street address (ship_street).

If the billing and shipping street addresses are identical, the skill retains shipping. That case is treated as the company's own office, not a hotel-shipping scenario.

When the switch fires, Name, Company, Address, Address2, City, State, Postal Code, and Country are pulled from the bill_* columns instead of the ship_* columns. Email and Phone always come from the shared email and phone columns regardless of which address block is used.

Cleaning rules

Applied to every row:

  1. HTML entity decoding via html.unescape on all text fields (e.g., &#039; → ', &amp; → &).
  2. Name: strip Guest: prefix, strip whitespace, convert ALL CAPS to Title Case (mixed case left as-is).
  3. Company: decode entities, convert ALL CAPS to Title Case if longer than three characters (short acronyms like ABC or IBM preserved), replace literal "nan" strings with empty.
  4. Address: decode entities, collapse doubled commas to single, strip whitespace.
  5. City: decode entities, ALL CAPS to Title Case, strip trailing commas.
  6. Postal Code: strip .0 float-conversion suffix, zero-pad 4-digit numeric codes to 5 (recovers lost-leading-zero Northeast US ZIPs).
  7. Country: normalize United States / USA / us (any case) to US; non-US countries left as-is.
  8. Phone: strip non-digit characters, prepend 1 for 10-digit US numbers, output digits only (e.g., 12065551234).
  9. General nulls: replace NaN, None, and literal "nan" strings with empty across the entire output.

Validation

Logged as warnings; no rows are dropped:

  • Empty Name after cleaning.
  • Empty Address after cleaning.
  • Suspicious Company field: contains dimensions (e.g., 10x8ft) or words ONLY / frame / banner. These usually indicate the field actually holds an order note rather than a company name. Flag for manual review; do not auto-correct.
  • US row missing State or Postal Code.

All rows ship to the CSV regardless of warning count. Review the warning log before importing to Thanks.io.

Output schema

29 columns in this exact order:

ID, Name, Company, Address, Address2, City, State, Postal Code, Country, Email, Phone, Custom 1, Custom 2, Custom 3, Custom 4, Order Item QR Scans, Order Item Status, Order Item Estimated Delivery Date, Order Item Failure, Message, Order Id, Order Type, Order Size, Order Method, Sub Account ID, Created At, Giftcard Reedem, Giftcard Brand, Giftcard Amount

Defaults filled regardless of input:

Column Default
Order Type postcard
Order Size 4x6
Order Method cardbuilder
Giftcard Reedem N/A
Giftcard Brand N/A
Giftcard Amount N/A

All other unmapped columns are left blank.

The Giftcard Reedem column header is misspelled (should be Redeem). This matches Thanks.io's upstream import schema verbatim. Do not "fix" it. The import will fail if the column is renamed.

Dependencies

  • Python 3.10+
  • pandas (for read_excel)
  • openpyxl (pandas' XLSX engine)
  • Standard library: html, re, csv, datetime, pathlib, argparse, logging

Install once:

pip install pandas openpyxl

Known caveats

  • The hotel-keyword list is a fixed allowlist of 13 entries. A hotel-equivalent venue not matching any of those words will not trigger the address switch. To extend, edit HOTEL_KEYWORDS in scripts/format.py.
  • ALL-CAPS → Title Case is a heuristic. A name like JD SMITH becomes Jd Smith. Review names if source data quality is uneven.
  • The address-difference comparison is case- and whitespace-normalized but otherwise literal. Two functionally identical addresses written differently (123 Main St vs. 123 Main Street) will be treated as different.
  • No persistence or run log. To audit a prior run, retain the archived XLSX and the produced CSV side by side.
  • The skill processes one XLSX per invocation. If multiple XLSX files exist in 01 - New/, run the skill once per file.

When something goes wrong

  • KeyError on a ship_... or bill_... column: the source export's column headers have drifted from the documented 21-column structure. Compare against the input columns listed under "Input columns the skill reads" above and fix the Plinth export rather than the skill.
  • All rows produce "empty Name" or "empty Address" warnings: likely a row-skip miscount. Check that the input XLSX still has the title-row + blank-row + headers pattern (i.e., headers on row 3). If the source format changed, update skiprows=2 in format.py.
  • Pandas raises on read_excel: confirm openpyxl is installed (pip install openpyxl).
  • Import to Thanks.io fails on column names: the output column order or naming has drifted. Compare the output CSV header against the OUTPUT_COLUMNS constant in scripts/format.py. The misspelled Giftcard Reedem is intentional.