How to clean a messy CSV file before importing it

A short, repeatable workflow for small exports from spreadsheets, shops and internal tools.

1. Confirm what the file actually contains

Open a copy of the CSV and inspect the first few rows. Look for the delimiter, the header row and whether every data row has the same number of fields. A file can look correct in a spreadsheet while still containing blank lines, extra spaces or inconsistent rows.

2. Detect the delimiter

Common delimiters include commas, semicolons and tabs. Do not change the delimiter simply because a spreadsheet application displays the file differently. Identify it from the raw text and preserve it during the first preview.

3. Remove predictable noise

Trim whitespace around cells, remove completely empty rows and remove exact duplicate rows when duplicates are not meaningful in your data. Keep a copy of the original export so the cleanup remains reversible at the file level.

4. Review malformed rows

A row with a different number of cells than the header needs attention. It may contain a broken quote, a missing value or a delimiter inside a field. Review these rows before exporting instead of silently forcing them into the table.

5. Preview and export

Check the cleaned header and a few representative records. Then export a new CSV and, if possible, keep a small report of rows removed and rows skipped. This makes the result easier to explain to a colleague or to reproduce later.

Worked example

product,quantity,price
Notebook,2,4.50
Notebook,2,4.50
  Pen  ,1,1.20
, ,

With trimming, empty-row removal and exact-duplicate removal enabled, the duplicate Notebook row and the blank row are removed, while the Pen value is normalized. The result should still be reviewed before import.