How to Format Text for Spreadsheets: CSV & TSV Guide
Getting plain text into a spreadsheet cleanly is a recurring headache. Paste a list of names and addresses into Excel and you often get everything crammed into a single column. The fix is choosing the right delimiter before you import — and that's where learning to format text for spreadsheet use pays off. This guide covers CSV vs TSV, delimiter selection, handling special characters, and the tools that make the job fast.
CSV vs TSV: What's the Difference?
Both formats store tabular data as plain text. The difference is the delimiter — the character that separates fields.
- CSV (Comma-Separated Values) uses commas:
name,age,city - TSV (Tab-Separated Values) uses tabs:
name age city
CSV is the default export format for almost every spreadsheet application and database. TSV is less common but shines when your data contains commas — think addresses like Springfield, IL — because tabs almost never appear in natural text.
Choosing the Right Delimiter
The delimiter decision comes down to one question: does your data contain the delimiter character?
| Delimiter | Use When | Watch Out For |
|---|---|---|
Comma (,) |
Standard CSV exports, simple data | Commas inside field values |
| Tab | Data contains commas (addresses, sentences) | Tabs lost when copying between apps |
Semicolon (;) |
European locales where comma is decimal separator | Excel regional settings may misread |
Pipe (|) |
Data contains commas, tabs, and semicolons | Non-standard; requires import config |
If your fields contain the delimiter, you must wrap them in double quotes. Smith, John,35,Chicago is ambiguous, but "Smith, John",35,Chicago is not — the quotes tell the parser to treat the comma inside as literal text.
Handling Commas and Quotes in Data
This is where most imports break. Consider a product list:
product,price,notes
"Widget, Deluxe",12.99,"Best seller, limited stock"
"O-Ring Kit",4.50,"Waterproof, ""industrial grade"""
Two rules handle every edge case:
- Quote any field containing the delimiter, a quote, or a newline.
- Escape literal quotes by doubling them.
""industrial grade""inside a quoted field represents"industrial grade".
Most spreadsheet apps handle this automatically on export. The problem is hand-typed or machine-generated text that skips the quoting rules. Running your data through the text formatter normalizes line endings and trims stray whitespace before you split fields, which prevents a surprising number of import errors.
Encoding: Why UTF-8 Matters
Spreadsheets expect a specific character encoding. If your file is saved as UTF-8 but Excel reads it as ANSI, accented characters and symbols turn into garbage. Names like José or Müller become José and Müller.
Best practices:
- Always save as UTF-8.
- For Excel specifically, save as UTF-8 with BOM so it detects the encoding correctly.
- Avoid UTF-16 unless your tool explicitly requires it.
If you're converting between formats, the CSV to JSON tool handles encoding cleanly and lets you inspect the parsed structure before re-exporting. Going the other direction, JSON to CSV flattens nested data and adds proper quoting automatically.
Step-by-Step: Format Text for Spreadsheet Import
1. Clean the Source Text
Remove extra blank lines and trim trailing spaces. The text formatter does this in one pass.
2. Pick Your Delimiter
If your data has no commas, CSV is fine. If it does, switch to TSV or plan to quote fields.
3. Quote Problem Fields
Wrap any field containing the delimiter in double quotes. Double any literal quotes inside those fields.
4. Save as UTF-8
Use UTF-8 (with BOM for Excel) to preserve special characters.
5. Import and Verify
Open the spreadsheet app's import dialog, select the delimiter, and preview the columns before confirming.
Common Pitfalls
Mixed Delimiters
A file that uses commas in some rows and tabs in others will misalign columns. Standardize on one delimiter before importing.
Invisible Characters
Copy-pasting from web pages often brings non-breaking spaces (\u00A0) that look like regular spaces but break parsing. A formatter that normalizes whitespace catches these.
Line Endings
Windows uses \r\n, Unix uses \n. Most modern apps handle both, but older versions of Excel on Mac expect \r. If rows stack into one cell, line endings are the usual culprit.
Leading Zeros
Product codes like 00742 lose their leading zeros when Excel treats them as numbers. Format those columns as text during import, or prefix with a tab character.
When to Convert Formats
Sometimes the cleanest path is a format conversion rather than a manual reformat. If your source is JSON from an API, the JSON to CSV tool produces a spreadsheet-ready file with proper quoting. If you need structured data from a CSV for a script, CSV to JSON parses and validates the structure.
Start Formatting
Formatting text for spreadsheet import comes down to three things: the right delimiter, proper quoting, and UTF-8 encoding. Get those right and your imports land in the correct columns every time. For the official spec, the RFC 4180 CSV standard defines the format in full, and Google Sheets support covers import settings for common cases.
