Standardize mixed date formats in a CSV and list unparsed values

01Define the accepted date formats before parsing

Date cleanup is safer when the accepted formats are explicit. This example recognizes six numeric formats and converts every successfully parsed value to YYYY-MM-DD. It does not use automatic date guessing.

The interpretation of slash and hyphen formats is a policy decision. In this tutorial, 09/02/2026 means September 2, 2026, while 04-09-2026 means September 4, 2026. If your source uses different conventions, change the accepted formats before processing real data.

02Create a small synthetic CSV

The following dataset is synthetic and was written specifically for this article. Save it as mixed_dates.csv in your working folder. It contains six valid dates in six different accepted formats and two values that should fail.

record_id,event_date,note
R001,2026-09-01,ISO format
R002,09/02/2026,US slash format
R003,2026/09/03,year first with slashes
R004,04-09-2026,day first with hyphens
R005,2026.09.05,dot separated
R006,20260906,compact numeric
R007,2026-02-30,invalid calendar date
R008,Sep 7 2026,unsupported text format

There are 8 data records. The first 6 should parse successfully. R007 uses a recognized pattern but February 30 is not a real date. R008 uses a text month name that is intentionally not included in the accepted format list.

03Work out the expected cleaned values by hand

The first six values all represent consecutive dates from September 1 through September 6, 2026. They should therefore produce six standardized values. The remaining two records should keep their original text in the review file.

The expected counts are therefore 8 total records, 6 parsed records, and 2 unparsed records. The cleaned CSV will still contain all 8 records; unparsed rows receive an empty standardized_date value and an UNPARSED status.

04Parse approved formats and write new files

Save the following script as date_format_cleanup.py. It reads the original CSV, writes a complete cleaned copy, and also writes a smaller review CSV containing only the records whose date could not be standardized.

import csv
from datetime import datetime
from pathlib import Path

SOURCE = Path("mixed_dates.csv")
OUTPUT_DIR = Path("outputs") / "date_cleanup_result"
CLEANED = OUTPUT_DIR / "cleaned_dates.csv"
UNPARSED = OUTPUT_DIR / "unparsed_dates.csv"
DATE_COLUMN = "event_date"

FORMATS = (
    "%Y-%m-%d",
    "%m/%d/%Y",
    "%Y/%m/%d",
    "%d-%m-%Y",
    "%Y.%m.%d",
    "%Y%m%d",
)


def parse_date(value: str) -> str | None:
    text = value.strip()
    for format_string in FORMATS:
        try:
            parsed = datetime.strptime(text, format_string)
        except ValueError:
            continue
        return parsed.strftime("%Y-%m-%d")
    return None


def main() -> None:
    if not SOURCE.is_file():
        raise FileNotFoundError(f"Source CSV not found: {SOURCE}")

    OUTPUT_DIR.parent.mkdir(parents=True, exist_ok=True)
    OUTPUT_DIR.mkdir()  # Stop rather than reuse an existing result folder.

    with SOURCE.open("r", encoding="utf-8-sig", newline="") as source:
        reader = csv.DictReader(source)
        if reader.fieldnames is None:
            raise ValueError("CSV has no header row.")
        if DATE_COLUMN not in reader.fieldnames:
            raise ValueError(f"Missing required column: {DATE_COLUMN}")
        if "standardized_date" in reader.fieldnames or "date_status" in reader.fieldnames:
            raise ValueError("Output column name already exists in source CSV.")

        cleaned_fields = [*reader.fieldnames, "standardized_date", "date_status"]
        review_fields = ["row_number", *reader.fieldnames]

        with CLEANED.open("x", encoding="utf-8", newline="") as cleaned_stream, \
             UNPARSED.open("x", encoding="utf-8", newline="") as review_stream:
            cleaned_writer = csv.DictWriter(cleaned_stream, fieldnames=cleaned_fields)
            review_writer = csv.DictWriter(review_stream, fieldnames=review_fields)
            cleaned_writer.writeheader()
            review_writer.writeheader()

            total = 0
            parsed_count = 0
            unparsed_count = 0

            for row_number, row in enumerate(reader, start=2):
                total += 1
                original_value = row[DATE_COLUMN]
                standardized = parse_date(original_value)

                output_row = dict(row)
                if standardized is None:
                    output_row["standardized_date"] = ""
                    output_row["date_status"] = "UNPARSED"
                    review_writer.writerow({"row_number": row_number, **row})
                    unparsed_count += 1
                else:
                    output_row["standardized_date"] = standardized
                    output_row["date_status"] = "PARSED"
                    parsed_count += 1

                cleaned_writer.writerow(output_row)

    if parsed_count + unparsed_count != total:
        raise RuntimeError("Record counts do not balance.")

    print(f"Processed {total} records.")
    print(f"Parsed: {parsed_count}; unparsed: {unparsed_count}.")
    print(f"Cleaned CSV: {CLEANED.as_posix()}")
    print(f"Review CSV: {UNPARSED.as_posix()}")


