Why does my CSV look wrong?
Answer four office situations with CSV files and find out where you're starting from.
Free course. Your Maestro, an AI, .
You work in the back office at Distribuidora El Nopal, a stationery and grocery distributor in Guadalajara that supplies stores all over Mexico. Every week CSV files land in your inbox: the orders exported by the branch system, a price list from a supplier in Valencia, the report accounting forwards you. And every week one of them looks wrong: accents turned into gibberish, everything crammed into one column, codes that lost their leading zeros, dates that jumped to another month.
Before any explanation, four situations from those weeks. Go with your best guess, without looking anything up. Your answer is marked as soon as you send it, and your Maestro explains why. Getting it wrong here is useful: these questions show where you're starting from, and at the end of the course they come back in different words so you can see how much your way of reading a file has changed.
Distribuidora El Nopal, its customers and every file in the course are made up: synthetic data, CC0.
Check yourself: You double-click clientes.csv. Where it should say Mérida, Excel shows Mérida, and where it said Peña, it shows Peña. What happened?
Check yourself: In the order system, a customer has the postal code 06700. You export the orders to CSV, open the file in Excel and the cell says 6700. Where did the zero go?
The first two have the same root, and it's the idea the whole course stands on: a CSV is text and nothing else. It doesn't record that a column holds numbers or dates, or how a cell is formatted. It stores letters, commas and line breaks, turned into bytes by a rule called an encoding. What you see in Excel is what Excel decided those bytes meant when it opened the file.
That's why almost nothing that looks wrong is actually broken. "Mérida" is "Mérida" read with the wrong rule; "6700" is "06700" that Excel turned into a number when it opened the file. The file is almost always fine; what went wrong was the reading. What does break a file is what comes after: saving Excel's interpretation over the original and sending it on.
Have you gotten a file like this at work? Tell your Maestro what you saw. It can tell you which page of the course covers your case.
Two more situations.
Check yourself: Your supplier in Valencia sends you lista_precios.csv. You open it as text and see lines like this: 00731;Cuaderno profesional;23,50. What are you looking at?
Check yourself: How does a program know which encoding (UTF-8, Windows-1252…) a CSV was written in?
Your four answers already give you the map of the course:
- In module 1 you open a CSV as what it is, text, and look at the bytes behind an "é": why UTF-8 and Windows-1252 store it differently and where "Mérida" comes from.
- In module 2 you read the Valencia price list, with semicolons and decimal commas (the way files from much of Latin America and Europe, or from any computer with those regional settings, arrive), and meet the BOM, three invisible bytes at the start of some files.
- In module 3 you guard against what Excel and pandas change without telling you: leading zeros, long numbers and dates.
- In module 4 you open and export from Excel without losing anything, and fix a file that has every problem at once.
The exercises run right on the course page, with Python, pandas and DuckDB already installed: there's nothing to install on your computer. You don't need to know how to program to start; when it's time to write code, you change a line or two and see what happens. If an answer surprised you, ask your Maestro before moving on. If not, let's look inside a CSV.