Skip to content
Why Your CSV Looks Wrong: Garbled Accents, Semicolons, Lost Zeros and Dates
Start free

What is inside a CSV file?

Open a CSV as text, see where each row splits, and find out why Excel shows you its interpretation of the file, not the file itself.

Free course. Your Maestro, an AI, .

A CSV (short for comma-separated values) stores a table in the simplest way there is: one row per line, a separator between columns and, almost always, a first line with the column names. That's all. There are no cells, colors or formulas, and nothing that says one column holds dates and another holds money.

This is the orders file that El Nopal's branch system exported, exactly as it's written:

datos/pedidos.csv
folio,fecha,cliente,ciudad,codigo_postal,sku,cantidad,precio_unitario
A-101,03/09/2026,Papelería La Única,Mérida,97000,00731,40,23.50
A-102,05/09/2026,"Abarrotes Doña Peña, S.A.",Ciudad de México,06700,01204,12,118.00
A-103,01/10/2026,Miscelánea El Güero,León,37000,00090,25,9.90
Synthetic data, CC0. This is how the file looks in a text editor, or with cat in a terminal.

Read it the way a program would, left to right and line by line. Three things are worth noticing right away:

  1. A separator between columns

    Here it's the comma. The first line has eight names separated by seven commas, so every row should have eight values. Other files use semicolons, tabs or a vertical bar: the file doesn't announce it, you see it in the text.

  2. Quotes around text that contains the separator

    The customer "Abarrotes Doña Peña, S.A." has a comma inside its name. The quotes say "this comma is part of the text, it doesn't separate columns." Excel and pandas understand them; a program that just cuts at every comma doesn't.

  3. Values written as-is

    06700, 00731 and 23.50 are written with their leading zero and their decimal point, as text. Nothing in the file says 06700 is a postal code and not a number, or that 03/09/2026 is September 3 and not March 9.

That third one is the one that matters. When Excel opens this file it builds a table and, along the way, decides what each value is. Microsoft puts it this way in its help: when Excel opens a .csv file, it "uses the current default data format settings to interpret how to import each column of data." What you see in the sheet is that interpretation. The file doesn't change unless you save it; in Microsoft's words, "Opening a text file in Excel does not change the format of the file."

So when a CSV looks wrong, the first question isn't "what's in the cell?" but "what does the file say?" And to find out, you have to look at it as text.

Try it

This project has the orders file, the price list from the Valencia supplier and a script, ver.py, that prints each line split into its fields. In the terminal, first type cat datos/pedidos.csv to see the file as it is, then uv run ver.py.

Your turn

Run uv run ver.py and read how many fields come out on each line of datos/pedidos.csv. Then change ARCHIVO so it reads datos/lista_precios.csv, and change SEPARADOR to the character that separates the columns in that file (look at it first with cat datos/lista_precios.csv). You're done when every line of the price list comes out with 4 fields.

    Python 3.12· runs with your account

    Two things should have jumped out at you.

    In the orders, the Abarrotes Doña Peña line comes out with one field too many: split cuts at every comma, including the one inside the quotes. Excel and pandas don't make that mistake because they understand the quotes. If you don't see why that line comes out different, ask your Maestro. The takeaway: to really read a CSV you use a CSV reader, not split; here we use it only to look at the file.

    In the price list, the separator is the semicolon, and the prices use a decimal comma: 23,50. If you had left the comma as the separator, every price would have been split in two. And the first field of the first line isn't plain sku: Python prints an escape in front of it, a backslash followed by ufeff, which stands for an invisible character. That character is called a BOM, and it gets its own page in module 2.

    The habit to take with you: when a file looks wrong, look at it as text before you touch anything. On your computer you can open it in any plain-text editor, or with cat in a terminal. In five seconds you know which separator it uses, whether it has quotes, how it writes decimals and whether the codes kept their zeros. Not sure what to open a CSV with as text on your computer? Ask your Maestro.

    Check yourself: Which of these can a CSV file store?

    Microsoft: Import or export text (.txt or .csv) files (opens in a new tab)