How to Clean a CSV Before Importing It: Dates, Phones, Spellings and Duplicates
Profile the columns first, then make each one consistent: ISO dates, phone numbers with a country code, plain numbers, one spelling per value and one row per record, keeping a log of every change.
Fastest way
Ask your AI to do it
Paste this into the AI agent you already use:
Help me use a Skill with another agent. Ask which agents and package I use, check supported locations and tools, identify any assumptions to change, and verify the workflow with a small example.
Quick answer
Most import errors come from the same few problems: dates in several formats, phone numbers with and without a country code, amounts with currency signs, the same value spelled three ways, and people entered twice. Fix them column by column, never by hand in the original file, and keep a record of what changed.
A safe order
- Profile: what each column holds, how full it is, and example values.
- Decide ambiguous dates: is 03/04/2024 the 3rd of April or March 4th? Any part over 12 in the column tells you.
- Standardise: dates to YYYY-MM-DD, phones to +country code, numbers without symbols, yes/no to true/false.
- Merge spellings only with approval, then remove duplicates by the columns that identify a record.
Keep the evidence
Write the cleaned data to a new file and keep a change log with the line, column, old value and new value. When someone asks why a record looks different, the answer is one filter away, and any rule can be undone.
Do it with an AI agent
CleanSheet profiles the CSV, standardises dates, phones, amounts, emails and yes/no values, merges spellings and removes duplicates, and logs every change by line and column with a list of values it could not clean.