DEV Community: Mahizul Islam

5 Common CSV Problems and How to Fix Them

Mahizul Islam — Thu, 02 Jul 2026 07:37:08 +0000

5 Common CSV Problems and How to Fix Them

CSV files are everywhere — database exports, spreadsheet
data, API responses, analytics reports. But anyone who
works with CSV regularly knows the frustration: files
that look fine but break on import, columns that won't
split, characters that turn into gibberish.

Here are the 5 most common CSV problems and exactly
how to fix each one.

1. Broken or Garbled Characters (â€™, Ã©, Â£)

What it looks like:
Your CSV opens and instead of apostrophes you see â€™,
instead of é you see Ã©, instead of £ you see Â£.

Why it happens:
Your file was saved in Windows-1252 encoding (Excel's
default on Windows) but opened as UTF-8. The two
encodings interpret the same bytes differently.

How to fix it:

In Python:

import pandas as pd
df = pd.read_csv('file.csv', encoding='windows-1252')
df.to_csv('fixed.csv', encoding='utf-8', index=False)

In Node.js:

const iconv = require('iconv-lite');
const fs = require('fs');
const buf = fs.readFileSync('file.csv');
const text = iconv.decode(buf, 'win1252');
fs.writeFileSync('fixed.csv', text, 'utf8');

No-code option: Use a free CSV to UTF-8 Converter
that auto-detects the encoding and converts instantly
in your browser.

2. All Data Appears in One Column

What it looks like:
You open a CSV in Excel and instead of separate columns,
everything is crammed into column A.

Why it happens:
Your file uses a semicolon, tab, or pipe as the delimiter
instead of a comma. Excel in European countries defaults
to semicolons because commas are used as decimal separators.

How to fix it:

In Excel: Don't double-click the file. Instead go to
Data → From Text/CSV → select your file → choose the
correct delimiter in the preview.

In Python:

import pandas as pd
# Try semicolon delimiter
df = pd.read_csv('file.csv', sep=';')
# Or tab
df = pd.read_csv('file.csv', sep='\t')

No-code option: Use a free CSV Delimiter Changer
to convert between comma, semicolon, tab, and pipe
delimiters instantly.

3. Inconsistent Column Count

What it looks like:
Row 1 has 5 columns, row 47 has 4, row 203 has 6.
Your import fails with an error like "too many values
to unpack."

Why it happens:
Values containing commas weren't properly quoted,
or rows were manually edited and a field was accidentally
deleted or duplicated.

How to fix it:

In Python:

import csv

with open('file.csv') as f:
    reader = csv.reader(f)
    headers = next(reader)
    expected = len(headers)
    for i, row in enumerate(reader, 2):
        if len(row) != expected:
            print(f"Row {i}: {len(row)} columns (expected {expected})")

No-code option: Use a free CSV Validator
that checks column consistency, empty headers,
duplicate rows, and broken quotes with a detailed
line-by-line report.

4. Duplicate Rows

What it looks like:
You import your CSV into a database and get primary
key conflicts, or your analytics show inflated numbers
because the same record appears multiple times.

Why it happens:
Data was exported twice, two files were merged without
deduplication, or a sync process ran multiple times.

How to fix it:

In Python:

import pandas as pd
df = pd.read_csv('file.csv')
df_clean = df.drop_duplicates()
# Or deduplicate on a specific column
df_clean = df.drop_duplicates(subset=['email'])
df_clean.to_csv('clean.csv', index=False)

No-code option: Use a free CSV Duplicate Remover
that supports full-row and column-specific deduplication
with case-insensitive matching.

5. Empty Rows and Trailing Whitespace

What it looks like:
Your row count is wrong, or string comparisons fail
because "Alice" and "Alice " are treated as different
values.

Why it happens:
Exported data often includes empty rows at the end,
and spreadsheet editors sometimes add invisible
trailing spaces.

How to fix it:

In Python:

import pandas as pd
df = pd.read_csv('file.csv')

# Remove empty rows
df = df.dropna(how='all')

# Trim whitespace from all string columns
df = df.apply(lambda x: x.str.strip() if x.dtype == 'object' else x)

df.to_csv('clean.csv', index=False)

No-code option: Use a free CSV Cleaner
that removes empty rows, trims spaces, removes
duplicate rows, and strips non-printable characters
in one click.

