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.
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.
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.
name, city
Ada , Lagos
Linus , Helsinki
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.
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)
['name', ' city'] [' Ada ', ' Lagos '] [] [' Linus ', ' Helsinki ']
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.
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
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.
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")
Wrote data.clean.csv
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.
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}")
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.
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()
$ python csv_cleaner.py data.csv Read 4 rows, wrote 3 clean rows to data.clean.csv
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.
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
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].
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']}")
Ada - Lagos Linus - Helsinki
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.
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}")
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.
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)
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).
Do it here. Finish the code below and press Run to try your answer right in the browser — no setup needed.
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))For each row build [field.strip() for field in row], and keep it only when any(...) of the trimmed fields is non-empty.
def clean_rows(rows):
cleaned = []
for row in rows:
trimmed = [field.strip() for field in row]
if any(trimmed):
cleaned.append(trimmed)
return cleaned
messy = [[" Ada ", " Lagos "], ["", ""], [" Linus ", " Helsinki "]]
print(clean_rows(messy))