How to clean a CSV before importing it into a database

A CSV can look fine in a spreadsheet and still fail during a database import. A missing header, mixed date formats, or a value in the wrong column can stop the import or put information in the wrong place.

Work on a copy of the file, check the columns and values, and fix problems before importing. The CSV Editor opens large CSV files in your browser and lets you find, edit, sort, and remove duplicate rows.

Clean the CSV before importing it

1. Open the CSV Editor and load a copy of your CSV file. Keep the original unchanged in case you need to start over.
2. Check the header row. Confirm that each column has a clear name, that names are not blank or repeated, and that each one matches a field you expect in the database.
3. Look down each column for blanks, inconsistent spelling, unexpected spaces, and values that do not belong there. Use find to locate repeated problem values, then edit them consistently.
4. Review possible duplicates before removing any. Remove rows only when they represent the same record for your purpose, not just because some of their values match.
5. Sort a column when it helps you spot unusual values, then review the affected rows. Save the cleaned file as a new CSV and check that copy before importing it.

Do not assume that sorting is required. It is a way to make patterns and outliers easier to see. The right cleanup depends on the rules for the database table you are importing into.

What a clean result looks like

The headers match the database fields

Each column should have one clear header, such as email, signup_date, or order_total. Check the import instructions for the destination database, because some systems require exact field names and others let you map CSV columns to fields during import. Avoid relying on column order alone if the import tool offers a way to match columns by name.

Each column uses consistent values

A database usually expects a field to contain one kind of value. A date column should not mix values like 03/08/2025, March 8, 2025, and not known. A number column should not mix numbers with text such as about 20. Choose a format that the destination database accepts and use it throughout the column.

Blank and missing values are intentional

A blank cell may mean the value is unknown, not applicable, or accidentally missing. Those meanings are not interchangeable in every database. Check whether the destination expects an empty value, a specific marker such as NULL, or a value in every row. Do not fill blanks with guesses.

Rows represent the records you intend to import

Remove repeated rows only if they are duplicates under your rules. Two people can share a name, and two orders can have the same date and total. A repeated email address may be an error in one table and valid in another. Compare the fields that identify a record before deciding.

Values stay in the right columns

Check rows with names, addresses, notes, or other text that may contain commas. A correctly formatted CSV can include commas inside quoted fields, so a comma inside a sentence does not automatically mean the row is broken. Look at how the values appear in the editor, and check any suspicious row before importing it.

Fix common CSV problems

1. Keep a backup copy. Save the original file separately before editing. If a cleanup removes valid rows or changes values you need, you can return to the source.
2. Make column names unique and clear. Rename blank or repeated headers so each one identifies a different field. Confirm that your chosen names are accepted by the database, especially if it has reserved words or naming rules.
3. Standardize dates and numbers. Pick one date format the importer accepts and apply it throughout the column. Use plain numbers without currency symbols or explanatory words if the field is numeric.
4. Preserve identifiers as text. Postal codes, account numbers, and product codes may contain leading zeros. If those zeros matter, do not treat the values as ordinary numbers. Check the destination field type and import options before changing them.
5. Use one spelling for repeated categories. Values such as NY, New York, and new york may be treated as different values. Decide which version to keep, find the variations, and edit them consistently.
6. Check required fields. If the database requires a value in a field, find rows where it is missing. Get the correct value from a reliable source or remove the record only if your import rules allow it.
7. Import a small sample first. If the database allows it, try a few representative rows before loading the full file. Include rows with long text, blank values, dates, and leading zeros so you can catch formatting problems early.

Know what the CSV Editor can and cannot check

The CSV Editor can help you inspect and edit rows, find values, sort data, and remove duplicates. It can open CSV files up to 10 million rows, which is useful when a regular spreadsheet struggles with a large file.

It cannot know the rules of your database table unless you check those rules yourself. It does not tell you whether a value meets a database constraint, whether a field is required, or whether a date format is accepted by a particular import process. Verify those details in the database's import settings or schema before you load the full file.

Once the cleaned copy is ready, open it again and check the headers and a few rows near the beginning, middle, and end. The CSV file you open and edit stays on your device.