PromptBase
PromptBase SelectGet Select
Close icon
General
Home
Marketplace
Create
Hire
Login
Chat
Sell
Explore

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
Claude

Csv Cleaner Fix Convert Json Python

Add to Cart
Instant accessInstant access
Usage rightsCommercial use
Money-back guaranteeMoney‑back
By purchasing this prompt, you agree to our terms of service
CLAUDE-5-5-SONNET
Tested icon
Guide icon
4 examples icon
Free credits icon
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
Report
Browse Marketplace