Download the scripts and tests (ZIP)
Python scripts, tests, and ready-to-run merge, join, and date practice files with expected results. No signup required.
An export contains 2026-09-15, 31/08/2026, and 03/04/2026 in the same date column. The first two can be interpreted under specific rules. The third could mean March 4 or April 3. A script that accepts its first successful interpretation can silently put a record in the wrong month.
This guide audits one date column, keeps every original record, and adds an ISO date only when the allowed formats lead to one distinct calendar date. Missing, invalid, and ambiguous values go into a separate issue report. Nothing edits the source file.
Tested with Python 3.10 on Windows on September 15, 2026, using synthetic exports. No extra packages, uploads, or account connections are needed to run the example.
Set the date contract
Run the downloadable audit
The Download the scripts and tests (ZIP) link on this page includes invented dates and expected results. Extract the whole archive, open a terminal in its root folder containing examples and fixtures, and check python --version. This practice was tested with Python 3.10 on Windows on September 18, 2026; no extra packages are needed. Substitute py -3.10 on Windows if you use that launcher, or python3 on systems using that command.
python examples/validate_csv_dates.py fixtures/dates/inputs/dates.csv practice-dates --column date --formats iso dmy mdy
Expect valid=2 missing=1 invalid=1 ambiguous=1 in the console and practice-dates/COMPLETE.txt. Exit code 2 is intentional: the audit finished and found issues. Exit 1 is a processing failure. Run the command separately rather than chaining it with &&, which commonly skips subsequent commands when the exit code is nonzero.
Compare practice-dates/checked.csv and practice-dates/issues.csv against fixtures/dates/expected. The ISO date and 31/08/2026 are valid. 03/04/2026 keeps both candidate dates in the issue report and has no chosen ISO date. 2026-02-30 is invalid and the empty value is missing. Record numbers count the header as record 1, not physical lines. A completion marker means the reports were written, not that every date is valid.
The destination folder must be new; use a different name if practice-dates already exists. Never select the expected-results folder as output. fixtures/README.md contains the whole practice walkthrough. To check all bundled workflows on temporary copies, run python -m unittest discover -s . -p test_examples.py -v from the extracted root. The tests compare parsed records, check the expected exit code and confirm input preservation. For your own exports, select only date formats confirmed by the producer, as explained next.
The script supports three explicit format names:
| Name | Exact shape | Example |
|---|---|---|
iso |
Four-digit year, two-digit month, two-digit day | 2026-09-15 |
dmy |
Two-digit day/month/four-digit year | 31/08/2026 |
mdy |
Two-digit month/day/four-digit year | 08/31/2026 |
Choose only the formats confirmed by the source system. If separate exports have different known conventions, audit them separately before merging their CSV records. Allowing both slash conventions deliberately makes 03/04/2026 ambiguous; it does not resolve the ambiguity.
Python’s datetime documentation describes parsing a string against a format and reporting invalid dates. Our script adds exact ASCII digit shapes before parsing, then compares all successful calendar dates. It does not trim whitespace, accept two-digit years, or infer the format from neighboring rows.
Make a five-record sample
Save this as dates.csv in UTF-8. Preserve the comma after 005; it represents an empty date cell.
id,date
001,2026-09-15
002,31/08/2026
003,03/04/2026
004,2026-02-30
005,
The sample includes two valid dates, one ambiguous value, an impossible calendar date, and one missing value. IDs remain text, including their leading zeros.
Save the complete audit script
Save the following as validate_csv_dates.py next to your sample. In the downloadable ZIP, scripts are inside examples/; prefix the script path accordingly when running from the extracted folder.
"""Audit one CSV date column using explicit formats and reject ambiguous dates."""
import argparse
import csv
import re
from datetime import datetime
from pathlib import Path
FORMATS = {
'iso': (r'[0-9]{4}-[0-9]{2}-[0-9]{2}', '%Y-%m-%d'),
'dmy': (r'[0-9]{2}/[0-9]{2}/[0-9]{4}', '%d/%m/%Y'),
'mdy': (r'[0-9]{2}/[0-9]{2}/[0-9]{4}', '%m/%d/%Y'),
}
def classify_date(value, formats):
if value == '':
return '', 'missing', []
candidates = set()
for name in formats:
pattern, fmt = FORMATS[name]
if re.fullmatch(pattern, value):
try:
candidates.add(datetime.strptime(value, fmt).date().isoformat())
except ValueError:
pass
candidates = sorted(candidates)
if len(candidates) == 1:
return candidates[0], 'valid', candidates
return '', 'ambiguous' if candidates else 'invalid', candidates
def audit_dates(source, destination, column, formats):
if not formats or len(set(formats)) != len(formats) or set(formats) - FORMATS.keys():
raise ValueError('Choose unique formats from iso, dmy, mdy')
source, destination = Path(source), Path(destination)
with source.open(encoding='utf-8-sig', newline='') as handle:
reader = csv.reader(handle, strict=True)
header = next(reader, None)
if not header or any(not h.strip() for h in header) or len(set(header)) != len(header):
raise ValueError('Expected unique, nonblank headers')
if column not in header:
raise ValueError('Date column not found')
if {'_date_iso', '_date_status'} & set(header):
raise ValueError('Input collides with output column names')
position = header.index(column)
checked, issues = [], []
counts = dict(valid=0, missing=0, invalid=0, ambiguous=0)
for number, row in enumerate(reader, 2):
if len(row) != len(header):
raise ValueError(f'Record {number}: wrong number of fields')
normalized, status, candidates = classify_date(row[position], formats)
checked.append(row + [normalized, status])
counts[status] += 1
if status != 'valid':
issues.append([str(number), row[position], status, '|'.join(candidates)])
destination.mkdir()
for name, titles, rows in (
('checked.csv', header + ['_date_iso', '_date_status'], checked),
('issues.csv', ['record', 'value', 'status', 'candidate_dates'], issues),
):
with (destination/name).open('x', encoding='utf-8', newline='') as handle:
writer = csv.writer(handle)
writer.writerow(titles)
writer.writerows(rows)
with (destination/'COMPLETE.txt').open('x', encoding='utf-8') as handle:
handle.write(' '.join(f'{key}={value}' for key, value in counts.items()) + '\n')
return counts
def main():
parser = argparse.ArgumentParser(description=__doc__)
parser.add_argument('source', type=Path)
parser.add_argument('destination', type=Path)
parser.add_argument('--column', required=True)
parser.add_argument('--formats', nargs='+', choices=FORMATS, required=True)
args = parser.parse_args()
try:
counts = audit_dates(args.source, args.destination, args.column, args.formats)
except (OSError, ValueError, csv.Error) as exc:
parser.exit(1, f'Audit failed: {exc}\nOutput without COMPLETE.txt is incomplete.\n')
print(' '.join(f'{key}={value}' for key, value in counts.items()))
return 2 if any(counts[k] for k in ('missing', 'invalid', 'ambiguous')) else 0
if __name__ == '__main__':
raise SystemExit(main())
The input must be comma-separated UTF-8, with an optional UTF-8 signature. If the source uses another confirmed encoding, convert it to UTF-8 first. The script checks headers, field counts, and CSV quoting before creating its destination folder. Duplicate or blank headers, a missing date column, and existing _date_iso or _date_status columns cause a structural failure.
Run it with explicit formats
python validate_csv_dates.py dates.csv date-audit --column date --formats iso dmy mdy
Expected output:
valid=2 missing=1 invalid=1 ambiguous=1
For this valid command, the script exits with status 2, meaning the audit finished and found date issues. Status 0 means it finished without date issues. Status 1 means a handled processing error, such as an existing destination or malformed input. Invalid command-line arguments also use exit code 2, so check the console message and fresh output folder rather than treating the number alone as proof of a completed audit.
date-audit/checked.csv contains all five records, in source order:
id,date,_date_iso,_date_status
001,2026-09-15,2026-09-15,valid
002,31/08/2026,2026-08-31,valid
003,03/04/2026,,ambiguous
004,2026-02-30,,invalid
005,,,missing
date-audit/issues.csv contains only the unresolved values:
record,value,status,candidate_dates
4,03/04/2026,ambiguous,2026-03-04|2026-04-03
5,2026-02-30,invalid,
6,,missing,
Record numbers count CSV records with the header as record 1. They are not physical line numbers: a quoted multiline note can span multiple lines while still belonging to a single record.
Verify the audit before using its dates
Run this from the folder containing the original sample and date-audit:
import csv
from pathlib import Path
assert Path('date-audit/COMPLETE.txt').is_file()
with open('dates.csv', encoding='utf-8-sig', newline='') as stream:
original = list(csv.reader(stream))
with open('date-audit/checked.csv', encoding='utf-8', newline='') as stream:
checked = list(csv.reader(stream))
assert [row[:-2] for row in checked] == original
assert [row[-1] for row in checked[1:]] == [
'valid', 'valid', 'ambiguous', 'invalid', 'missing'
]
assert [row[-2] for row in checked[1:]] == [
'2026-09-15', '2026-08-31', '', '', ''
]
print('Verified original record values and date classifications')
Ask the data owner which convention applies to the ambiguous record. If they confirm day/month/year for that file, rerun with --formats iso dmy into a new folder. The script never chooses a date because one format was listed first. Conversely, 04/04/2026 is valid even with both slash formats enabled because both interpretations produce the same date.
Failures and practical limits
Exit code 2 has two different causes
With the downloaded practice files, the correctly formed command produces reports and prints valid=2 missing=1 invalid=1 ambiguous=1. If you instead type an unsupported format, the command parser stops before the audit:
python examples/validate_csv_dates.py fixtures/dates/inputs/dates.csv rejected-dates --column date --formats ymd
On Python 3.10 on Windows, September 19, 2026, this returned exit code 2 with an invalid choice message and created no destination. ymd is not an accepted format name. Python’s argument-parsing documentation describes invalid-argument errors.
Use iso for a confirmed year-month-day source. For the bundled mixed sample, use the documented iso dmy mdy list and a fresh output folder. A finished audit has the printed counts and a COMPLETE.txt created for that run; a usage error prints usage: and an argument error. Never check an old folder’s marker to validate a new failed command.
After the source owner confirms that the bundled slash dates use day/month/year, this demonstration resolves the ambiguous row without changing the original data:
python examples/validate_csv_dates.py fixtures/dates/inputs/dates.csv confirmed-dmy --column date --formats iso dmy
Expect valid=3 missing=1 invalid=1 ambiguous=0. Exit code 2 remains correct because the impossible date and missing value still require attention. The former ambiguous row now has _date_iso of 2026-04-03. Do not remove a format merely to reduce issue counts unless the producer confirms that convention.
Handing the result to another guide
checked.csv keeps the original date column and adds _date_iso and _date_status. Re-auditing this same file directly is refused because those output names already exist. Prefer rerunning from the original export with the confirmed format contract. When preparing an import, review unresolved rows before selecting columns; a blank _date_iso is not a repaired date. The column selection guide changes columns, not date validity or row inclusion. These tools do not automatically form a complete cleanup pipeline.
The included tests cover leap years (including 1900 and 2000), invalid dates, strict padding, whitespace, non-ASCII digits, ambiguous values, BOM input, Unicode and multiline notes, header conflicts, malformed records, existing destinations, and command exit codes. A header-only file produces empty reports with zero counts.
An empty string is missing; spaces are invalid. An unapproved shape and an impossible calendar date share the invalid status. Timestamps, time zones, spreadsheet serial dates, named months, and business rules such as “invoice date must not be in the future” are outside this contract.
Reports are held in memory before writing, so use this version for exports that fit comfortably in memory. It preserves field values, not exact original quoting or byte layout. A write failure can leave a partial output folder; COMPLETE.txt appears only after both CSV files close successfully. The marker means processing completed, not that every date is valid. Review the counts and issue report too.
Never overwrite the original to resolve issues. Keep the original export and a record of the confirmed date convention. Once reviewed, use the explicit _date_iso column in later work, or select and reorder the columns needed by the receiving system.