Tony CantaveHow I Validate CSV Data With Python Before It Causes Problems

Most of the data problems I have dealt with in my career did not start with a broken system. They started with a file. A spreadsheet export with an empty column, a date typed the wrong way, the same record listed twice. When I worked on ERP and SAP integration in manufacturing, one bad row in an import file could mean a long afternoon of cleanup. Today I write Python and JavaScript samples for AI model training and build software for Local Flyer Ads, and the lesson still holds: check the data before you trust it.

Here is the simple approach I use to validate a CSV file with Python before it goes into a database, a report or another program. It uses only the standard library, so there is nothing extra to install.

Step one is deciding what good data looks like, before writing any code. I write the rules in plain language first. For a list of advertisers, the rules might be that every row needs a business name, a town and a start date; that the start date must be a real calendar date in one agreed format; that the monthly price must be a number greater than zero; and that no business appears twice. Writing the rules first keeps the script honest. If I cannot explain a rule in one sentence, I probably do not understand the data well enough yet.

Step two is checking the columns before the rows. I open the file with the csv module and read it with DictReader, which turns each row into a dictionary keyed by the header names. Before looking at a single row, I compare the header list against my list of required columns. If a required column is missing entirely, every row would fail, and a wall of identical errors helps nobody. One clear message that names the missing column is far more useful.

Step three is checking each row on its own. For every required field, I strip the surrounding spaces and flag the row if nothing is left, because a cell that holds only a space looks filled in but is really empty. For the date, I pass the text to datetime.strptime with the exact format I expect. If Python cannot parse it, the row gets flagged with the bad value included in the message, so the person fixing it can see exactly what was typed. For the price, I try to convert the text to a number and flag it if the conversion fails or the result is zero or less.

Step four is catching duplicates. As I loop through the rows, I keep a set of business names I have already seen. Before comparing, I lowercase each name and trim extra spaces, so "Sample Barbers" and "sample barbers " with a trailing space count as the same business. That kind of near duplicate slips past a quick visual check almost every time.

Two design choices make the output useful. First, the script collects every problem instead of stopping at the first one. If a file has twelve issues, I want to see all twelve at once, not fix one, rerun, and discover the next. Second, every message includes the line number. Line 2 is the first data row because line 1 holds the headers, so when someone opens the file in a spreadsheet they can jump straight to the row that needs attention. At the end, the script prints a simple count of problems found.

Day to day, I run the check by hand on any file before importing it. If it reports zero problems, the file moves on. If not, I fix the source rather than patching the data later. For files that arrive on a schedule, the same function can run at the start of an automated job and stop the job when problems are found, so bad data never reaches the next step.

My AI evaluation work taught me something that applies here too: a false alarm is costly. If a validator flags things that are not really wrong, people learn to ignore it. So I keep the rules tight and specific, and I add a new rule only when I have seen a real problem it would have caught.

Data validation is not glamorous, but it is one of the most valuable habits I know. A few dozen lines of Python can save hours of cleanup. If you work with spreadsheets or exports in a small business, start with three or four rules you can explain in a sentence each, and build from there.

Questions or ideas? Reach me at sales@localflyerads.com.