How to clean and deduplicate a CSV with ChatGPT and verify the counts

ChatGPT can clean a messy CSV file inside its data analysis environment: according to OpenAI's help center, it writes and runs Python with pandas on the uploaded file, and it can identify and repair duplicate rows, missing values, outliers and incorrect data types. The cleaned table can then be downloaded, and OpenAI notes that the downloaded file is always in CSV format. For a spreadsheet or CSV upload, OpenAI documents a size ceiling of approximately 50 MB, depending on the size of each row, inside a hard limit of 512 MB per file.

Cleaning is the part of an analysis where an AI assistant saves the most time and also where it can do the most quiet damage. A deduplication step that matches on the wrong key, a date column parsed day-first instead of month-first, or a currency column with a dollar sign that silently turns into text can change every number downstream, from customer counts to monthly revenue. The output still looks like a clean table, so the error rarely announces itself.

The workflow below uses a small illustrative file of twelve customer sign-ups with the problems analysts meet every week. It gives the prompts to send, the rules ChatGPT should apply, and the correct result computed in Python beforehand: twelve raw rows, one exact duplicate, eight unique customers after email normalization, and a monthly fee total of $1,404.00 once each customer is counted once.

··········

WHAT CSV CLEANING AND DEDUPLICATION MEANS FOR ANALYSTS.

A CSV exported from a CRM, a form tool or a billing system usually carries several kinds of noise at once. The same customer appears twice because an email was typed in capitals or with a trailing space, a state is written as "California" in one row and "CA" in another, dates arrive in both ISO and US formats, and amounts include dollar signs and thousands separators that make the column text instead of numbers.

Deduplication is the step with the largest effect on results. Removing rows that are identical in every column is easy, and pandas does it with a single call. The harder and more frequent case is two records for the same person that differ in formatting or in content, such as an older and a newer sign-up for the same email. Those rows are invisible to an exact-match check, so the cleaning rules must first normalize the matching key and then decide which record survives.

Analysts need this before any count, join or dashboard refresh. A customer table with hidden duplicates inflates active-customer metrics and, once joined to a fact table, multiplies revenue, the same fan-out problem covered in our guide to writing SQL with Claude and catching fan-out and date errors.

··········

CHATGPT DATA ANALYSIS LIMITS FOR CSV FILES.

The facts below come from OpenAI's help center articles on data analysis with ChatGPT and on file uploads, as published on November 10, 2026. The data analysis article does not list which ChatGPT plans include the feature, so plan availability should be checked in the account or workspace settings.

........

Item	What OpenAI documents
Supported data files	Excel (.xls, .xlsx), CSV, PDF and JSON
CSV or spreadsheet size	Approximately 50 MB, depending on row size
Hard limit per file	512 MB
Files per conversation	Up to 10 for data analysis
How analysis runs	Python with pandas in a code execution environment
Network access	No direct outbound network requests
Environment lifetime	Destroyed within 13 hours of the conversation becoming inactive
Cleaned table download	Download button on the generated table, CSV format
Free plan uploads	Up to 3 files per day

........

Two details shape the cleaning workflow. ChatGPT reads the first few rows of a file to infer its structure, so a header row with clear, plain column names helps it pick the right types. OpenAI also recommends one row per record, with no multiple tables on one sheet and no empty rows or columns. Any code ChatGPT ran can be inspected through the "View Analysis" link at the end of a response, and the "Always show details" setting displays it by default.

Earlier coverage of how ChatGPT reads uploaded tables is collected in our explainer on whether ChatGPT can analyze CSV files, with parsing, calculations and error handling.

··········

AN ILLUSTRATIVE MESSY CSV FOR THE WORKED EXAMPLE.

The file below is illustrative and was built for this example. It holds twelve sign-ups with six columns: email, name, state, signup_date, plan and monthly_fee. Row 2 starts with a space before the email, row 10 ends with one, and two cells are empty.

........

Row	email	name	state	signup_date	plan	monthly_fee
1	ana.lopez@example.com	Ana Lopez	CA	2026-03-02	Pro	$49.00
2	ANA.LOPEZ@example.com	ana lopez	California	03/02/2026	Pro	49
3	ben.ito@example.com	Ben Ito	NY	2026-04-15	Basic	$19.00
4	ben.ito@example.com	Ben Ito	NY	2026-04-15	Basic	$19.00
5	carla.m@example.com	Carla Mendes	TX	2026-05-01	Team	$1,200.00
6	dev.patel@example.com	Dev Patel	WA	05/20/2026	Pro	49.00
7	carla.m@example.com	Carla Mendes	Texas	2026-06-10	Team	1200
8	eve.kim@example.com	Eve Kim	(blank)	2026-06-18	Basic	$19.00
9	frank.o@example.com	Frank Osei	IL	2026-07-04	(blank)	(blank)
10	dev.patel@example.com	DEV PATEL	wa	2026-05-20	Pro	$49.00
11	gina.r@example.com	Gina Russo	FL	2026-07-22	Pro	$49.00
12	hal.n@example.com	Hal Novak	OR	2026-08-09	Basic	19

