How do I fix a CSV that has every problem at once?

End of the month. Accounting sends you pedidos_sucursal.csv for the report, and it has everything. Before opening it in any program, read it as text, like in module 1:

datos/pedidos_sucursal.csv

folio;fecha;cliente;codigo_postal;sku;cantidad;importe
000101;02/09/2026;PapelerÃa La Ãšnica;44100;00731;40;940,00
000102;05/09/2026;Abarrotes DoÃ±a PeÃ±a;06700;01204;12;1.416,00
000103;03/10/2026;Farmacia San JosÃ©;45640;00090;150;1.485,00
000104;07/10/2026;MiscelÃ¡nea El GÃ¼ero;06700;00731;25;587,50
000105;11/10/2026;PapelerÃa La Ãšnica;44100;09810;2;2.360,00

Synthetic data, CC0. The file, seen as text, without its BOM.

And the file starts with a BOM. Make the list yourself before reading on: how many problems do you see?

There are six, one for each topic in the course:

What you see	What it is	How you tell the reader (DuckDB)
`;` between columns	The separator of a region with a decimal comma	`delim=';'`
`940,00` and `1.416,00`	Decimal comma and thousands point	`decimal_separator=','`, `thousands='.'`
`000101`, `06700`, `00731`	Codes that start with zero	`types={'folio': 'VARCHAR', ...}`
`02/09/2026`	Day/month/year date, with every day from 1 to 12	`dateformat='%d/%m/%Y'`
Three bytes at the start	The BOM	DuckDB removed it on its own in the sandbox
`DoÃ±a PeÃ±a`	Mojibake already saved in the file	Repair it after reading

The last one is different from the rest, and it's the subtlest lesson in the course. In module 1, MÃ©rida appeared because you read a UTF-8 file with the wrong table: changing the reading fixed it. Not here. Somebody in accounting already opened the file with the wrong table, saw DoÃ±a, and saved it that way. Now the file is valid UTF-8 that contains, letter by letter, Ã and ±. No encoding at read time fixes it, because the reading isn't wrong: the text is. You repair it with the recipe from module 1: texto.encode("cp1252").decode("utf-8"), which undoes exactly that mistake.

Look at the file as text
Do the accents look odd?
- Path 1 of 2: not UTF-8
  1. Change the encoding when reading
    if the file isn't UTF-8
  Continues at: Separator, decimals and thousands
- Path 2 of 2: already says Ã in UTF-8
  1. Repair the text after reading
    if it was already saved broken
  Continues at: Separator, decimals and thousands
Separator, decimals and thousands
Codes as text and date format
Check types and totals

The questions in order; each answer becomes a reader option.

DuckDB for reading, in plain sight

In this project you read with DuckDB, inside Python. Its read_csv accepts the same decisions you already know from pandas, under other names, and you write them inside the query. DuckDB guesses too: in the sandbox it detected the semicolon on its own, left the invoice numbers (folio) as text and read the dates correctly. But the amount (importe) stayed as text. And if you give it only decimal_separator=',', without thousands, it leaves it as text without warning; if you also demand that it be a number, it stops with an error that says which line and which value it couldn't convert, 1.416,00. That error helps you: it tells you where to look.

Even if DuckDB guesses the dates or the invoice numbers right, write the options out. The month a different file arrives, your script will keep reading the same way.

If DuckDB gives you a conversion error, paste it to your Maestro and read it together.

Try it, on your own now

The reporte.py script reads the file with no options and repairs the names with a function that doesn't do anything yet. Run it with uv run reporte.py and look at the types and the totals: the amount comes out as text, stuck together, and the customers with garbled characters. Fix it yourself, with no hints in the code: the table above has everything you need. If you get stuck, ask your Maestro for the next step, not for the solution.

Your turn

Run uv run reporte.py. Change only the top part of the script: the read_csv options and the reparar function. You're done when importe is float64, the names read correctly ("Papelería La Única"), sales by month are 9: 2356.0 and 10: 4432.5, and the top customer in sales by customer is Papelería La Única with 3300.0.

Files

datos

import duckdb

def reparar(texto):

"""The names arrived broken: somebody opened the file with the wrong

table and saved it again that way."""

return texto

pedidos = duckdb.sql("""

SELECT *

FROM read_csv('datos/pedidos_sucursal.csv')

""").df()

# --- Don't change anything from here down. ---

pedidos["cliente"] = pedidos["cliente"].map(reparar)

print(pedidos.dtypes.to_string())

print()

print("Sales by month:")

print(pedidos.groupby(pedidos["fecha"].dt.month)["importe"].sum().to_string())

print()

print("Sales by customer:")

print(pedidos.groupby("cliente")["importe"].sum().sort_values(ascending=False).to_string())

Python 3.12· runs with your account

When your report adds up, look at what your read_csv says: each option is an answer to one of the questions in the diagram. Whoever reads your script later knows how the file arrives without opening it.

And one last caution about reparar: use it only on text that is really broken. In the sandbox, applied to a healthy name like Mérida, it fails with UnicodeDecodeError, because there's no mistake to undo. If a file has broken and healthy names mixed together, the repair has to go row by row, and it's a good idea to ask your Maestro to help you write it.

Check yourself: You read a UTF-8 file with the right encoding and the names still say "DoÃ±a PeÃ±a". What happened, and what do you do?

DuckDB: reading CSV files and their options