TSV Versus CSV and When Tabs Are the Saner Choice for Data

The usual argument for TSV is that tabs almost never appear in text, so a tab separated file needs no quoting, and cut -f3 does what you expect. That argument is right as far as it goes, and it stops going at the first field that contains a tab, because TSV has no agreed way to say so. This comparison measures where the shell tools win with TSV, then shows the three dialects that collide when a tab turns up. The wider question of choosing a separator is in the delimiter comparison, and the whole format picture is in the CSV guide.

What actually differs between them

Quoting. CSV has a quoting rule, and TSV in its original form has none. A CSV field can contain any character if you wrap it in quotes; a TSV field can contain anything except a tab and a line break, and there is no wrapper to rescue it.

The IANA registration for text/tab-separated-values is a short document from 1993. It says that each record is a single line, that fields are separated by a tab, that the first line holds field names, and then, in one sentence, that fields containing tabs "are not allowable in this encoding". The registration does not define an escape. So a strict TSV is a promise that the data has no tabs, and anything beyond that promise is a convention that someone invented in a tool.

Where TSV wins

On the command line, a tab delimiter lines up with how Unix tools think. This is a three column TSV with a free text cell that contains a comma and quotes, and the same data converted to CSV. The cell is the third field:

echo "TSV:"; cut -f3 ok.tsv
echo "CSV:"; python - <<'PY'
import csv
csv.writer(open("tsv-as.csv", "w", newline=""), lineterminator="\n").writerows(csv.reader(open("ok.tsv", newline=""), delimiter="\t"))
PY
cut -d, -f3 tsv-as.csv

TSV:
note
likes commas, "quotes" and more
plain
CSV:
note
"likes commas
plain

cut -f3 returned the whole note, likes commas, "quotes" and more, and the CSV version returned "likes commas, a fragment that starts with the opening quote and stops at the first comma. The CSV is correct and cut is not equipped to read it. For TSV, cut and awk -F'\t' work, and sort and paste do too. Sorting by the numeric second column and joining the first column with commas takes one pipeline:

printf 'b\t2\na\t10\nc\t1\n' > t.tsv
sort -t "$(printf '\t')" -k2,2n t.tsv | cut -f1 | paste -sd, -

c,b,a

That is the case for TSV. It makes the standard line tools into a dependable toolkit for data, and it removes the quoting layer that is the usual source of bugs. It is part of why formats from bioinformatics, where files run to many gigabytes and live in shell pipelines, such as BED and VCF, are tab separated.

Where TSV breaks: the first tab in the data

Now put a tab inside a field. Python's csv module, given delimiter="\t", treats TSV as CSV with a different separator, so it wraps the field in quotes. A line splitting tool does not know about quotes:

python - <<'PY'
import csv, io, sys
rows = [["id", "note"], ["1", "a\tb"], ["2", "c"]]
buf = io.StringIO()
csv.writer(buf, delimiter="\t", lineterminator="\n").writerows(rows)
print(repr(buf.getvalue()))
print("awk would see:", [len(l.split("\t")) for l in buf.getvalue().splitlines()], "fields per line")
PY

'id\tnote\n1\t"a\tb"\n2\tc\n'
awk would see: [2, 3, 2] fields per line

The data has two columns. The line 1<TAB>"a<TAB>b" was split by awk into three fields, and the header and the last record into two, so any script that trusts NF is now wrong on exactly one row. Everything that worked for TSV is lost the moment a writer follows the CSV convention for it. Meanwhile any reader that follows the CSV convention reads the file back correctly.

That makes the first dialect: TSV with CSV quoting, as written by Python and DuckDB. DuckDB's COPY with a tab delimiter produced 1<TAB>"a<TAB>b" for the same data. It is a CSV with a tab delimiter, and it needs a CSV parser.

The backslash dialect

