CSV Cleaner

Turn a messy spreadsheet export into a tidy one. A real command-line tool that reads a CSV, cleans it, and writes the result to a new file.

Beginner 45–60 minutes

About this project

Exported data is almost always grubby: stray spaces around values, blank rows, inconsistent casing. A CSV cleaner reads that mess, tidies every field, and writes a fresh, clean copy, without ever touching the original. It’s the kind of small standard-library automation that quietly saves people hours.

Why it’s worth building: this is the classic “read a file, transform it, write a file” pipeline, the backbone of data work and automation. We’ll also make it a proper tool: instead of hard-coding filenames, it takes them from the command line, so you can point it at any CSV and reuse it for real.

The whole job in one picture: read a file, transform each row, write a clean copy.

Build it step by step

We’ll build the pipeline in pieces, read, clean, write, then wrap it in a command-line front door so it behaves like a tool you’d actually keep around. First, make a messy file to test against.

Step 0: A messy file to clean

Create a file called data.csv next to your script with these exact contents — note the rogue spaces and the blank line. Real exports look just like this.

Example · data.csv 
name, city
  Ada , Lagos 

 Linus , Helsinki
Our test data: padded fields and an empty row, on purpose.

Step 1: Read the rows

The csv module understands the comma-separated format so you don’t have to split lines by hand (which breaks the moment a value contains a comma). Opening with newline="" is the documented way to let csv handle line endings correctly across operating systems. We wrap the read in a read_rows function so every later step can reuse it.

Example 
import csv


def read_rows(path):
    """Read every row of a CSV into a list of lists."""
    with open(path, newline="", encoding="utf-8") as f:
        return list(csv.reader(f))


for row in read_rows("data.csv"):
    print(row)
Output 
['name', ' city']
['  Ada ', ' Lagos ']
[]
[' Linus ', ' Helsinki ']
Each row comes back as a list of strings, spaces and blanks intact.

Step 2: Clean each field, drop empty rows

Two problems, two fixes. .strip() removes the padding from every field; a check with any() drops rows where nothing is left. Wrapping it in a function means the rest of the program just says clean_rows(rows) and trusts it.

Example 
def clean_rows(rows):
    """Trim whitespace from each field and drop fully-empty rows."""
    cleaned = []
    for row in rows:
        trimmed = [field.strip() for field in row]
        if any(trimmed):          # keep the row only if something remains
            cleaned.append(trimmed)
    return cleaned
A comprehension trims the fields; any() decides if the row is worth keeping.

Step 3: Write a clean copy

Cleaning is useless if you can’t save the result. csv.writer turns lists back into proper CSV lines, quoting anything that needs it. We write to a new file so the original mess is never overwritten.

Example 
def write_rows(rows, path):
    with open(path, "w", newline="", encoding="utf-8") as f:
        csv.writer(f).writerows(rows)


# try it end to end:
rows = read_rows("data.csv")          # from Step 1, wrapped in a function
write_rows(clean_rows(rows), "data.clean.csv")
print("Wrote data.clean.csv")
Output 
Wrote data.clean.csv
writerows writes the whole cleaned table in one call.

Step 4: Make it a command-line tool

Hard-coded filenames make a script a one-trick pony. Reading the command-line arguments in sys.argv lets you run python csv_cleaner.py messy.csv against any file. We derive a sensible default output name with pathlib, and catch a missing file instead of dumping a traceback.

Example 
import sys
from pathlib import Path

in_path = Path(sys.argv[1])
out_path = in_path.with_name(in_path.stem + ".clean.csv")

try:
    rows = read_rows(in_path)
except FileNotFoundError:
    print(f"No such file: {in_path}")
    raise SystemExit(1)

print(f"Reading {in_path}, writing {out_path}")
One argument turns a fixed script into a reusable tool.

The finished tool

Here’s the complete command-line cleaner: read → clean → write, wrapped in functions, driven by a command-line argument, with a friendly error and a summary line. This is a script you can genuinely keep in a tools folder and use.

Example · csv_cleaner.py 
import csv
import sys
from pathlib import Path


def read_rows(path):
    """Read every row of a CSV into a list of lists."""
    with open(path, newline="", encoding="utf-8") as f:
        return list(csv.reader(f))


def clean_rows(rows):
    """Trim whitespace from each field and drop fully-empty rows."""
    cleaned = []
    for row in rows:
        trimmed = [field.strip() for field in row]
        if any(trimmed):
            cleaned.append(trimmed)
    return cleaned