if __name__ == "__main__":
    main()

python date_format_cleanup.py

05Compare the expected output files

cleaned_dates.csv should contain the original three columns plus standardized_date and date_status. All 8 original records remain present. The two failed rows are not deleted.

unparsed_dates.csv should contain exactly 2 data rows. Because the original CSV header is physical line 1, R007 is CSV row 8 and R008 is CSV row 9.

06Check counts and console output

The expected console output below is derived by hand from the synthetic input and script. It is not a captured execution log.

Processed 8 records.
Parsed: 6; unparsed: 2.
Cleaned CSV: outputs/date_cleanup_result/cleaned_dates.csv
Review CSV: outputs/date_cleanup_result/unparsed_dates.csv

1. Confirm that cleaned_dates.csv contains 8 data rows rather than only the 6 successful rows.
2. Confirm that standardized dates run from 2026-09-01 through 2026-09-06 for R001 through R006.
3. Confirm that R007 and R008 have empty standardized_date values and UNPARSED status.
4. Confirm that unparsed_dates.csv contains exactly R007 and R008.
5. Run the script again without changing OUTPUT_DIR. It should stop with FileExistsError instead of overwriting the previous result.

07Recognize common errors

Do not respond to a high failure rate by adding many speculative formats. That can turn detectable bad data into silently misinterpreted dates.

08Understand the limits before scaling

This example handles calendar dates only. It does not parse times, time zones, Excel serial dates, localized month names, two-digit years, or values that contain additional descriptive text. Those cases need explicit rules.

datetime.strptime validates calendar dates, so a structurally plausible value such as 2026-02-30 is rejected. That is useful, but a successfully parsed date is not proof that the source value is factually correct. A typo such as 2026-09-12 instead of 2026-09-21 can still be a valid calendar date.

The order of FORMATS matters when two accepted patterns can interpret the same string differently. A production cleanup process should document the source convention, preserve the original value, report failures, and review ambiguous formats rather than relying on parsing alone.

Execution and verification record

ChatGPT Python tool · Python 3.13.5 · standard library csv/datetime/pathlib

• The main run printed exactly: 'Processed 8 records.', 'Parsed: 6; unparsed: 2.', 'Cleaned CSV: outputs/date_cleanup_result/cleaned_dates.csv', and 'Review CSV: outputs/date_cleanup_result/unparsed_dates.csv'.
• cleaned_dates.csv contained all 8 source records plus standardized_date and date_status.
• R001 through R006 were parsed exactly to 2026-09-01 through 2026-09-06.
• R007 value 2026-02-30 was rejected as an invalid calendar date and written with an empty standardized_date and UNPARSED status.
• R008 value 'Sep 7 2026' was rejected because it matches none of the approved formats and was written with an empty standardized_date and UNPARSED status.
• unparsed_dates.csv contained exactly 2 data rows: R007 with row_number 8 and R008 with row_number 9.
• The SHA-256 hash of mixed_dates.csv was unchanged after processing, confirming the source file was not modified.
• A second run with outputs/date_cleanup_result already present raised FileExistsError before overwriting previous results.

References

The explanations and examples were written for this site. See the official sources below for the related behavior and concepts.

• Official Python 3.12 documentation · datetime ↗ (New tab)
• Official Python 3.12 documentation · strftime and strptime behavior ↗ (New tab)
• Official Python 3.12 documentation · csv ↗ (New tab)
• Official Python 3.12 documentation · pathlib ↗ (New tab)