The second dialect is what databases write. PostgreSQL's COPY in text format, which is the default when you do not say FORMAT csv, uses a tab delimiter and escapes with backslashes. A tab becomes \t, a backslash becomes \\, and a NULL becomes \N. MySQL's SELECT ... INTO OUTFILE follows the same idea with a twist: on MySQL 8.4 it put a backslash in front of a literal tab instead of writing \t, wrote \N for NULL and doubled the backslashes. I exported three rows from PostgreSQL: one with a tab, one NULL and one with a backslash:

psql -qAt -c "\copy (SELECT 1 AS id, E'a\tb' AS note UNION ALL SELECT 2, NULL UNION ALL SELECT 3, E'back\\\\slash') TO 'pgtext.tsv'"
cat -A pgtext.tsv

1^Ia\tb$
2^I\N$
3^Iback\\slash$

Reading it back into PostgreSQL's own text format restores the data exactly: row 1 has a real tab, row 2 is NULL, and row 3 has a single backslash and a length of 10.

DROP TABLE
CREATE TABLE
COPY 3
 id |    note    | is_null | len 
----+------------+---------+-----
  1 | a       b  | f       |   3
  2 |            | t       |    
  3 | back\slash | f       |  10
(3 rows)

A generic TSV reader does not know the dialect. pandas read the same file and returned the literal two characters \t in the first note, the literal string \N for the NULL, and a doubled backslash for the third:

[{'id': '1', 'note': 'a\\tb'}, {'id': '2', 'note': '\\N'}, {'id': '3', 'note': 'back\\\\slash'}]

DuckDB did the same when I turned its quoting off:

┌────┬─────────────┐
│ id │    note     │
├────┼─────────────┤
│ 1  │ a\tb        │
│ 2  │ \N          │
│ 3  │ back\\slash │
└────┴─────────────┘

None of that is a bug in the readers. It is two dialects meeting: the file is valid in the writer's convention, and the reader assumes another. Anything that crosses between a database's text format and a general TSV tool needs to say which one it is, for example by loading with COPY ... (FORMAT csv, DELIMITER E'\t'), which uses CSV quoting with a tab. The PostgreSQL reference for COPY documents both formats, with the backslash sequences listed.

The third dialect, which is a promise

The third dialect is the IANA one. No quoting and no escapes, with the data guaranteed free of tabs and line breaks. It is the cleanest, and it only holds as long as the promise does. A column that comes from a form field will eventually hold a tab pasted from a spreadsheet, and the failure when it does is not an error. It is a row with the wrong number of fields, or worse, a row with the right number and shifted values.

In practice, the way to keep the promise is to sanitize on the way out: replace tabs and line breaks in text fields with a space, and document that the file does so. If you cannot afford to alter the data, you do not have the third dialect.

So which one

For a file that crosses teams or companies, CSV with the usual quoting. Everything reads it, and the quoting rule is a documented standard even if the specification is loose. For a file between two stages of a pipeline that you control, where the fields are identifiers, numbers and short controlled text, TSV is the saner choice, and I use it with cut, sort, join and awk freely. For anything that holds free text typed by people, use CSV, or pick TSV only if you sanitize.

The one case I treat as a rule: if a database loads the file, use the database's own text format at both ends, or use FORMAT csv at both ends. Mixing them is how \N ends up as a string in a column of names. The guide for loading CSV into PostgreSQL and the MySQL import guide show the exact options that avoid it.

When TSV is the wrong answer

TSV is a poor fit for people who swap files with spreadsheet users, because .csv is the extension their tools and habits expect. I did not test Excel with .tsv files, and the Microsoft documentation on importing and exporting text files describes the wizard route. It also fails when free text can contain tabs and line breaks and you cannot sanitize: a customer comment, an address line pasted from a PDF, source code. And it fails where the receiving system specifies a format. A bank that asks for a pipe or a semicolon does not want to hear that tab is saner.

If you are choosing a format for new data and none of those apply, consider whether you want either. A pipeline that reads its own files more than a few times is better off with a format that carries types and has no delimiter problem at all, as the Parquet comparison shows for repeated analysis.