def write_rows(rows, path):
    with open(path, "w", newline="", encoding="utf-8") as f:
        csv.writer(f).writerows(rows)


def main():
    if len(sys.argv) < 2:
        print("Usage: python csv_cleaner.py <input.csv> [output.csv]")
        raise SystemExit(1)

    in_path = Path(sys.argv[1])
    if len(sys.argv) > 2:
        out_path = Path(sys.argv[2])
    else:
        out_path = in_path.with_name(in_path.stem + ".clean.csv")

    try:
        rows = read_rows(in_path)
    except FileNotFoundError:
        print(f"No such file: {in_path}")
        raise SystemExit(1)

    cleaned = clean_rows(rows)
    write_rows(cleaned, out_path)
    print(f"Read {len(rows)} rows, wrote {len(cleaned)} clean rows to {out_path}")


if __name__ == "__main__":
    main()
Output 
$ python csv_cleaner.py data.csv
Read 4 rows, wrote 3 clean rows to data.clean.csv
Run it on data.csv and you get data.clean.csv — original untouched.

Keep going, make it your own

You have a working tool. Each idea below turns it into something you’d reach for on real data, and introduces one more genuinely useful technique.

Remove duplicate rows. A set remembers what it’s already seen, so you can skip repeats in one pass. Lists can’t go in a set, but a tuple of the row can.

Example 
def drop_duplicates(rows):
    seen = set()
    unique = []
    for row in rows:
        key = tuple(row)        # tuples are hashable; lists are not
        if key not in seen:
            seen.add(key)
            unique.append(row)
    return unique
A set turns “have I seen this row?” into an instant check.

Clean by column with DictReader. Reading rows as dictionaries lets you target specific fields, title-case names, lowercase emails, instead of guessing positions. Far more readable than row[0], row[1].

Example 
import csv

with open("data.csv", newline="", encoding="utf-8") as f:
    for record in csv.DictReader(f):
        # headers can be padded too, so strip the keys as well as the values
        clean = {key.strip(): value.strip() for key, value in record.items()}
        print(f"{clean['name'].title()} - {clean['city']}")
Output 
Ada - Lagos
Linus - Helsinki
DictReader keys each row by its header; a dict comprehension trims both.

Clean a whole folder at once. Use pathlib’s glob to find every CSV in a folder, and write the results into a cleaned/ subfolder you create automatically. This is real automated file management, point it at a directory and walk away.

Example 
from pathlib import Path

out_dir = Path("cleaned")
out_dir.mkdir(exist_ok=True)              # create the output folder once

for csv_path in Path("data").glob("*.csv"):
    cleaned = clean_rows(read_rows(csv_path))
    write_rows(cleaned, out_dir / csv_path.name)
    print(f"Cleaned {csv_path.name} -> cleaned/{csv_path.name}")
glob finds the files; mkdir prepares the destination; the loop does the rest.

Give it real flags with argparse. Once a tool grows options, argparse (also standard library) handles --output, --dedupe, and a built-in --help for you, the professional step up from reading sys.argv by hand.

Example 
import argparse

parser = argparse.ArgumentParser(description="Clean a CSV file.")
parser.add_argument("input", help="path to the messy CSV")
parser.add_argument("-o", "--output", help="where to write the clean copy")
parser.add_argument("--dedupe", action="store_true", help="drop duplicate rows")
args = parser.parse_args()

print(args.input, args.output, args.dedupe)
argparse gives you --help and friendly errors for free.

Download the files

Grab the sample data and the finished tool, then run python csv_cleaner.py data.csv and compare your output to the expected file.

Mini exercise (medium)

Write the cleaner’s core: clean_rows(rows) that trims whitespace from every field and drops rows that are entirely empty, working on an in-memory table (no file needed).

Now you. Edit the starter code below, then Run it, everything happens in the browser.

def clean_rows(rows):
    cleaned = []
    for row in rows:
        # TODO: strip each field; keep the row only if something remains
        cleaned.append(row)
    return cleaned

messy = [["  Ada ", " Lagos "], ["", ""], [" Linus ", " Helsinki "]]
print(clean_rows(messy))

Where to go next

You’ve built a real automation tool, the same pattern behind report generators and data pipelines. To package it up properly (folders, a virtual environment, a README), see Organizing a Python Project.