Clean and prepare your data

“My numbers turned to text when I opened the CSV.”

Decimal commas, semicolons, and locale, and how to import cleanly in R and Python.

A collaborator sends you their data, you open the CSV, and every number is text, or the whole file sits in one column, or the dates are backwards. The data is fine. It was written on a machine whose locale differs from yours, and your reader guessed the separators wrong. The real danger is not the obvious mess; it is the column that looks fine but was read as text, so it is quietly dropped from a calculation, sorted wrong, or half-converted later, and nothing warns you. Name the separators and it imports in one line.

By Heidi Andersén, MD, PhD · Updated 18 July 2026 · 5 min read

Why it happensA comma is a decimal in half of Europe

In much of Europe and the Nordics the decimal separator is a comma: three and a half is written 3,5. That clashes with the comma a CSV uses between fields, so those locales switch the field separator to a semicolon. A US CSV uses a dot for decimals and a comma between fields. Excel follows the machine’s locale silently, so the same file is written and read differently on a Finnish and a US computer. Nothing is corrupt; the reader is just guessing wrong.

The three symptomsWhat each one tells you

Everything in one column. The file is semicolon-separated and your reader expected commas.
Numbers read as text. The file uses decimal commas and your reader read the comma as a thousands mark or a field break.
Dates are wrong or flipped. Day and month are swapped (03/04 is 3 April or 4 March depending on locale), or Excel has already converted a code like a gene name into a date.

Import cleanlyName the separators

# European: semicolon fields, comma decimals
readr::read_csv2("data.csv")
# or state it explicitly:
readr::read_delim("data.csv", delim = ";",
  locale = readr::locale(decimal_mark = ",", grouping_mark = "."))
# US: comma fields, dot decimals
readr::read_csv("data.csv")

Python

import pandas as pd
# European:
pd.read_csv("data.csv", sep=";", decimal=",")
# US:
pd.read_csv("data.csv")

Both are built in. read_csv2() is R’s established reader for semicolon files with comma decimals, and pandas exposes sep and decimal directly, so no add-on is needed.

Check it survivedNever trust the eyeball

After import, confirm that numeric columns are actually numeric (str() in R, df.dtypes in Python), inspect any parsing warnings, and check that a known total is correct. A column read as text may be excluded from calculations, sorted incorrectly, or partially converted later without you noticing. To avoid the problem next time: agree one format with collaborators, export as UTF-8, and do not open the raw data in Excel before analysis, because it converts codes and dates on the way in. If you must open it there, import the columns as text.

ChecklistFour checks

Before you analyse

You know the field separator (comma or semicolon) and the decimal mark (dot or comma).
You read the file with those stated, not the default.
Numeric columns test as numeric, and a known total is correct.
Dates parsed to the right day and month.

Related guidesRead next

This one needs no tool, just the right separators. If the file also came from a survey with stacked headers, start with the two-header-rows guide.