Encoding errors, mixed date formats, duplicated rows and numbers stored as text: a reproducible Pandas cleaning script you can adapt to any export.

Load the file without losing data

Exports from accounting or CRM tools are often encoded in Windows-1252 and use semicolons. Reading them with defaults produces garbled accents or a single column. Specify the separator and encoding explicitly, and read everything as text first so nothing is converted too early.

import pandas as pd

df = pd.read_csv("export.csv", sep=";", encoding="cp1252", dtype=str)
print(df.shape)
print(df.head())

Normalize column names

Column names with spaces, capitals and accents make code fragile. Convert them to snake_case once, at the start of the script.

df.columns = (
    df.columns.str.strip()
    .str.lower()
    .str.normalize("NFKD").str.encode("ascii", "ignore").str.decode("ascii")
    .str.replace(r"[^a-z0-9]+", "_", regex=True)
)

Fix numbers stored as text

European exports write 1 234,56. Remove thousand separators, replace the decimal comma, then convert with errors="coerce" so invalid values become NaN instead of crashing. Always count the NaN produced: a sudden spike tells you the format assumption was wrong.

df["amount"] = pd.to_numeric(
    df["amount"].str.replace("\u00a0", "").str.replace(" ", "").str.replace(",", "."),
    errors="coerce",
)
print(df["amount"].isna().sum(), "invalid amounts")

Parse dates consistently

Mixed formats (12/03/2026 vs 2026-03-12) are the most dangerous issue because a wrong parse looks valid. Use dayfirst=True for European data and inspect the min and max dates to detect inversions.

df["date"] = pd.to_datetime(df["date"], dayfirst=True, errors="coerce")
print(df["date"].min(), df["date"].max())

Remove duplicates and blank rows

Decide which columns define a unique record (for example invoice number) before dropping duplicates; dropping on all columns misses rows that differ only by whitespace. Strip text columns first.

text_cols = df.select_dtypes("object").columns
df[text_cols] = df[text_cols].apply(lambda s: s.str.strip())
df = df.dropna(how="all").drop_duplicates(subset=["invoice_number"])

Export and document

Write the cleaned file as UTF-8 so Excel and Google Sheets open it correctly, and log the number of rows removed at each step. Keeping these counts makes the cleaning auditable when a colleague asks why totals changed. You can paste a description of your own file into FormulAI to get a script tailored to your columns.

df.to_csv("export_clean.csv", index=False, encoding="utf-8-sig")

How to Clean a Messy CSV File with Python Pandas (Step by Step)