Why does my CSV use semicolons?
Find out why a file separates with semicolons where the comma is the decimal mark, and learn to read its separator and decimal mark from the text.
Free course. Your Maestro, an AI, .
The price list from the Valencia supplier arrives like this when you open it as text:
sku;descripcion;precio
00731;Cuaderno profesional;23,50
01204;Plumas azules (caja);118,00
00090;Goma blanca;9,90A CSV, in theory, separates with commas. But here the comma already has a job: it's the decimal mark. 23,50 means twenty-three euros and fifty cents. If the file also separated columns with commas, every price would be split into two columns and nobody could read it. So the program that wrote it picked another separator: the semicolon.
If your own computer is set to U.S. formats you may never produce a file like this, but you will receive them: from suppliers in Europe and much of Latin America, and from any colleague whose regional settings use a decimal comma.
Who decides the separator
On Windows, the separator Excel uses when it saves a .csv comes from the computer's regional settings. Microsoft explains it in its help: "When you save a workbook as a .csv file, the default list separator (delimiter) is a comma. You can change this to another separator character using Windows Region settings." And it adds the part that explains this file: if the decimal separator is a comma, "this forces Excel to use a semi-colon for the list separator." On a computer set up to write 23,50, Excel saves CSVs with semicolons.
Neither one is wrong. They're two conventions, and the file doesn't say which it uses: you see it in the text. What is wrong is reading it with the wrong convention. Predict what pandas does, since by default it expects commas and a decimal point:
pandas with no instructions
Example 1 of 3
import pandas as pd
with open("lista_precios.csv", "w", encoding="utf-8") as archivo:
archivo.write("sku;descripcion;precio\n00731;Cuaderno profesional;23,50\n01204;Plumas azules (caja);118,00\n00090;Goma blanca;9,90\n")
tabla = pd.read_csv("lista_precios.csv")
print(tabla.shape)
print(tabla.columns.tolist())
A guess is fine: it's kept as you wrote it, so you can compare. Only you and your Maestro see your predictions.
Example 2 of 3, opens once you explain this one
The first case is the one that hurts most at work: pandas didn't complain. It handed back a table with the wrong shape and moved on. If you ever double-click a CSV and see each whole line crammed into column A, suspect the same thing: the file uses a different separator than the program assumed. The file is fine; it was read with other rules.
How to read a file's rules
Before opening it in any program, look at the first lines as text and answer three questions:
What separates the columns?
Look at the header: the column names are separated by the same character as the values. It could be a comma, a semicolon, a tab or a vertical bar
|. Count: if the header has four names, each row should split into four values with that character.How does it write decimals?
Find a column of money or measurements.
23,50uses a decimal comma;23.50, a point. If the separator is a semicolon, the comma is almost certainly decimal.Does it group thousands?
A value like
1.180,00uses the period to group thousands and the comma for decimals.1,180.00is the other way around. If you don't tell the program, it will read it as text or, worse, as a different number.
With those three answers you know what to tell any program. In pandas and DuckDB the options are named differently, but they're the same three decisions:
| What you saw | pandas (read_csv) | DuckDB (read_csv) |
|---|---|---|
; separator | sep=";" | delim=';' |
| Decimal comma | decimal="," | decimal_separator=',' |
| Period for thousands | thousands="." | thousands='.' |
DuckDB also accepts sep as another name for delim. One detail of its own: thousands has to be a single character and different from the decimal_separator.
In Excel, you give those answers when you import the file, as you'll see in module 4. If you have a work file handy, paste its first lines to your Maestro. Remove any personal data first.
A common temptation is to change Windows' regional settings so Excel reads the supplier's file. Microsoft warns that the change is global: "Changing the Windows setting will cause a global change on your computer, affecting all applications." To read a file you don't need to touch anything in the system: it's enough to tell the program what rules that file uses.