Common questions from junior and senior developers about working with CSV files.
CSV (Comma-Separated Values) is a plain-text format used to store tabular data. Each line represents a row, and columns are separated by a delimiter—usually a comma. It is widely used because it is simple, lightweight, and supported by almost every spreadsheet and database tool.
Use the built-in csv module instead of parsing the file manually:
import csv
with open('data.csv', newline='', encoding='utf-8') as f:
reader = csv.DictReader(f)
for row in reader:
print(row)Always set newline='' and an explicit encoding to avoid common pitfalls.
That is usually an encoding problem. CSV files do not specify their encoding, so they may be saved as UTF-8, Latin-1, Windows-1252, etc. Try opening the file with UTF-8 first; if characters still look wrong, detect the encoding with a library like chardet (Python) or jschardet (JavaScript).
A delimiter is the character that separates columns. The most common is the comma (,), but many regional versions of Excel use a semicolon (;) or tab (\t) depending on the operating system's locale.
Wrap the value in double quotes:
name,description
Acme,"Widgets, gadgets, and tools"If the value itself contains quotes, escape them by doubling them: "He said ""hello""".
Use streaming or chunked parsing instead of loading the whole file into memory. In Python, iterate over a csv.reader object directly; in Node.js, use fs.createReadStream() with csv-parser. For files larger than available RAM, consider batch processing or database bulk-loading tools.
RFC 4180 is the closest thing CSV has to a formal specification. It defines CRLF line endings, optional headers, double-quote escaping, and comma delimiters. In practice, many tools produce "CSV-like" files with different line endings, delimiters, quote rules, or missing headers, which is why a tolerant parser is essential.
Validate at three levels: structural (consistent column counts), syntactic (correct quoting and escaping), and semantic (expected types, ranges, required fields, unique keys). Tools like csvlint, frictionless, or a custom schema validator help automate this process.
Use CSV for simple, flat, human-readable tabular exports that need to be opened in spreadsheets. Prefer JSON for nested or typed data, Parquet for large analytical workloads, and a database when you need indexing, transactions, concurrency, or complex querying.
CSV stores everything as text, so you must define a schema to convert values to booleans, numbers, dates, etc. Decide how nulls are represented ("", NULL, \N, NaN) and apply consistent rules across the file. Document the schema so downstream consumers know what to expect.
Usually not. RFC 4180 allows quoted fields, escaped quotes, embedded newlines, and commas, all of which are easy to mishandle with regex. Use a battle-tested parser library for production code and reserve regex only for very simple, well-known, and controlled inputs.