All guides
Guide

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.

All guides