CSV Sanitizer & Schema Mapper Prompt
Quickly clean messy CSVs, infer types, fix headers, and output a consistent schema for hassle-free analysis.
Prompt Overview
Tips For You
Collect 30–50 row slices from different time periods to expose hidden formats; lock naming conventions early (renaming later breaks joins and charts); document assumptions directly in the schema table for auditability; keep raw values in a hidden sheet for easy rollback and checks.
From Operations TeamNexusAi TechnologyProblem It Solves
Raw CSV exports often contain inconsistent headers, mixed types, empty rows, and confusing encodings that break downstream analysis and charts.
Header normalization
Standardizes messy headers into analytics-ready names.
Type inference
Detects dates, currency, booleans, and numeric fields reliably.
Dual execution
Provides both spreadsheet formulas and Python/pandas steps.
QA assertions
Includes practical checks to prevent silent data errors.
AI Prompt Instructions
Act as: A senior analytics engineer specializing in data quality, CSV normalization, and analytics-ready schemas for spreadsheets and BI tools.
Why this task matters: Clean, typed, and consistently named columns allow reliable KPIs, correct joins, and trustworthy executive dashboards.
Important boundaries:
- Do not fabricate data.
- Preserve raw values in a traceable way.
- Prefer simple, reversible transformations.
- Keep regional formats noted (dates, decimals, currency).
User inputs:
- Business context (team, purpose, target KPIs).
- Paste a 30–50 row sample from the CSV (include header).
- Indicate locale (e.g., en-US) and date/currency conventions.
Objectives:
1) Diagnose issues (headers, types, missingness, duplicates, delimiters, encodings).
2) Propose a clean schema: column names, data types, and definitions.
3) Provide transformation steps that can be executed in Google Sheets/Excel or Python/pandas.
4) Output small QA checks and exception flags.
Analysis workflow:
1) Profile the sample: uniqueness, ranges, nulls, categorical levels, likely IDs/keys.
2) Map headers to standardized, analytics-friendly names (lowercase_snake_case).
3) Resolve data types (date, datetime, boolean, int, float, currency, category, text).
4) Specify transformations: trim/normalize text; parse dates; split combined fields; fix currency symbols; unify units.
5) Identify primary key candidates and foreign keys if visible.
6) List rows that should be filtered (totals, notes, blank lines) and the rule.
Required output format:
- Clean Schema Table: original_header → clean_name, type, sample_value, rule.
- Transform Steps: spreadsheet functions and equivalent pandas code.
- QA Checks: 5–10 assertions (e.g., non-null % for key fields, date range, numeric-only columns).
- Exceptions Handling: how to log and where to review.
Quality controls:
- Avoid destructive transforms; retain raw in a separate sheet or column suffix _raw when needed.
- Call out ambiguous fields and request a decision.
Verification checklist:
- Headers standardized and documented.
- Types validated with sample rows.
- Critical IDs unique; no silent drops.
- Date parsing confirmed for locale.
Final instruction: Produce the schema table, transformation steps (Sheets formulas + pandas), QA assertions, and a short note on risks and open questions. Keep it concise and implementation-ready.
Expected Outcome
Clean Schema Table: - original: Customer ID → customer_id (text) sample: C-1023 rule: UPPER(TRIM()) - original: Order Date → order_date (date) sample: 2026-06-14 rule: DATEVALUE with locale note - original: Revenue ($) → revenue_usd (float) sample: 1299.50 rule: strip $ and commas Transform Steps: - Sheets: =VALUE(SUBSTITUTE(SUBSTITUTE(C2,"$",""),",","")) - pandas: df['revenue_usd']=pd.to_numeric(df['Revenue ($)'].str.replace('[\$,]','',regex=True)) QA Checks: - Assert unique customer_id - order_date within last 36 months - revenue_usd ≥ 0
Implementation Journey
Diagnose and map the CSV in ChatGPT
Open ChatGPT and paste 30–50 representative rows with headers plus your locale and KPI context. Ask for the clean schema, type inferences, and fix rules. Expect a schema table, spreadsheet functions, pandas code, and QA checks.
10-15 minApply fixes in Google Sheets or Excel
Create a cleaned sheet using the provided header map and formulas. Validate types, parse dates, and calculate currency-safe values. Use the QA checks to flag anomalies in a separate QA tab.
15-25 minExport and lock the schema
Freeze the header row, protect key cells, and export to a clean CSV for downstream joins and charts. Store the schema and QA notes in your team wiki to standardize future imports.
5-10 min