How to clean CSV imports in Excel

1) Check the delimiter before opening

Excel assumes comma-separated by default, but many exports use semicolons, tabs, or pipes. Opening with the wrong delimiter jams everything into column A.

• Open the CSV in a plain text editor first and look at what separates values.
• European systems often export with semicolons because commas are used as decimal separators.
• Tab-separated files sometimes use .csv extension — the content matters, not the name.

2) Fix encoding problems

Characters like accented letters, currency symbols, or CJK text break when the encoding is wrong. The most common issue is a UTF-8 file opened as Latin-1, or vice versa.

If you see garbled characters after import, the file was decoded with the wrong charset. Re-import specifying UTF-8 explicitly, or convert the file encoding before opening.

3) Handle quoting and escaping

Fields that contain the delimiter character must be quoted. When quoting is inconsistent, columns shift and rows break:

• A comma inside an address field splits it into two columns if the field is not quoted.
• Embedded newlines inside quoted fields can create phantom rows.
• Double-quotes used as escape characters must be paired — a missing quote corrupts every row after it.

4) Convert to XLSX for a clean working copy

Once the CSV is clean, convert it to XLSX to lock in the structure. XLSX preserves column types, avoids re-parsing issues, and is safer to share with colleagues who might open it in different locale settings.