If you’ve ever spent an afternoon wrestling with a CSV file that came from a client or a third-party tool, you know the pain. Rows with inconsistent date formats, duplicate entries, nulls where there should be numbers — it’s a mess. And you can’t just fix it by hand because there are 50,000 rows. You need a script.
In this post, I’ll walk you through a simple Python script that cleans up the most common CSV issues. You don’t need to be a coding wizard to use it. If you can run a terminal command, you can do this.
The first step is to install pandas, the data workhorse of Python. If you have Python installed, you can just run `pip install pandas`. If not, install Python first — it’s a five-minute job. I’ll assume you’re on macOS or Linux, but Windows users can follow along with a few tweaks.
Now, let’s write the script. Create a file called `clean_csv.py` and paste the following:
“`python
import pandas as pd
import sys
if len(sys.argv) != 2:
print(“Usage: python clean_csv.py “)
sys.exit(1)
input_file = sys.argv[1]
df = pd.read_csv(input_file)
df.drop_duplicates(inplace=True)
df = df.dropna(how=’all’)
df.fillna(”, inplace=True)
for col in df.select_dtypes(include=[‘object’]).columns:
df[col] = df[col].str.strip()
df[col] = df[col].str.replace(‘s+’, ‘ ‘, regex=True)
output_file = ‘cleaned_’ + input_file
df.to_csv(output_file, index=False)
print(f”Cleaned data saved to {output_file}”)
“`
That’s the core. Let me explain what it does. First, it reads your CSV into a DataFrame. Then it drops exact duplicate rows — because the same row might appear twice. Next, it removes rows where every field is empty, because those are just noise. Then it fills any remaining missing values with an empty string, so your downstream tools don’t choke on `NaN`.
Then, for every text column, it strips leading and trailing whitespace and collapses multiple spaces into one. This fixes a common issue where names or addresses have extra spaces that break lookups. Finally, it writes a new file with `cleaned_` prefixed to the original name.
To run it, just type `python clean_csv.py sales_export.csv`. In seconds, you’ll have a clean file ready for your database or analytics tool. No manual work, no errors.
A few tips from real-world use: if your CSV has a column with dates, you might want to normalize them. Add a line like `df[‘date’] = pd.to_datetime(df[‘date’]).dt.strftime(‘%Y-%m-%d’)` to standardize. Also, if you have columns with leading zeros (like zip codes), you’ll want to keep them as strings — pandas might convert them to numbers and drop the zeros. Use `dtype={‘zip’: str}` when reading.
This script is a starting point. You can extend it to handle specific column rules, merge data from multiple files, or even send an email with the cleaned file attached. I’ve used a variation of this to clean data from a CRM export that had 40k rows, and it reduced my processing time from a day to ten minutes.
If you want a ready-to-run version with a few extra features, check out our CSV Cleaner script in the library. It’s a more polished version with command-line flags for common transformations, and it’s been tested on messy exports from HubSpot, Salesforce, and Google Sheets. You’ll get it instantly with a lifetime license.