The Ultimate CSV, TSV, and Excel Data Transformation Cheatsheet

Tabular data in CSV, TSV, and Excel files forms the backbone of business reporting, machine learning pipelines, and database imports. However, inconsistent delimiters and character encoding traps cause hours of headache. Here is your practical cheatsheet.

1. Delimiters: CSV vs TSV vs Semicolon

Different regions and tools export tabular data with varying conventions:

• Comma-Separated Values (CSV): Standard in US/UK, but fraught with comma conflicts when dealing with street addresses, names, or currency values like $1,200.00.
• Tab-Separated Values (TSV): Much cleaner for textual datasets because tabs rarely appear naturally in cell contents.
• Semicolon-Separated Values: Standard in European locales where the comma is used as the decimal point (e.g. 12,50 €).

2. Mastering Quoted Cells & Escaping

According to RFC 4180, when a field contains a delimiter, line break, or double quote, the entire cell must be wrapped in double quotes. Any internal double quote must be escaped by doubling it:

id,name,quote
101,"Doe, Jane","She said ""Hello World!"" to the team."

If an unquoted comma appears inside a cell, standard parsers will interpret it as a column break, instantly corrupting your row structure.

3. Direct In-Browser Excel Generation

Instead of relying on heavy desktop software or insecure cloud converters, modern browser tools can pack OpenXML .xlsx spreadsheet packages natively using pure compression algorithms:

1. The engine splits rows and columns into clean cell coordinates (A1, B1, etc.).
2. Numeric values are assigned XML numeric tags (<v>) for native Excel formula support.
3. Strings are stored in OpenXML inline string formats with full XML entity escaping.
4. The package is compressed into a standard zip archive blob ready for immediate download.

4. Best Practices for Clean Datasets

To ensure frictionless data interchange across data warehouses, Pandas, and Excel:

• Always write headers in lowercase snake_case (e.g. user_id, order_amount) without spaces or punctuation.
• Ensure UTF-8 character encoding with zero BOM to prevent parsing crashes in Linux pipelines.
• Use ISO 8601 timestamps (YYYY-MM-DDTHH:mm:ssZ) rather than localized date strings.
• Strip trailing blank lines and whitespace before loading into SQL databases.