CSV (Comma-Separated Values)

CSV (Comma-Separated Values) is a ubiquitous, plain-text format for tabular data storage and exchange, standardized in RFC 4180. Each record occupies a single line composed of fields separated by literal commas (,). Because of its lightweight, human-readable simplicity, CSV serves as the primary data exchange format for spreadsheet software (Microsoft Excel, Google Sheets), relational database exports, and data science workflows.

Convert tabular datasets directly into structured JSON arrays with our fast, privacy-friendly CSV to JSON Converter tool.

---

Technical Specifications at a Glance

Specification	RFC 4180 Standard	Common Real-World Variants
Standard Specification	IETF RFC 4180	Vendor-specific exports
MIME Media Type	text/csv	application/csv, text/plain
Standard File Extension	.csv	.csv, .tsv, .txt
Field Delimiter	Comma (,)	Semicolon (;), Tab (\t), Pipe (|)
Record Separator	CRLF (\r\n)	LF (\n) or CRLF (\r\n)
Text Delimiter (Quotes)	Double quote (")	Double quote ("), Single quote (')
Quote Escaping Rule	Double double-quotes ("")	Backslash (\") or double quotes ("")

---

The 4 Core Rules of RFC 4180

While historically implemented inconsistently across software applications, RFC 4180 establishes four standardized formatting rules:

1. Line Breaks: Each record must be placed on a separate line, terminated by a CRLF (\r\n) line break.
2. Header Row (Optional): The first record may contain a header identifying column field names with identical column count.
3. Escaping Fields with Reserved Characters: Any field containing commas (,), line breaks (\n), or double quotes (") must be enclosed in double quotes:

   id,name,address
   1,"Doe, John","123 Main St, Apt 4"
   

4. Escaping Double Quotes: If double quotes are present inside a quoted field, each quote must be escaped with an adjacent double quote (""):

   id,quote
   101,"She said, ""Hello World!"""
   

---

Security Threat: CSV Formula Injection (DDE)

One of the most dangerous vulnerabilities in CSV processing is CSV Formula Injection (also known as Formula Injection or Dynamic Data Exchange (DDE) attacks).

When spreadsheet applications like Microsoft Excel or LibreOffice open a CSV file, any cell starting with mathematical or formula prefix characters (=, +, -, @, \t, \r) is automatically evaluated as an executable formula.

Example Attack Vector:

id,username,email
1,attacker,"=CMD|'/C calc'!A0"

If an administrator exports user data to CSV and opens it in Excel, the spreadsheet attempts to execute local commands on the user's operating system or exfiltrate private data via HTTP callbacks.

Prevention Best Practices:

• Prepend Single Quotes: Prefix any string beginning with =, +, -, or @ with a single apostrophe (') or tab to force spreadsheet engines to treat the cell as raw text.
• Sanitize Data on Export: Strip dangerous leading characters or enclose values in quotes during generation.

---

Code Examples: Parsing CSV

Modern JavaScript (Node.js & Browser)

// Lightweight RFC 4180 compliant CSV line parser
export function parseCsvRow(row: string, delimiter = ','): string[] {
  const fields: string[] = [];
  let current = '';
  let insideQuotes = false;

  for (let i = 0; i < row.length; i++) {
    const char = row[i];
    const nextChar = row[i + 1];

    if (char === '"') {
      if (insideQuotes && nextChar === '"') {
        current += '"';
        i++; // Skip escaped quote
      } else {
        insideQuotes = !insideQuotes;
      }
    } else if (char === delimiter && !insideQuotes) {
      fields.push(current.trim());
      current = '';
    } else {
      current += char;
    }
  }
  fields.push(current.trim());
  return fields;
}

// Example usage:
const record = '1,"Acme, Inc.","San Francisco, CA","""Active"""';
console.log(parseCsvRow(record));
// Output: ['1', 'Acme, Inc.', 'San Francisco, CA', '"Active"']

Python 3 (using csv module)

import csv
from io import StringIO

csv_data = """id,product,price
101,"Smart Watch",199.99
102,"Wireless Earbuds, Pro",89.50"""

reader = csv.DictReader(StringIO(csv_data))
for row in reader:
    print(f"Product: {row['product']} -> ${row['price']}")

---

Frequently Asked Questions

Why does Microsoft Excel open some CSV files in a single column?

In European regions where the comma is used as a decimal separator (e.g. 3,14), Excel defaults to using the semicolon (;) as the CSV list separator. When opening a standard comma-delimited CSV, Excel fails to split the columns. To fix this, you can specify sep=, as the first line of the CSV file.

What is the difference between CSV and TSV?

CSV uses commas (,) to separate fields, whereas TSV (Tab-Separated Values) uses horizontal tabs (\t). TSV is often preferred for natural language text datasets because text rarely contains raw tab characters, minimizing the need for complex quotation escaping rules.

How do you convert CSV files into JSON format?

Because CSV is strictly rectangular and 2-dimensional (rows and columns), converting it into JSON requires mapping each row to an object where keys correspond to header names, or to an array of arrays. You can convert large datasets instantly using our client-side CSV to JSON Converter tool.