If you’ve ever spent an hour deleting blank rows, fixing date formats, and removing duplicates from a CSV that your CRM exported, you know the pain. I used to do that every Monday. Then I wrote a 20-line Python script that does it in one go. Here’s how it works, and how you can use it too.
The problem is that most tools export CSVs in their own quirky format: extra columns, inconsistent date strings, and a bunch of empty cells where data should be. Manually cleaning up is not only tedious but also error-prone. You miss a duplicate or you accidentally delete a row that had data in a different column. Automation removes that risk.
My script uses Python’s built-in csv module — no pandas, no numpy, nothing to install. It reads the file, trims whitespace from every cell, converts dates to YYYY-MM-DD, drops duplicate rows based on a column you specify, and writes the cleaned file. That’s it. It runs in a few seconds even on a 100MB file.
Here’s the core logic: you pass the input filename and the column index for deduplication. The script reads all rows into memory, then filters out blank rows (where all cells are empty), then uses a set to track seen values in the dedupe column. If a value appears twice, the second one is skipped. Finally, it writes to a new file with ‘_cleaned’ appended.
I’ve used this script for client data, for my own email lists, and for product analytics exports. It’s saved me hours every month. And it’s not just for data analysts — any founder who exports from a tool like HubSpot, Stripe, or Google Forms can use it.
One caution: always test on a copy of your data first. The script does a lot, but it can’t read minds. If your date format is unusual, you might need to tweak the regex. But for 90% of cases, it works out of the box.
If you want the ready-to-run script, I’ve made it available in the library. It comes with a README that explains the parameters and a few examples. You’ll be cleaning CSVs in minutes, not mornings.
So next time you’re staring at a messy export, remember: you don’t have to do that by hand. A few lines of code can take the pain away. Your future self will thank you.