What is CSV? — Comma-Separated Values Explained
Definition
CSV (Comma-Separated Values) is a simple, text-based file format for storing tabular data (numbers and text) in plain text. Each line of the file corresponds to a row of data, and each field within a row is separated by a comma. CSV files can be opened in any text editor and are widely supported by spreadsheet applications, database systems, and data analysis tools.
Despite its simplicity, CSV has been one of the most enduring data interchange formats since the early days of computing. It remains the go-to choice for exporting data from spreadsheets, transferring data between systems, and preparing data for machine learning pipelines.
CSV Format Rules
CSV follows a straightforward set of formatting conventions:
- Rows are separated by newlines (CRLF or LF)
- Fields within a row are separated by a delimiter (typically a comma)
- Optional header row at the top defines column names
- Fields containing commas, newlines, or double quotes must be enclosed in double quotes
- Double quotes inside a quoted field are escaped by doubling them (
"") - Spaces around commas are part of the field value (not trimmed)
- Blank lines may be ignored depending on the parser
Core Delimiter Rules
| Rule | Example | Explanation |
|---|---|---|
| Simple field | John |
Plain text, no quoting needed |
| Field with comma | "Smith, John" |
Comma in value requires quotes |
| Field with quotes | "He said ""Hello""" |
Quotes escaped by doubling |
| Multi-line field | "Line1\nLine2" |
Newline inside quotes is valid |
| Empty field | , or "" |
Two delimiters with nothing between |
Example CSV with Headers
firstName,lastName,email,age,isActive
John,Smith,john.smith@example.com,35,true
Jane,Doe,jane.doe@example.com,28,false
Bob,"Johnson, Jr.",bob.j@example.com,42,true
Alice,"Williams ""Ali""",alice.w@example.com,31,trueWhen parsed, this CSV produces the following table:
| firstName | lastName | age | isActive | |
|---|---|---|---|---|
| John | Smith | john.smith@example.com | 35 | true |
| Jane | Doe | jane.doe@example.com | 28 | false |
| Bob | Johnson, Jr. | bob.j@example.com | 42 | true |
| Alice | Williams "Ali" | alice.w@example.com | 31 | true |
Notice in row 3 the last name Johnson, Jr. contains a comma, so the entire field is wrapped in double quotes. In row 4, the field contains a literal double quote, so it is escaped by writing it as "".
RFC 4180 Standard
CSV was formally standardized in RFC 4180 (October 2005), titled "Common Format and MIME Type for Comma-Separated Values (CSV) Files". Key specifications:
- MIME type:
text/csv - Line terminator: CRLF (but most parsers accept LF)
- Header row: Optional, but common
- Fields: May be quoted or unquoted
- Quoted fields: Must be enclosed in double quotes
- Quote escaping: Double quote inside a quoted field appears as
"" - Spacing: Spaces around commas are considered part of the field value
RFC 4180 is widely implemented, though many CSV parsers in practice deviate from the strict specification. The standard deliberately leaves some behaviors unspecified (such as how to handle leading zeros), leading to variations across implementations.
CSV vs TSV vs Other Delimiters
CSV (Comma)
| Pro | Con |
|---|---|
| Human-readable in raw form | Commas often appear in text fields |
| Universal spreadsheet support | Requires quoting for many values |
| Default for most tools | Ambiguous locale handling (decimal comma) |
TSV (Tab-Separated Values)
| Pro | Con |
|---|---|
| Tabs rarely appear in data | Harder to visually inspect |
| No quoting needed for most text | Less universal tool support |
| Preferred for Unix pipelines | Tabs invisible in many editors |
Pipe-Delimited (|)
| Pro | Con |
|---|---|
| Pipes are very rare in data | Non-standard format |
| No quoting needed in practice | Poor spreadsheet support |
| Clean for log files | Limited tooling ecosystem |
Comparison Table
| Feature | CSV | TSV | Pipe-Delimited |
|---|---|---|---|
| Delimiter | , |
\t |
` |
| RFC Standard | RFC 4180 | None (informal) | None |
| Spreadsheet Support | Excellent | Good | Poor |
| Quoting Needed | Often | Rarely | Almost never |
| Human Readable | Good | Moderate | Good |
| Locale Safe | No (decimal comma) | Yes | Yes |
Common CSV Issues
Trailing Commas
Some systems emit trailing commas at the end of rows:
name,age,city
Alice,30,New York,
Bob,25,,These can produce empty fields at the end of each row. Most robust parsers handle this gracefully, but it can cause issues in strict parsers or when the data is consumed by SQL importers.
Quoted Fields with Commas
When a field value naturally contains a comma (e.g., "Smith, John") the entire field must be quoted:
name,title,department
"Smith, John","Senior Engineer, R&D",EngineeringIf the comma in Senior Engineer, R&D were not quoted, it would be split into two columns, shifting all subsequent data.
Encoding Problems
CSV files have no built-in encoding declaration. Common pitfalls:
- UTF-8 BOM: Some tools add a Byte Order Mark (BOM) at the start. Excel on Windows expects this for UTF-8 CSV files.
- Latin-1 vs UTF-8: If a CSV contains accented characters (é, ñ, ü) and the encoding is not recognized, characters become garbled.
- Excel encoding: Older Excel versions export CSV in Windows-1252 encoding by default, which breaks special characters when opened in UTF-8 tools.
Multi-Line Fields
Values that contain newlines must be quoted:
id,description
1,"This description
spans two lines"
2,"Another value"Not all CSV parsers handle multi-line fields correctly, which can cause rows to be split unexpectedly.
Leading Zeros
Values like 00123 or zip codes (02134) can lose leading zeros:
id,zipCode
1,02134
2,00123Some spreadsheet applications interpret these as numbers and drop the leading zeros. To preserve them, wrap the field in quotes: "02134".
Common Use Cases
Spreadsheet Export
Almost every spreadsheet application — Microsoft Excel, Google Sheets, Apple Numbers, LibreOffice Calc — supports CSV export. CSV is the simplest way to move tabular data between applications without proprietary format issues.
Database Import and Export
CSV is the standard interchange format for relational databases:
- MySQL / MariaDB:
LOAD DATA INFILEandSELECT INTO OUTFILEsupport CSV directly - PostgreSQL:
COPY ... FROMandCOPY ... TOwith CSV format - SQLite:
.importand.exportcommands - MongoDB:
mongoimportandmongoexportwith--type=csv
Data Migration
CSV is the lingua franca for migrating data between SaaS platforms, CRMs, marketing tools, and analytics systems. Most platforms offer CSV import/export as a universal integration mechanism.
Data Science and Machine Learning
CSV is the most common format for datasets on Kaggle, data.gov, and other open data portals. Libraries like pandas (pd.read_csv), R (read.csv), and NumPy read CSV natively.
ETL Pipelines
Extract, Transform, Load (ETL) workflows frequently use CSV as an intermediate format between data sources and data warehouses.
Log Analysis
Many logging systems emit CSV-formatted logs for consumption by analysis tools, making it easy to import logs into spreadsheets or databases.
LangStop CSV Tools
- CSV to JSON — Convert CSV to JSON format
- CSV to YAML — Convert CSV to YAML format
- CSV to XML — Convert CSV to XML format
- JSON to CSV — Convert JSON to CSV format
- JSON to TSV — Convert JSON to TSV format
- YAML to CSV — Convert YAML to CSV format
- XML to CSV — Convert XML to CSV format
- CSV Mock Data Generator — Generate realistic test data in CSV