What is CSV? Understanding Comma-Separated Values

name,age,city,country
John Doe,30,New York,USA
Jane Smith,25,London,UK
Bob Johnson,35,Toronto,Canada

Structure:
- First row: Headers (column names)
- Subsequent rows: Data records
- Commas separate values (columns)
- Newlines separate records (rows)
- Plain text, no formatting

Comma (most common):
name,age,city
John,30,NYC

Semicolon (European standard):
name;age;city
John;30;NYC

Tab (TSV - Tab-Separated Values):
name	age	city
John	30	NYC

Pipe:
name|age|city
John|30|NYC

Why different delimiters?
- Regional settings (Excel uses ; in some locales)
- Data contains commas (use different delimiter)
- Personal preference

Problem: Value contains comma
Wrong: John Doe,25,New York, NY
Right: John Doe,25,"New York, NY"

Problem: Value contains quotes
Wrong: He said "Hello",greeting
Right: "He said ""Hello""",greeting
(Double the quotes inside quoted field)

Problem: Value contains newline
Right: "This is a
multi-line
value",other_field

Rules:
1. Enclose field in double quotes if it contains:
   - Delimiter (comma)
   - Double quote
   - Newline
2. Escape quotes by doubling them: " becomes ""

RFC 4180 Rules:

1. Each record on separate line (CRLF)
2. Last record may/may not have ending line break
3. Optional header row as first line
4. Same number of fields in each record
5. Fields may be enclosed in double quotes
6. Fields with special chars MUST be quoted
7. Quote inside field: use double quote ("") 
8. Spaces are part of field value

Example:
name,age,email
"Doe, John",30,"john@example.com"
"Smith, Jane",25,jane@example.com

Excel peculiarities:

1. May use semicolon in some locales
2. Uses system locale for decimal separator
   USA: 1,234.56
   Europe: 1.234,56
   
3. Date formatting varies by locale
   USA: 12/31/2024
   Europe: 31.12.2024
   
4. UTF-8 BOM for encoding:
   Adds \uFEFF at start of file
   
5. Leading zeros stripped:
   00123 becomes 123 (use ="00123" to preserve)
   
6. Large numbers as scientific notation:
   123456789012345 → 1.23E+14

first_name,last_name,email,phone,company
John,Doe,john.doe@example.com,+1-555-0100,"Acme, Inc."
Jane,Smith,jane.smith@example.com,+1-555-0101,Tech Corp
Bob,Johnson,bob.j@example.com,+1-555-0102,"Johnson & Associates"

order_id,date,customer,product,quantity,price,total
1001,2024-01-15,"Smith, John",Widget A,2,29.99,59.98
1002,2024-01-15,Jane Doe,Widget B,1,49.99,49.99
1003,2024-01-16,Bob Lee,"Widget C, Premium",3,99.99,299.97

user_id,username,email,created_at,is_active
1,johndoe,john@example.com,2024-01-01 10:30:00,true
2,janesmith,jane@example.com,2024-01-02 14:15:00,true
3,bobtest,bob@example.com,2024-01-03 09:00:00,false

// Using PapaParse library
const Papa = require('papaparse');

// Parse CSV string
const csv = `name,age,city
John,30,NYC
Jane,25,LA`;

const result = Papa.parse(csv, {
  header: true,  // First row as headers
  dynamicTyping: true  // Auto-convert numbers
});

console.log(result.data);
// [{name: 'John', age: 30, city: 'NYC'}, ...]

// Generate CSV from objects
const data = [
  {name: 'John', age: 30, city: 'NYC'},
  {name: 'Jane', age: 25, city: 'LA'}
];

const output = Papa.unparse(data);
console.log(output);

import csv

# Reading CSV
with open('data.csv', 'r') as file:
    reader = csv.DictReader(file)
    for row in reader:
        print(row['name'], row['age'])

# Writing CSV
data = [
    {'name': 'John', 'age': 30, 'city': 'NYC'},
    {'name': 'Jane', 'age': 25, 'city': 'LA'}
]

with open('output.csv', 'w', newline='') as file:
    fieldnames = ['name', 'age', 'city']
    writer = csv.DictWriter(file, fieldnames=fieldnames)
    
    writer.writeheader()
    writer.writerows(data)

# Pandas (powerful for data analysis)
import pandas as pd

df = pd.read_csv('data.csv')
print(df.head())
df.to_csv('output.csv', index=False)

<?php
// Reading CSV
$file = fopen('data.csv', 'r');
$headers = fgetcsv($file);

while (($row = fgetcsv($file)) !== false) {
    $data = array_combine($headers, $row);
    echo $data['name'] . ": " . $data['age'] . "\n";
}
fclose($file);

// Writing CSV
$data = [
    ['name', 'age', 'city'],
    ['John', 30, 'NYC'],
    ['Jane', 25, 'LA']
];

$file = fopen('output.csv', 'w');
foreach ($data as $row) {
    fputcsv($file, $row);
}
fclose($file);
?>

CSV:
+ Simple, universal, small files
+ Human readable, version control friendly
- No formatting, single table only

Excel (XLSX):
+ Multiple sheets, formulas, formatting
+ Data types, validation, charts
- Proprietary, larger files, binary format

Use CSV for: Data exchange, backups, simple tables
Use Excel for: Analysis, presentation, complex data

CSV:
+ Compact for tabular data
+ Better for spreadsheet import
- Flat structure only

JSON:
+ Nested objects and arrays
+ Data types built-in
+ Better for APIs and config
- Verbose for simple tables

CSV Example (compact):
name,age
John,30

JSON Example (verbose but structured):
[{"name":"John","age":30}]

Problem: Accented characters show as ¿ � or �
Solution: 
1. Save as UTF-8 with BOM
2. In Excel: Data → From Text → UTF-8
3. In code: specify encoding when reading

Python:
with open('data.csv', 'r', encoding='utf-8') as f:
    reader = csv.reader(f)

Problem: 00123 becomes 123 in Excel

Solutions:
1. Prefix with apostrophe: '00123
2. Use formula: ="00123"
3. Format cell as Text before import
4. Use tab-delimited instead of comma