CSV Data Cleaning and Normalization

1Quick Overview

You: [uploads customers.csv, 12,000 rows, mixed date formats,
      inconsistent country names] "Clean this for our CRM import"

Skill returns:
  ✓ Normalized 4 date columns to ISO 8601 (found 6 formats)
  ✓ Standardized 'Country' column (14 variants → 3 canonical)
  ✓ Split 'Full Name' into first/last (11,847 rows)
  ✓ Removed 43 duplicate rows (based on email)
  ✓ Fixed 128 encoding artifacts (UTF-8 replacement chars)
  ✓ Trimmed whitespace from all string columns

Output: customers-clean.csv + transformations.md

2What This Skill Does

This Skill takes a messy CSV (or Excel sheet exported as CSV) and produces a cleaned version plus a transformation report documenting every change. It handles the common data hygiene problems: inconsistent date formats normalized to ISO 8601, character encoding fixes, combined fields split ("Last, First" → separate columns), leading/trailing whitespace trimmed, duplicate rows detected and removed based on user-specified keys, and inconsistent categorical values collapsed to canonical forms ("USA"/"US"/"United States" → "US").

The transformation report is the important part — it's a plain-English record of every change with row counts and examples. This makes the cleaning reproducible and auditable, which matters when the data feeds downstream systems (CRM import, financial reconciliation, analytics warehouse).

The Skill is idempotent by design — running it twice on the same file produces the same output as running it once. It also refuses to guess when confidence is low: ambiguous dates (03/04/2026 = March 4 or April 3?) trigger a clarifying question rather than a silent guess.

3When You Should Use This Skill

• The user has a CSV or spreadsheet with obvious data hygiene issues (mixed date formats, encoding artifacts, inconsistent values).
• The user is prepping data for import into a downstream system that has strict schema requirements.
• The user needs an auditable data cleaning process — the transformation report is the deliverable.
• The user is deduplicating a customer list, contact list, or lead list before running a campaign.
• The user is combining data from multiple sources and needs the categorical values reconciled.

4The Skill Definition (SKILL.md)

The complete SKILL.md file that ships with the download. This is what the model reads when your prompt matches this Skill's trigger conditions.

---
name: csv-data-cleaning-and-normalization
description: Clean CSV files — normalize dates, fix encodings, split combined fields, dedupe, standardize categorical values. Idempotent. Produces auditable transformation report.
version: 1.9.0
tags: [csv, data-cleaning, normalization, dedupe, etl]
inputs:
  - name: file
    type: file
    required: true
    description: The CSV or Excel file to clean
  - name: dedupe_keys
    type: array
    required: false
    description: Column names to use for duplicate detection (e.g. ["email"])
  - name: date_columns
    type: array
    required: false
    description: Columns known to contain dates (auto-detected if omitted)
  - name: categorical_map
    type: object
    required: false
    description: Manual mapping for categorical standardization
outputs:
  - name: cleaned_file
    type: file
  - name: transformation_report
    type: file
---

## Behavior

1. Load the file. Detect delimiter, encoding, header row.
2. Profile columns: detect dates, numbers, strings, categoricals.
3. For each detected issue, apply the appropriate transformation:
   - Whitespace: trim leading/trailing on all string columns.
   - Encoding: replace common artifacts (é, ’, Â) with proper UTF-8.
   - Dates: normalize to ISO 8601 (YYYY-MM-DD). Ask if ambiguous.
   - Combined name fields: split "Last, First" or "First Last" if consistent pattern.
   - Categoricals: standardize using categorical_map, or auto-cluster if not provided.
   - Duplicates: remove based on dedupe_keys, keeping first occurrence.
4. Emit cleaned file + transformation report.

## Idempotency

Every transformation must be safe to re-apply. Second run on the output of the first run must produce the same output.

## Hard rules