........

The file contains every common defect in a compact form. Rows 3 and 4 are exact duplicates. Rows 1 and 2 describe the same customer with different capitalization, a leading space, a full state name and a US-format date. Rows 6 and 10 repeat Dev Patel with a trailing space and a lowercase state. Rows 5 and 7 are two genuine records for Carla Mendes, an earlier and a later sign-up, so a rule is needed to decide which one represents the customer. Row 8 lacks a state and row 9 lacks a plan and a fee.

··········

PROMPTS FOR CLEANING THE CSV IN CHATGPT.

The first prompt asks for a diagnosis before any change, which follows OpenAI's own suggestion to start by asking ChatGPT to check the data for common issues and then to fix them.

I uploaded signups.csv. Before changing anything, check this data for common issues.
Report: the number of rows, exact duplicate rows, likely duplicates after trimming
spaces and lowercasing the email column, inconsistent state values, date formats found
in signup_date, non-numeric values in monthly_fee, and empty cells per column.
Do not modify the file yet.

The second prompt states the cleaning rules explicitly. Leaving the rules to the model is the most common reason two runs on the same file produce different results, so every decision is written down: the matching key, the date convention, which duplicate survives and how missing values are treated.

Now clean the file with these rules and show the pandas code:
1. Read every column as text first.
2. email: trim spaces and convert to lowercase. This is the deduplication key.
3. name: trim spaces and convert to title case.
4. state: trim spaces; map full names to two-letter codes (California -> CA,
   Texas -> TX); uppercase the rest; leave empty values as missing.
5. signup_date: dates with slashes are US format MM/DD/YYYY; convert all to YYYY-MM-DD.
6. monthly_fee: remove $ and commas, convert to a number; empty stays missing.
7. Keep one row per email: the row with the latest signup_date.
8. Do not fill or guess missing values.
Report the row count before and after each step, then let me download the result as CSV.

A third prompt is useful once the table is ready: asking ChatGPT whether anything in the result looks unusual, a check OpenAI also proposes in its help center, before the file goes into a report.

··········

EXPECTED RESULT OF THE CSV DEDUPLICATION.

The correct output of the rules above was computed in Python with pandas before writing. Twelve raw rows contain one exact duplicate, so a plain exact-match deduplication leaves eleven rows. Normalizing the email column reveals eight unique customers, and keeping the latest sign-up per email selects the June 10, 2026 record for Carla Mendes.

........

email	name	state	signup_date	plan	monthly_fee
ana.lopez@example.com	Ana Lopez	CA	2026-03-02	Pro	49.00
ben.ito@example.com	Ben Ito	NY	2026-04-15	Basic	19.00
carla.m@example.com	Carla Mendes	TX	2026-06-10	Team	1200.00
dev.patel@example.com	Dev Patel	WA	2026-05-20	Pro	49.00
eve.kim@example.com	Eve Kim	(missing)	2026-06-18	Basic	19.00
frank.o@example.com	Frank Osei	IL	2026-07-04	(missing)	(missing)
gina.r@example.com	Gina Russo	FL	2026-07-22	Pro	49.00
hal.n@example.com	Hal Novak	OR	2026-08-09	Basic	19.00

........

The control figures are the fastest way to judge ChatGPT's answer without reading every row. If any of them differ, one of the cleaning rules was not applied as written.

........

Check	Correct value
Raw rows	12
Exact duplicate rows	1
Rows after exact deduplication only	11
Unique customers after email normalization	8
Missing state after cleaning	1 (Eve Kim)
Missing plan and fee after cleaning	1 (Frank Osei)
Monthly fee total, deduplicated	$1,404.00
Monthly fee total, raw rows	$2,721.00
Monthly fee total, exact deduplication only	$2,702.00

........

The gap between $1,404.00 and $2,702.00 shows the cost of stopping at exact duplicates: the second Carla Mendes record alone adds $1,200.00, and the near-duplicates for Ana Lopez and Dev Patel add $98.00. The reference code below produces the expected table and runs on pandas 2.0 or later, which added the mixed date format option.

import pandas as pd

df = pd.read_csv("signups.csv", dtype=str, keep_default_na=False)
print(len(df), int(df.duplicated().sum()))  # 12 rows, 1 exact duplicate

