TSV (Tab-Separated Values) is one of the simplest and most robust data formats in existence. While CSV gets all the attention, TSV often works better for data containing commas — like addresses, currency values, or free-text fields. Understanding when to use TSV instead of CSV (or instead of Excel) can save hours of debugging.
What Is TSV?
TSV (.tsv) is a plain text format where each row is on a separate line, and each column is separated by a tab character (\t, ASCII 0x09). Like CSV, TSV is used to store tabular data — spreadsheets, database exports, data pipelines — without proprietary binary encoding.
Name Email City Price
Alice Smith alice@example.com New York $1,250.00
Bob Jones bob@example.com San Francisco $3,800.00
In the above, each column is separated by a tab (shown as whitespace here). Notice that commas, dollar signs, and spaces inside values cause no parsing issues — unlike CSV, which requires quoting or escaping when commas appear in data.
TSV vs CSV: When to Use Which
| Scenario | Recommended | Why |
|---|---|---|
| Data contains many commas (addresses, prices) | TSV | No escaping needed |
| Data contains tabs | CSV | Tabs must be escaped in TSV |
| Maximum tool compatibility | CSV | Excel auto-detects CSV, less so TSV |
| Database imports (MySQL, PostgreSQL) | TSV | LOAD DATA INFILE uses tabs by default |
| Bioinformatics data | TSV | Scientific standard (GTF, BED, VCF, GFF3 are TSV-based) |
| Human editing in text editor | TSV | Columns visually align in monospace editors |
| Web APIs / JSON interchange | JSON | Better for nested/complex data |
TSV in Science and Bioinformatics
TSV is the backbone of genomic data formats:
- BED (Browser Extensible Data) — genomic coordinate format, tab-separated
- VCF (Variant Call Format) — DNA variant data, tab-separated
- GFF3/GTF — gene annotation formats, tab-separated
- FASTQ — sequencing data with tab-separated fields
- DESeq2 output — differential expression results export to TSV
If you work with genomics, bioinformatics, or any scientific pipeline, you're almost certainly using TSV already — you may just not call it that.
Working with TSV Files
Open TSV in Excel: Excel doesn't always auto-detect TSV. If double-clicking the .tsv file opens it as one column, use File → Import → Text File (Windows) or Data → From Text/CSV. Choose "Tab" as the delimiter.
Excel to TSV: File → Save As → Text (Tab delimited) (*.txt) — then rename the .txt to .tsv.
TSV in Python:
import csv
# Read TSV
with open('data.tsv', 'r') as f:
reader = csv.reader(f, delimiter='\t')
for row in reader:
print(row)
# Write TSV
with open('output.tsv', 'w', newline='') as f:
writer = csv.writer(f, delimiter='\t')
writer.writerow(['Name', 'Email', 'City'])
writer.writerow(['Alice', 'alice@example.com', 'New York'])
TSV in pandas (Python):
import pandas as pd
# Read
df = pd.read_csv('data.tsv', sep='\t')
# Write
df.to_csv('output.tsv', sep='\t', index=False)
# Convert TSV to Excel
df.to_excel('output.xlsx', index=False)
# Convert TSV to CSV
df.to_csv('output.csv', index=False)
Command-line tools:
# Count rows (excluding header)
wc -l data.tsv
# Get first 5 rows
head -5 data.tsv
# Get specific columns (1st and 3rd)
cut -f1,3 data.tsv
# Sort by second column
sort -k2 -t$'\t' data.tsv
# Convert TSV to CSV (handle embedded commas)
python3 -c "
import csv, sys
r = csv.reader(sys.stdin, delimiter='\t')
w = csv.writer(sys.stdout)
for row in r: w.writerow(row)
" < data.tsv > data.csv
MySQL/PostgreSQL import:
-- MySQL
LOAD DATA INFILE '/path/to/data.tsv'
INTO TABLE mytable
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;
-- PostgreSQL
COPY mytable FROM '/path/to/data.tsv' WITH (FORMAT text, HEADER true);
Converting TSV to Other Formats
TSV to CSV: Replace tabs with commas (quoting fields that contain commas):
df = pd.read_csv('data.tsv', sep='\t')
df.to_csv('data.csv', index=False)
TSV to XLSX: df.to_excel('data.xlsx', index=False) — pandas handles it.
TSV to JSON:
import json, pandas as pd
df = pd.read_csv('data.tsv', sep='\t')
df.to_json('data.json', orient='records', indent=2)
TSV to ODS/LibreOffice:
soffice --headless --infilter="Text - txt - csv (StarCalc):9,34,UTF-8" --convert-to ods data.tsv
TSV Best Practices
- Always use UTF-8 encoding — avoids character set issues across systems
- Include a header row — the first line naming each column
- Avoid trailing tabs — some parsers add an extra empty column
- Use Unix line endings (\n) — Windows line endings (\r\n) can cause issues with
wc -land some parsers - Quote values with tabs in them if your tool supports quoted TSV (RFC 4180 style but with tab delimiter)
Related conversions
Frequent conversions across the catalogue: