Prompt Details
Model
(claude-5-5-sonnet)
Token size
662
Example input
[WHAT THE DATA IS]: Shopify-style order export from a small US online store (sample data)
[MESSY CSV DATA]: Order ID,Order Date,Customer Email,Product, Qty ,Unit Price,Status,Ship Country #1001,2026-10-03,JANE.DOE@EXAMPLE.COM,Bamboo Toothbrush 4-pack,2,$12.50,Paid,US #1002,10/04/2026,mike@example.com , Standing Desk Frame ,1,"$1,299.00",paid,United States #1003,Oct 5 2026,li.wei@example.com,Linen Tote Bag,3,$18,SHIPPED,usa #1002,10/04/2026,mike@example.com,Standing Desk Frame,1,"$1,299.00",paid,United States ,,,,,,, #1004,2026-10-06,ana@example,Soy Candle (Fig),two,$9.99,refunded,Canada #1005,06/10/2026,sam@example.com,Bamboo Toothbrush 4-pack,1,12.5,Pending,CA #1006,2026-10-07,,Linen Tote Bag,1,$18.00,Paid,U.S.
[CLEANING RULES]: ISO dates (slash dates are MM/DD/YYYY), prices as plain decimals, lowercase emails and status, 2-letter country codes, remove exact duplicate orders (keep first), add line_total = qty x unit_price, flag invalid or missing emails
[JSON OUTPUT SHAPE]: array of order objects, one per order
[SCRIPT TYPE]: plain Python 3, standard library only
Example output
Inputs: Shopify-style order export, US store (sample data) · ISO dates, plain prices, 2-letter countries, dedupe orders, add line_total · array of order objects · plain Python 3 stdlib
```csv
Order ID,Order Date,Customer Email,Product, Qty ,Unit Price,Status,Ship Country
#1001,2026-10-03,JANE.DOE@EXAMPLE.COM,Bamboo Toothbrush 4-pack,2,$12.50,Paid,US
#1002,10/04/2026,mike@example.com , Standing Desk Frame ,1,"$1,299.00",paid,United States
#1003,Oct 5 2026,li.wei@example.com,Linen Tote Bag,3,$18,SHIPPED,usa
#1002,10/04/2026,mike@example.com,Standing Desk Frame,1,"$1,299.00",paid,United States
,,,,,,,
#1004,2026-10-06,ana@example,Soy Candle (Fig),two,$9.99,refunded,Canada
#1005,06/10/2026,sam@example.com,Bamboo Toothbrush 4-pack,1,12.5,Pending,CA
#1006,2026-10-07,,Linen Tote Bag,1,$18.00,Paid,U.S.
```
## 1. Detected problems
| # | Problem | Where (column / original line) | Fix applied | Severity |
|---|---|---|---|---|
| 1 | Three different date formats | `Order Date`, lines 3, 5, 8 (`10/04/2026`), line 4 (`Oct 5 2026`) | All converted to ISO `YYYY-MM-DD`; slash dates read as MM/DD/YYYY | High |
| 2 | Ambiguous slash date | `Order Date`, line 8 (`06/10/2026`) | Read as 2026-06-10 per your MM/DD rule, but flagged: order IDs are sequential, so 2026-10-06 is more likely. Confirm in Shopify. Line 3 (`10/04`) is flagged too but fits the ID sequence | High |
| 3 | Exact duplicate order | `#1002`, line 5 (copy of line 3) | Removed, first copy kept | High |
| 4 | Currency symbols and thousands separators | `Unit Price`, every line (`$12.50`, `"$1,299.00"`, `12.5`) | Plain decimal with 2 places (`1299.00`) | Med |
| 5 | Quantity written as a word | `Qty`, line 7 (`two`) | Converted to `2`, flagged | Med |
| 6 | Invalid email (no top-level domain) | `Customer Email`, line 7 (`ana@example`) | Kept as-is and flagged. Never guessed | Med |
| 7 | Missing email | `Customer Email`, line 9 | Left empty in CSV, `null` in JSON, flagged | Med |
| 8 | Five spellings of two countries | `Ship Country` (`US`, `United States`, `usa`, `U.S.`, `Canada`, `CA`) | Mapped to `US` / `CA` | Med |
| 9 | Blank row | line 6 | Removed | Low |
| 10 | Stray whitespace and double spaces | `Product` and `Customer Email`, line 3; header ` Qty ` | Trimmed and collapsed | Low |
| 11 | Mixed status casing, `#` prefix on IDs | `Status`, `Order ID` | Lowercase status, numeric-string ID (`1001`) | Low |
Biggest risk: the ambiguous date on order 1005. If you load it as June, it falls out of every October report.
## 2. Detection & assumptions
- Delimiter: comma. Quoted fields only where a price contains a comma.
- Encoding: UTF-8, no BOM (the script reads with `utf-8-sig` anyway, so a BOM won't break it).
- Header row: line 1. Header ` Qty ` had padding, now `qty`.
- Date order: MM/DD/YYYY for slash dates (your rule; US store). `Oct 5 2026` is unambiguous.
- Decimal format: `.` decimal, `,` thousands, `$` prefix.
- Duplicate key: `order_id`. A repeated ID with identical content is treated as an export duplicate.
- Assumption: one product per row (no multi-line orders), so `line_total` = order total here.
## 3. Cleaned CSV
```csv
order_id,order_date,customer_email,product,qty,unit_price,line_total,status,ship_country
1001,2026-10-03,jane.doe@example.com,Bamboo Toothbrush 4-pack,2,12.50,25.00,paid,US
1002,2026-10-04,mike@example.com,Standing Desk Frame,1,1299.00,1299.00,paid,US
1003,2026-10-05,li.wei@example.com,Linen Tote Bag,3,18.00,54.00,shipped,US
1004,2026-10-06,ana@example,Soy Candle (Fig),2,9.99,19.98,refunded,CA
1005,2026-06-10,sam@example.com,Bamboo Toothbrush 4-pack,1,12.50,12.50,pending,CA
1006,2026-10-07,,Linen Tote Bag,1,18.00,18.00,paid,US
```
## 4. JSON output
```json
[
{
"order_id": "1001",
"order_date": "2026-10-03",
"customer_email": "jane.doe@example.com",
"product": "Bamboo Toothbrush 4-pack",
"qty": 2,
"unit_price": 12.5,
"line_total": 25.0,
"status": "paid",
"ship_country": "US"
},
{
"order_id": "1002",
"order_date": "2026-10-04",
"customer_email": "mike@example.com",
"product": "Standing Desk Frame",
"qty": 1,
"unit_price": 1299.0,
"line_total": 1299.0,
"status": "paid",
"ship_country": "US"
},
{
"order_id": "1003",
"order_date": "2026-10-05",
"customer_email": "li.wei@example.com",
"product": "Linen Tote Bag",
"qty": 3,
"unit_price": 18.0,
"line_total": 54.0,
"status": "shipped",
"ship_country": "US"
},
{
"order_id": "1004",
"order_date": "2026-10-06",
"customer_email": "ana@example",
"product": "Soy Candle (Fig)",
"qty": 2,
"unit_price": 9.99,
"line_total": 19.98,
"status": "refunded",
"ship_country": "CA"
},
{
"order_id": "1005",
"order_date": "2026-06-10",
"customer_email": "sam@example.com",
"product": "Bamboo Toothbrush 4-pack",
"qty": 1,
"unit_price": 12.5,
"line_total": 12.5,
"status": "pending",
"ship_country": "CA"
},
{
"order_id": "1006",
"order_date": "2026-10-07",
"customer_email": null,
"product": "Linen Tote Bag",
"qty": 1,
"unit_price": 18.0,
"line_total": 18.0,
"status": "paid",
"ship_country": "US"
}
]
```
## 5. Reusable script
```python
#!/usr/bin/env python3
"""Clean a Shopify-style order export -> clean.csv + clean.json (stdlib only)."""
import csv, json, re
from datetime import datetime
IN, OUT_CSV, OUT_JSON = "input.csv", "clean.csv", "clean.json"
COUNTRY = {"us": "US", "usa": "US", "u.s.": "US", "united states": "US",
"ca": "CA", "canada": "CA"}
WORDS = {"one": 1, "two": 2, "three": 3, "four": 4, "five": 5}
EMAIL_RE = re.compile(r"^[^@\s]+@[^@\s]+\.[a-z]{2,}$")
FIELDS = ["order_id", "order_date", "customer_email", "product", "qty",
"unit_price", "line_total", "status", "ship_country"]
def snake(h):
return re.sub(r"[^a-z0-9]+", "_", h.strip().lower()).strip("_")
def is_ambiguous(s):
m = re.match(r"^(\d{1,2})/(\d{1,2})/\d{4}$", s.strip())
return bool(m) and int(m.group(1)) <= 12 and int(m.group(2)) <= 12
def parse_date(s):
s = s.strip()
# Assumption: slash dates are MM/DD/YYYY (US store)
for fmt in ("%Y-%m-%d", "%m/%d/%Y", "%b %d %Y"):
try:
return datetime.strptime(s, fmt).date().isoformat()
except ValueError:
pass
return ""
def parse_money(s):
s = s.strip().replace("$", "").replace(",", "")
try:
return round(float(s), 2)
except ValueError:
return None
def parse_qty(s):
s = s.strip().lower()
if s.isdigit():
return int(s), False
return WORDS.get(s), s in WORDS
rows, flags, seen = [], [], set()
read = dupes = blank = 0
with open(IN, newline="", encoding="utf-8-sig") as f:
reader = csv.reader(f)
header = [snake(h) for h in next(reader)]
for line_no, raw in enumerate(reader, start=2):
read += 1
if not any(c.strip() for c in raw):
blank += 1
flags.append(f"line {line_no}: blank row removed")
continue
r = dict(zip(header, (c.strip() for c in raw)))
oid = r["order_id"].lstrip("#")
if oid in seen: # exact duplicate order id -> keep first
dupes += 1
flags.append(f"line {line_no}: duplicate order {oid} removed")
continue
seen.add(oid)
email = r["customer_email"].lower()
if email and not EMAIL_RE.match(email):
flags.append(f"line {line_no}: invalid email '{email}' kept, flagged")
if not email:
flags.append(f"line {line_no}: missing email")
if is_ambiguous(r["order_date"]):
flags.append(f"line {line_no}: ambiguous date '{r['order_date']}' read as MM/DD")
qty, from_word = parse_qty(r["qty"])
if from_word:
flags.append(f"line {line_no}: qty '{r['qty']}' converted to {qty}")
price = parse_money(r["unit_price"])
rows.append({
"order_id": oid,
"order_date": parse_date(r["order_date"]),
"customer_email": email or None,
"product": re.sub(r"\s+", " ", r["product"]),
"qty": qty,
"unit_price": price,
"line_total": round(qty * price, 2) if qty is not None and price is not None else None,
"status": r["status"].lower(),
"ship_country": COUNTRY.get(r["ship_country"].lower(), r["ship_country"].upper()),
})
with open(OUT_CSV, "w", newline="", encoding="utf-8") as f:
w = csv.DictWriter(f, fieldnames=FIELDS, lineterminator="\n")
w.writeheader()
for r in rows:
w.writerow({k: ("" if v is None else (f"{v:.2f}" if k in ("unit_price", "line_total") else v)) for k, v in r.items()})
with open(OUT_JSON, "w", encoding="utf-8") as f:
json.dump(rows, f, indent=2)
f.write("\n")
print(f"read={read} kept={len(rows)} duplicates_removed={dupes} blank_removed={blank} flags={len(flags)}")
for x in flags:
print(" -", x)
```
## 6. Run it & verify
Save the export as `input.csv` in the same folder, then run: `python3 clean.py`
Expected report: `read=8 kept=6 duplicates_removed=1 blank_removed=1 flags=7`
- [ ] Row count: 8 data lines read, 6 kept. 8 − 1 duplicate − 1 blank = 6
- [ ] Revenue check: sum of `line_total` = 1428.48 (25.00 + 1299.00 + 54.00 + 19.98 + 12.50 + 18.00)
- [ ] Spot check: order 1002 shows `1299.00`, not `1.0` or `1,299.00`
- [ ] `python3 -m json.tool clean.json` prints without errors. Then fix order 1005's date and ana@example in Shopify before your next export
By purchasing this prompt, you agree to our terms of service
CLAUDE-5-5-SONNET
6 outputs from 1 messy CSV: problems table, notes, clean CSV, JSON, Python script, checks. Paste a messy export (orders, CRM, surveys, statements) plus your rules. Get line-numbered issues the prompt can spot, cleaned CSV, JSON in your shape, and a reusable Python script (stdlib or pandas). Helps with dates, currency, EU numbers, BOMs, semicolons, duplicates, phones, yes/no, N/A. Review before import. Runs in any Claude chat.
...more
Added 1 week ago