- NEVER silently guess ambiguous dates. Ask.
- NEVER delete data without documenting it in the transformation report.
- NEVER change numeric precision (don't round unless asked).
- If a column's values don't match its inferred type consistently (some numbers, some strings), flag it — don't silently coerce.
- Encoding fixes: only replace known artifacts, never guess.

## Report format

The transformation report is a markdown file with one section per transformation:
- Name of the transformation
- Column(s) affected
- Row count affected
- 3 example before/after pairs
- Any rows requiring manual review (with row numbers)

5How to Install

6How to Use — Real Examples

Input: customers.csv (12,043 rows, 8 columns)

Skill profiles the data:
  - 'Signup Date': dates in 6 different formats
  - 'Country': 14 unique values that should be 3 (US, UK, CA)
  - 'Full Name': single column, "Last, First" for 11,847 rows
  - 'Email': 43 duplicates based on lowercased email
  - Various columns: leading/trailing whitespace on 8,921 cells
  - 'Notes': UTF-8 replacement chars in 128 cells

Skill transformations applied (all logged):
  1. Trimmed whitespace: 8,921 cells across 6 columns
  2. Fixed UTF-8 artifacts in 'Notes' column: 128 replacements
  3. Split 'Full Name' → 'First Name' + 'Last Name': 11,847
     rows (196 skipped — single-word names, kept as-is with
     row numbers listed for review)
  4. Normalized 'Signup Date' → ISO 8601: 11,943 rows.
     Flagged: 100 rows had "03/04/2024" format (ambiguous) —
     see report for row numbers requiring manual disambiguation.
  5. Standardized 'Country' (14 → 3): US, UK, CA
  6. Removed duplicates by 'email' (lowercased): 43 rows dropped

Output: customers-clean.csv (12,000 rows) + report.md

First run:
  Input:  customers.csv       (12,043 rows, messy)
  Output: customers-clean.csv (12,000 rows, clean)
  Report: 6 transformations applied

Second run:
  Input:  customers-clean.csv (12,000 rows, already clean)
  Output: customers-clean.csv (12,000 rows, unchanged)
  Report: 0 transformations applied

Every transformation is designed to be a no-op when its
target state is already achieved. Safe to run inside ETL
pipelines that may re-process files.

Input: sales-log.csv with 'Date' column values like:
  "03/04/2024"
  "12/07/2024"
  "01/15/2024"

Skill analyzes:
  - Some values have day > 12 (e.g. 15) → US format
    (M/D/Y) confirmed for those rows
  - Some values have both parts ≤ 12 → ambiguous

Skill asks:
  "The 'Date' column has 47 rows where the format is
   ambiguous — either M/D/Y (US) or D/M/Y (rest of
   world). Rows with day > 12 confirm M/D/Y. Should
   I apply M/D/Y to all rows?
   
   [Yes, use M/D/Y] [No, use D/M/Y] [Show me the
   ambiguous rows]"

Without user confirmation, Skill will NOT process the
date column. Transformation report will list the column
as "skipped — awaiting user input on format".

7Real-World Use Cases

8Common Variations (Fork Ideas)

• Excel-native fork — Read/write .xlsx directly instead of CSV. Preserves multiple sheets, formulas, and cell formatting. Slower but preserves what CSV loses.
• PII redaction fork — In addition to cleaning, redact or hash PII (email, phone, address) for teams needing GDPR-safe data before sharing internally.
• Fuzzy dedupe fork — Standard dedupe is exact-match. Fuzzy fork uses edit distance + phonetic matching to catch "John Smith" vs "J. Smith" vs "Jon Smyth".
• Schema-validation-first fork — Given a target schema (JSON Schema or Pydantic model), validate the CSV against it and clean specifically to make it conform.
• Large-file fork — For files over 100MB — chunked processing that never loads the full file into context. Slower but handles multi-GB CSVs.

9Compatible Providers

Provider-by-provider status as of 2026-08-11. See our providers directory for full context on each.

10Testing & Benchmarks

Tested against 500 real-world messy CSVs from CRMs, spreadsheets, and third-party exports. Transformation correctness measured by human review of sample rows. Data loss incidents are cases where the cleaned file lost data that the original had (should be zero).

11Troubleshooting

The five most common issues we hit with this Skill. Each links to a full fix on our sister site.

12Related Skills

Other Skills our team uses alongside CSV Data Cleaning and Normalization.

13Related MCP Servers

MCP servers that pair naturally with this Skill for full workflows.

14Frequently Asked Questions