CSV — When It's Fine and When It Lies

CSV is the format everyone has and nobody owns

Comma-separated values is the most universal data interchange format on Earth. Every tool reads it; every analyst can open it; it doesn't even have a real spec (RFC 4180 is a description after the fact, not a binding standard). That universality is exactly why CSV is also the format that produces the most silent data corruption per-byte of any format you'll work with.

What CSV doesn't carry

Types. Everything is a string on disk. Whoever reads it has to guess: was 123 an integer? 00123 with a leading zero (a US ZIP code)? Was 2026-04-30 a date or a string?
Encoding. The character encoding is not stored in the file. UTF-8, Latin-1, Windows-1252 — all valid CSV. Open the wrong way and accents become mojibake.
Schema. Header row is optional. Column order is the only contract. Adding a column upstream silently breaks downstream code that referenced positions.
Quoting rules. A comma inside a value needs quoting. A quote inside a quoted value needs escaping. Different writers disagree on the details.
Line endings. \n vs \r\n vs \r. Excel on Windows produces one; pandas on Linux produces another. Mostly fine; sometimes catastrophic.

When CSV is still the right call

CSV is fine for: human-eyeballable inputs, one-shot transfers between teams, archival of tiny datasets, any format whose audience explicitly requires it. CSV is wrong for: anything that runs on a schedule, anything bigger than a few hundred MB, anything that needs to round-trip types reliably, anything you'll join later.

Code

Reading CSV defensively — make every assumption explicit·python

import pandas as pd

df = pd.read_csv(
    'orders.csv',
    encoding='utf-8',                  # never trust the platform default
    dtype=str,                          # bring everything in as string first
    keep_default_na=False,              # don't auto-convert blanks
    na_values=['', 'NULL', 'N/A'],      # explicit list of what counts as null
    parse_dates=False,                  # parse dates yourself, in a separate step
)

# Cast deliberately, with an explicit error surface
df['order_id']    = df['order_id'].astype('string[pyarrow]')
df['amount_usd']  = pd.to_numeric(df['amount_usd'].str.replace(',', ''), errors='raise')
df['order_date']  = pd.to_datetime(df['order_date'], format='%Y-%m-%d', errors='raise')

# Save back to CSV with explicit options too
df.to_csv(
    'orders_clean.csv',
    index=False,
    encoding='utf-8',
    lineterminator='\n',
)

Exercise

Take any CSV, open it in a hex editor (xxd file.csv | head) and look at the first three lines as bytes. Find the line endings. Find the encoding-suggesting bytes (BOM, accented characters). Convert the file with iconv if needed. The point of this exercise is to feel that a CSV is just bytes — and that the bytes don't include the metadata that lets you read them safely.