How to clean a CSV before importing it

Cleaning begins with a reversible copy

Do not edit the only export. Preserve the original filename, bytes, export date, source system, applied filters, and expected record count. Create a working copy whose name identifies the intended destination. If the import affects customers, inventory, money, access, research, or regulated records, also record who approved the mapping and how rollback works.

A CSV contains text, not database types, validation rules, relationships, formatting, formulas, or workbook sheets. The destination decides whether 00123 is text or the number 123, whether 03/04/05 is a date, and whether an empty field means blank, zero, null, or “leave unchanged.” Cleaning is therefore partly about the file and partly about the import contract.

A practical CSV cleaning sequence

1. Duplicate the source and record its row count, bytes, delimiter, and encoding.
2. Check that the header is present, unique, stable, and mapped to destination fields.
3. Protect identifiers, long numbers, dates, and codes from automatic type conversion.
4. Inspect malformed rows, unexpected blanks, duplicate records, and formula-like cells.
5. Import a small representative copy, validate it, then reconcile the full result.

Run the steps in that order. Removing rows before you understand parsing can hide a delimiter or quoting problem. Replacing commas before you understand quoted fields can corrupt addresses and descriptions.

Confirm delimiter, quoting, line endings, and encoding

RFC 4180 documents a common CSV shape: records separated by line breaks, fields separated by commas, an optional header, quoted fields when values contain commas or line breaks, and doubled quotes inside quoted fields. Real exports also use semicolons, tabs, and pipes. A parser must understand quoting; splitting each line on every comma is not sufficient.

If an import produces one giant column, select the correct delimiter. If it produces extra columns, inspect a row containing punctuation and confirm the source escaped it correctly. FileGizmo’s conversion tools use a CSV parser and support common delimiters, which makes them useful for a structural preview without uploading the dataset.

Encoding failures appear as replacement characters, mojibake, or missing symbols in names and addresses. UTF-8 is the safest modern default, but a legacy export may use a regional encoding. Do not “fix” corrupted characters by global search and replace until you identify how the bytes were decoded.

Make headers useful and mappings explicit

Every column should have a non-empty, unique header. Prefer stable machine-friendly names such as customer_id, postal_code, and created_at over punctuation-heavy labels. Do not rename a header merely to look nicer if an existing import template expects the original key.

Create a mapping table with source header, destination field, expected type, required status, blank behavior, and transformation. A field named status could mean a customer state in one system and workflow state in another. A written mapping exposes that ambiguity before thousands of rows are committed.

Remove columns only when they are truly unnecessary and retained in the source copy. FileGizmo’s Extract Columns can create a smaller delivery file, while Filter CSV can keep rows matching an explicit condition. Neither tool knows the business meaning of the data, so review the output count.

Preserve identifiers as text

Postal codes, phone numbers, account references, SKUs, government identifiers, tracking numbers, and long card-like values are labels even when they contain only digits. Spreadsheet software can remove leading zeros, convert long values to scientific notation, or truncate precision.

Microsoft’s current guidance recommends importing through Data → From Text/CSV or Power Query when column types need control, then setting identifier columns to Text. It notes that Excel can remove leading zeros and preserve only 15 digits of numeric precision. Formatting a damaged number afterward does not restore discarded digits.

Dates deserve the same caution. 04/05/2026 is ambiguous across locales. Prefer an agreed unambiguous representation such as ISO 2026-05-04 when the destination accepts it, and never transform dates without knowing the source convention and timezone.

Define duplicates before deleting them

An exact duplicate row is a strong candidate for removal. Two rows sharing an email address may represent an accidental duplicate, a household, a reused address, or a historical record. Two transactions with the same amount and date are not necessarily duplicates.

Use Remove Duplicate CSV Rows for exact-row cleanup or a clearly chosen key, then record the input count, removed count, and output count. Review several removed pairs. Keep a rejected-record file when the import process supports one so decisions remain auditable.

Normalize case, whitespace, phone formats, or abbreviations only under an explicit rule. Global replacement can merge distinct values. FileGizmo’s Find and Replace CSV works on chosen columns and reports replacement counts, making a narrow change easier to verify than editing raw text.

Treat formula-like cells as untrusted input

OWASP describes CSV Injection, also called Formula Injection, as the risk created when untrusted values are embedded in CSV and then interpreted as formulas by spreadsheet software. A value beginning with characters recognized by the destination may execute a formula or create a dangerous link when the recipient opens the file.

Do not solve this with one universal prefix copied from a blog. Escaping behavior varies by spreadsheet and downstream importer, and a defensive prefix may become part of the actual data. Identify untrusted columns, follow the destination’s security documentation, disable external content, and test the exported and re-imported value. Never click an unexpected formula-generated link.

Test the destination, not just the preview

Build a small sample containing the header plus representative rows: blanks, commas, quotes, line breaks, Unicode, leading zeros, long identifiers, dates, the longest text, and a deliberate duplicate. Import into a test area or reversible transaction. Confirm field mapping, types, row count, rejected rows, defaults, updates versus inserts, and how the destination handles empty cells.

After the full import, reconcile source data rows, accepted rows, rejected rows, created records, updated records, and skipped records. Those numbers should explain one another. Spot-check records from the beginning, middle, and end, plus every important edge case.

Cleaning a CSV is not making it visually tidy. It is preserving meaning while moving text through a parser and a destination with its own rules. A preserved original, explicit mapping, representative test, and reconciled counts provide more safety than an irreversible “clean data” button.