states = {"california": "CA", "texas": "TX"}
df["email"] = df["email"].str.strip().str.lower()
df["name"] = df["name"].str.strip().str.title()
df["state"] = df["state"].str.strip().map(
    lambda s: states.get(s.lower(), s.upper()) if s else pd.NA)
df["signup_date"] = pd.to_datetime(df["signup_date"].str.strip(),
                                   format="mixed", dayfirst=False)
df["plan"] = df["plan"].replace("", pd.NA)
df["monthly_fee"] = pd.to_numeric(
    df["monthly_fee"].str.replace(r"[$,]", "", regex=True).replace("", pd.NA))

clean = (df.sort_values("signup_date", kind="stable")
           .drop_duplicates(subset="email", keep="last")
           .sort_values("email")
           .reset_index(drop=True))

print(len(clean))                   # 8
print(clean["monthly_fee"].sum())   # 1404.0

Comparing this script with the code shown under "View Analysis" is a quick review: the deduplication key, the keep rule and the date setting should match line for line in intent, even if ChatGPT names variables differently.

··········

WHERE CHATGPT CSV CLEANING GOES WRONG AND HOW TO CHECK IT.

The first failure is deduplicating on the full row. A call such as drop_duplicates with no subset removes only the exact copy in rows 3 and 4, returns eleven rows and reports the job as done. The row count after deduplication is the check: in the example it must be eight.

The second failure is the date convention. The value 03/02/2026 is March 2 in US format and February 3 in day-first format. In this file the choice does not change which records survive, but in a real export it can reorder sign-ups and change which duplicate is kept as the latest. The prompt should always state the format, and the result should be spot-checked against two or three source rows.

The third failure is numeric conversion. If the dollar sign and the thousands separator are not removed first, "$1,200.00" either stays text or becomes missing, and the fee total drops by $1,200.00 without any error message. Checking the count of missing values in monthly_fee before and after conversion catches it: the only missing fee should be Frank Osei's.

The fourth failure is filling gaps. Asked to "clean" the file without constraints, a model may infer a state from a name or set a missing fee to the plan's usual price. Those values look plausible and are invented. Rule 8 in the prompt forbids it, and the expected table keeps both gaps visible for a human to resolve with the source system.

Business decisions stay with the analyst. Whether the latest or the earliest record represents a customer, whether a changed email means a new customer and whether a duplicate should be merged in the source CRM are rules the data owner sets. Broader workflows that combine upload, cleaning, modeling and charts are described in our overview of ChatGPT advanced data analysis from upload to visualization, and the same deduplication rules apply when many exports are combined, as in our walkthrough on merging dozens of CSVs into a single master sheet with ChatGPT.

··········

CHATGPT FILE HANDLING, RETENTION AND DATA GOVERNANCE.

OpenAI describes the code execution environment as secure and isolated from the rest of its platform, without direct outbound network access. The environment is created by the first analysis in a conversation, is accessible only within that conversation and is destroyed within 13 hours of the conversation becoming inactive. A cleaning session left idle overnight may therefore need the file uploaded again.

Uploaded files follow a different rule from the environment. OpenAI's file upload documentation says files used with data analysis are retained for a duration that varies by plan, files in ChatGPT are saved up to the retention period of the corresponding chat, and deleted files are removed from OpenAI's systems within 30 days. Storage is capped at 10 GB per end user and 100 GB per organization.

Customer exports usually contain personal data such as names and email addresses. Before uploading, teams should confirm that their ChatGPT plan and workspace settings are approved for that data under internal policy, and consider replacing direct identifiers with internal IDs when the cleaning rules do not depend on them.

··········

CLEANING A CSV WITH CHATGPT: WHAT THE DOCUMENTATION CONFIRMS.

OpenAI's help center confirms the essentials of the workflow: ChatGPT runs pandas code on uploaded CSV files, it can identify and repair duplicates, missing values and wrong data types, the code is visible through "View Analysis", and the cleaned table downloads as a CSV. The documented ceiling for a CSV is approximately 50 MB depending on row size, with up to 10 files per data analysis conversation.

What depends on the account is plan availability, which the data analysis article does not list, and file retention, which OpenAI says varies by plan. What the documentation does not provide is any guarantee that the cleaning rules will be inferred correctly, which is why the rules belong in the prompt and the control figures belong in the review.

For the illustrative file, a correct run returns eight customers out of twelve rows and a deduplicated monthly fee total of $1,404.00, with one missing state and one missing fee left visible. A result of eleven rows or a total above $1,404.00 means the near-duplicates were kept; a total of $204.00 means the $1,200.00 fee was lost in conversion.

··········

FOLLOW US FOR MORE.

·····

DATA STUDIOS

·····

datastudios.org