Summary

Problem	Quick Fix
Garbled characters	Convert to UTF-8
All data in one column	Change delimiter
Inconsistent columns	Validate before import
Duplicate rows	Deduplicate on key column
Empty rows / whitespace	Clean before processing

All the tools linked in this article are free and
browser-based — your data never leaves your device.
Check out the full CSV Toolkit
for 16 tools covering everything CSV.

How to Fix CSV Encoding Issues (UTF-8, Windows-1252, and More)

Mahizul Islam — Wed, 27 May 2026 12:12:01 +0000

How to Fix CSV Encoding Issues (UTF-8, Windows-1252, and More)

If you've ever opened a CSV file and seen broken characters like â€™ instead of apostrophes, or Ã© instead of é, you've encountered a CSV encoding problem. This is one of the most common issues developers and data analysts face when working with CSV files.

In this guide, I'll explain why encoding issues happen, how to detect them, and how to fix them — without writing a single line of code.

Why Does CSV Encoding Matter?

CSV files don't store information about their encoding. When you open a CSV, your software has to guess which encoding was used. If it guesses wrong, you get garbled text.

The most common culprits:

Windows-1252 — the default encoding for Excel on Windows. Fine for Western European languages, but breaks on special characters from other languages.
ISO-8859-1 (Latin-1) — similar to Windows-1252, commonly used in older systems.
UTF-16 — used by some Windows applications, includes a BOM (Byte Order Mark) at the start.
Shift-JIS, GBK, EUC-KR — common in Japanese, Chinese, and Korean systems respectively.

UTF-8 is the universal standard. Every modern database, API, and web application expects UTF-8. If your CSV isn't UTF-8, you'll run into import errors, broken characters, and data loss.

How to Detect CSV Encoding

Before fixing, you need to know what encoding your file is using. Look out for these signs:

Question marks ? replacing characters — encoding mismatch
Extra invisible characters at the start — this is a BOM (Byte Order Mark)
Import errors in MySQL, PostgreSQL, or MongoDB

You can check your CSV encoding instantly using the free CSV Encoding Checker — it detects UTF-8, Windows-1252, UTF-16, and more directly in your browser without uploading your file anywhere.

How to Fix CSV Encoding

Once you know the encoding, converting to UTF-8 is straightforward.

Option 1: Use a Free Online Tool (No Code)

The easiest way is to use the CSV to UTF-8 Converter. It supports 14 encodings including Windows-1252, ISO-8859-1, Shift-JIS, GBK, and UTF-16. Everything runs in your browser — your file is never uploaded to a server.

Option 2: Python

import pandas as pd

df = pd.read_csv('your-file.csv', encoding='windows-1252')
df.to_csv('fixed-file.csv', encoding='utf-8', index=False)

Option 3: Node.js

const iconv = require('iconv-lite');
const fs = require('fs');

const input = fs.readFileSync('your-file.csv');
const decoded = iconv.decode(input, 'win1252');
fs.writeFileSync('fixed-file.csv', decoded, 'utf8');

Option 4: Excel

Open Excel → Data → From Text/CSV
In the import wizard, change File Origin to 65001: Unicode (UTF-8)
Save as CSV

The UTF-8 BOM Problem

Even after converting to UTF-8, Excel sometimes still shows garbled characters. This is because Excel on Windows needs a BOM (Byte Order Mark) — a hidden 3-byte marker at the start of the file — to recognize UTF-8.

When downloading from the CSV to UTF-8 Converter, the file automatically includes a BOM so Excel opens it correctly every time.

Quick Reference: Common Encoding Issues

Broken text	Original character	Likely encoding
`â€™`	`'` (apostrophe)	Windows-1252
`Ã©`	`é`	Windows-1252
`Â£`	`£`	Windows-1252
`?????`	Japanese/Chinese/Korean	Wrong encoding
Invisible chars at start	(none)	UTF-16 BOM

Summary

Check your encoding with a CSV Encoding Checker
Convert to UTF-8 using Python, Node.js, Excel, or an online converter
Include a UTF-8 BOM if opening in Excel on Windows
Always save exports as UTF-8 to avoid future issues

All the tools mentioned in this article are free and browser-based — your data never leaves your device. Check out the full CSV toolkit for more tools like CSV Validator, CSV Formatter, and CSV Duplicate Remover.