CSV files: the most common problems and how to fix them first

CSV is the most widespread exchange format for tabular data: business software, banks, e-commerce sites and spreadsheets all export it. It is simple, readable in any text editor and accepted by any database. Its very simplicity, however, leaves room for many variants and ambiguities. A file that looks perfect can hide errors that are only discovered after the import, when the data has already made its way into reports.

The separator: comma, semicolon or tab

CSV stands for comma-separated values. In countries where the comma is the decimal separator, such as Italy, many programs use a semicolon instead, to avoid 12,50 being split into two columns. The same spreadsheet exported on two computers with different regional settings can therefore produce two different files.

Before importing, it is worth opening the file in a text editor and looking at the first lines. The separator is recognisable at a glance. Serious import tools always let you specify it explicitly, and it is better to do so than to rely on automatic detection.

Quotes and fields containing the separator

A field containing the separator, a line break or quotes must be enclosed in double quotes, and any quotes inside it must be doubled:

code;description;price
A12;"USB cable; 2 m long";8,90
B07;"24"" Full HD monitor";149,00

This is the convention described in RFC 4180, the most frequently cited reference document for the format. Many programs that generate CSV follow it only partly. A file whose number of columns varies from row to row almost always indicates a field containing the separator that was not quoted properly.

A quick check in Python:

import csv
from collections import Counter

with open("items.csv", encoding="utf-8", newline="") as f:
    counts = Counter(len(row) for row in csv.reader(f, delimiter=";"))
print(counts)  # ideally a single value

Character encoding

Accented letters are the first casualty. If a file shows sequences such as «città » instead of «città», the file is encoded in UTF-8 but is being read as though it were in a Western encoding such as Windows-1252. The reverse case produces replacement characters or read errors.

Practical rules:

• always ask for and produce files in UTF-8;
• if a file will not read as UTF-8, try Windows-1252 or ISO-8859-1, which are common in older European business software;
• beware of the BOM, an invisible sequence at the start of some UTF-8 files: it can end up stuck to the name of the first column, which then differs from the expected name. In Python the utf-8-sig encoding removes it automatically.

Leading zeros and long numbers

Double-clicking a CSV to open it in a spreadsheet is the quickest way to ruin it. The program interprets the values and, on saving, writes back its interpretation:

• the postcode 00184 becomes 184;
• a 16-digit code or a card number may be shown in scientific notation and lose its last digits;
• a product code such as 3-4 or MAR1 can be turned into a date.

Codes are not numbers, even when made of digits: they are not added up or compared as quantities. On import they should always be read as text. With pandas:

import pandas as pd

df = pd.read_csv("customers.csv", sep=";", dtype={"postcode": str, "customer_code": str})

If the file needs to be viewed in a spreadsheet, it is better to use the guided import feature, setting code columns to text.

Numbers with local separators

1.250,50 in Italian format corresponds to 1,250.50 in English format. An importer expecting a decimal point will read the first as text or, worse, as a wrong number. Pandas accepts the parameters decimal="," and thousands="." to handle the Italian format.

Ambiguous dates

03/04/2025 is 3 April in Italy and 4 March in the United States. If every day in the file is 12 or less, there is no way to tell from the content. You have to ask whoever produced the file, or check against a known record. The ISO format 2025-04-03 removes the ambiguity and is the one to prefer for any file you produce yourself.

Spaces, blank lines and «fake» values

Other frequent defects:

• spaces before or after values, which make "Rossi" and "Rossi " different;
• blank lines or total rows at the bottom of the file;
• headers repeated halfway through, when the CSV was stitched together from several exports;
• conventional values signalling absence, such as N/A, -, 0 or 1900-01-01, to be converted into genuinely empty values.

A five-step method

1. Open the file in a text editor and check the separator, encoding and header.
2. Count the columns on each row and isolate the anomalous ones.
3. Import everything as text into a staging table.
4. Convert types, dates and numbers with queries or scripts, logging the rows that fail to convert.
5. Compare counts and totals with the source system before moving the data into the final tables.

In short

• The separator changes with regional settings: check it and specify it explicitly.
• Fields with separators or line breaks must be quoted; varying column counts signal an error.
• Garbled accented letters indicate a wrong encoding; the BOM can alter the first column.
• Codes, postcodes and long numbers should be read as text; dates must be clarified or written in ISO format.
• It is best to import into a staging table and convert afterwards.