CSV

CSV is one of the oldest and most ubiquitous data formats. It appeared in the 70s for data exchange between mainframes. Its popularity stems from extreme simplicity: plain text, no metadata, no compression.

Basic structure:

name,age,city\nJohn,30,New York\nMary,25,Boston

First row typically contains headers (column names). Each subsequent row is a record. Values are separated by commas, though the separator can be semicolon (;), tab (\t) or pipe (|) depending on regional settings.

CSV is the default export format in Excel, Google Sheets, SQL databases and analytics tools. Every programming language has native libraries for reading/writing CSV: csv in Python, csv-parse in Node, encoding/csv in Go.

Key advantage: universality. A 1980 CSV opens today in any text editor. No vendor lock-in or incompatible versions.

Though CSV seems trivial, it has subtleties that break naive parsers:

• Values with commas: if a field contains the separator, it must be quoted: "Pérez, Juan",30,NYC.
• Quotes inside values: escaped by doubling: "He said ""hello"" and left" represents He said "hello" and left.
• Line breaks in fields: multiline values go in quotes: "First line\nSecond line",other,value.
• Regional separator: Europe uses ; because they use comma as decimal separator (3,14 instead of 3.14). Excel exports according to system settings.
• Encoding: standard doesn't specify charset. UTF-8 is recommended, but many legacy systems use Latin-1 or Windows-1252, causing issues with accents.

RFC 4180 (2005) tried to standardize CSV, but adoption is partial. Many exporters generate "Excel-compatible CSV" that breaks other parsers.

BOM (Byte Order Mark): Excel requires \uFEFF at start of UTF-8 file to detect encoding correctly. Other parsers ignore or reject it.

Exporting database data: PostgreSQL (COPY TO), MySQL (SELECT INTO OUTFILE), SQLite (.mode csv) export to CSV natively. Ideal for lightweight backups or migrating data between systems.

Analysis with Excel/Google Sheets: non-technical stakeholders can open CSV directly. For simple dashboards or ad-hoc reports, CSV + Sheets is faster than building a BI.

ETL and data pipelines: tools like Apache Spark, Pandas, dbt ingest CSV without configuration. S3 + CSV is common architecture for data lakes.

Public datasets: government, universities, Kaggle publish data in CSV because it's the most portable format. Doesn't require special software to consume.

Don't use for:

• Hierarchical/nested data: CSV is strictly tabular. For nested relationships (e.g. user → orders → items) you need JSON or Parquet.
• Large files (>1GB): uncompressed CSV takes up space and is slow to parse. Prefer Parquet (columnar + compression) or CSV.gz.
• Complex data types: CSV has no native types. Everything is string until parser infers. Dates, booleans and nulls are ambiguous.
• Security: CSV injection exists. Fields starting with =, +, -, @ can execute formulas in Excel. Sanitize inputs.

CSV vs JSON:

• CSV wins in simplicity, size and compatibility with analytics tools.
• JSON wins in expressiveness (nesting, native types) and is standard for APIs.
• Rule of thumb: if data is tabular and going to Sheets/SQL, use CSV. If going to web app or has hierarchy, use JSON.

CSV vs Parquet:

• Parquet is binary columnar format, optimized for BigData (Spark, Hive, Redshift).
• 10-100x more efficient in storage and analytical queries (reads only needed columns).
• CSV wins if you need to inspect data with cat or edit manually.

CSV vs Excel (.xlsx):

• Excel supports multiple sheets, formulas, formatting and validation. CSV is single-sheet, data only.
• Excel is binary (zip + XML), heavier. CSV is plain text.
• CSV wins in automation: easier to generate/parse programmatically.

Best practices:

• Always include header row with descriptive names.
• Use UTF-8 + BOM if target is Excel.
• Escape quotes and line breaks per RFC 4180.
• Compress large CSVs with gzip (CSV.gz reduces 80-90%).
• Version with Git (plain text = readable diffs).

Examples

• name,age,city\nCarlos,28,Chicago\nLaura,32,Austin
• product,price,stock\n"Laptop, 15\"",1200.50,15\nMouse,25.00,200
• date,temperature,humidity\n2024-01-15,72.5,65\n2024-01-16,75.2,60
• id;description;active\n1;"Item with; separator";true\n2;Normal;false
• user,role,"permissions"\nadmin,superuser,"read,write,delete"\nguest,readonly,read

FAQ