How to Tell What Delimiter an Unknown CSV File Uses

A file called export.txt arrives in your inbox. It might be commas, semicolons, tabs or pipes, and the extension tells you nothing. Guessing wrong either breaks the load loudly, which is the good outcome, or produces one wide column that looks like success. This procedure takes about two minutes and ends with a delimiter you have checked rather than assumed. The reasons the question exists at all are in the CSV format guide, and the choice of delimiter when you write a file is in the delimiter comparison.

What you need

The file, a terminal, and a few seconds of patience. I use six small test files for the examples. Four are ordinary and carry a comma, semicolon, tab and pipe respectively. One has a single column, and one has commas inside a quoted field. Each has a header and two records.

Step 1: look at the bytes

Do not open the file in a spreadsheet. A spreadsheet will apply its own guess, and the guess is the thing you are testing. Print the first lines with cat -A, which draws a tab as ^I and the end of a line as $:

head -2 d-tab.tsv | cat -A; head -2 d-pipe.txt | cat -A

id^Iname^Iqty$
1^IAna^I3$
id|name|qty$
1|Ana|3$

Two seconds of reading settles most files. The first shows ^I between fields, so it is tab delimited, and the second shows the pipes plainly. If the output is a wall of non-ASCII symbols, the file is probably in a legacy encoding or compressed, and a delimiter question comes second. The encoding detection article covers that case.

Step 2: ask a sniffer, and ask two

Three sniffers are within reach, and they use different methods, which is why asking two is worth the extra command. Python's csv.Sniffer looks at character frequencies across the sample. DuckDB's sniff_csv tries a grid of dialects and scores each by how many rows parse consistently. qsv sniff is a compiled tool built for the same job, and it also reports the header.

Python's standard library version:

python - <<'PY'
import csv
for f in ("d-comma.csv", "d-semi.csv", "d-tab.tsv", "d-pipe.txt", "d-single.csv", "d-text.csv"):
    try:
        d = csv.Sniffer().sniff(open(f).read())
        print(f"{f:12} -> {d.delimiter!r}")
    except csv.Error as e:
        print(f"{f:12} -> Error: {e}")
PY

d-comma.csv  -> ','
d-semi.csv   -> ';'
d-tab.tsv    -> '\t'
d-pipe.txt   -> '|'
d-single.csv -> Error: Could not determine delimiter
d-text.csv   -> ','

DuckDB, one file at a time because sniff_csv takes a literal file name:

for f in d-comma.csv d-semi.csv d-tab.tsv d-pipe.txt d-single.csv d-text.csv; do
  printf '%-13s' "$f"; duckdb -list -noheader -c "SELECT 'delimiter=' || replace(Delimiter, chr(9), '<tab>') || '  header=' || HasHeader FROM sniff_csv('$f')"
done

d-comma.csv  delimiter=,  header=true
d-semi.csv   delimiter=;  header=true
d-tab.tsv    delimiter=<tab>  header=true
d-pipe.txt   delimiter=|  header=true
d-single.csv delimiter=,  header=true
d-text.csv   delimiter=,  header=true

And qsv:

for f in d-comma.csv d-semi.csv d-tab.tsv d-pipe.txt d-single.csv d-text.csv; do printf '%-13s' $f; qsv sniff $f 2>&1 | grep -E '^(Delimiter|Header Row)' | tr '\n' ' '; echo; done

d-comma.csv  Delimiter: , Header Row: true 
d-semi.csv   Delimiter: ; Header Row: true 
d-tab.tsv    Delimiter: tab Header Row: true 
d-pipe.txt   Delimiter: | Header Row: true 
d-single.csv Delimiter: , Header Row: false 
d-text.csv   Delimiter: , Header Row: true 

On the four ordinary files all three give the same delimiter. The disagreement is on d-single.csv, a file with one column called name and no delimiter at all. Python raised Could not determine delimiter. DuckDB answered comma and qsv answered comma with no header. Those are three different answers to a question that has none, and it is the useful result of asking more than one tool: when they disagree, the file is unusual and deserves a look.

Step 3: count the candidates, with a warning

Counting delimiter characters on the first line is the oldest trick, and it is fine when the data is simple. This loop counts each candidate in the header of each file:

for f in d-comma.csv d-semi.csv d-tab.tsv d-pipe.txt d-single.csv d-text.csv; do
  printf '%-12s' "$f"
  for d in ',' ';' '\t' '|'; do
    n=$(head -1 "$f" | awk -v d="$d" 'BEGIN{FS=d} {print NF-1}')
    printf '  %s=%s' "$d" "$n"
  done; echo
done

d-comma.csv   ,=2  ;=0  \t=0  |=0
d-semi.csv    ,=0  ;=2  \t=0  |=0
d-tab.tsv     ,=0  ;=0  \t=2  |=0
d-pipe.txt    ,=0  ;=0  \t=0  |=2
d-single.csv  ,=0  ;=0  \t=0  |=0
d-text.csv    ,=1  ;=0  \t=0  |=0

The header lines give clean counts: two of whichever character is the delimiter and zero of the others. Look at d-text.csv, the file with commas inside quotes. The header gives ,=1, which is right, but each data line carries four commas, three of them inside the quoted field. Count the whole file instead of the header and the numbers stop agreeing between lines, so a rule like "the delimiter is the character with the same nonzero count on every line" rejects the correct answer. Counting is a hint, not a test, whenever quotes are involved.

Step 4: let the CSV parser vote

The test that does not suffer from that problem asks a real parser. For each candidate, parse the first couple of hundred records and look at the widths. The delimiter is the candidate that gives one consistent width greater than one. This is about 20 lines of Python:

"""Pick the delimiter under which the first records parse to the same, wider-than-one width.

Quote aware (it uses the csv module), unlike counting characters with awk.
"""
import csv
import io
import sys

CANDIDATES = [",", ";", "\t", "|"]


def detect(path: str, sample_lines: int = 200):
    with open(path, newline="", encoding="utf-8") as f:
        sample = "".join(line for _, line in zip(range(sample_lines), f))
    scored = []
    for d in CANDIDATES:
        widths = [len(r) for r in csv.reader(io.StringIO(sample, newline=""), delimiter=d)]
        if widths and len(set(widths)) == 1 and widths[0] > 1:
            scored.append((widths[0], d))
    if not scored:
        return None, "no candidate gives a consistent width above 1"
    scored.sort(reverse=True)
    best = scored[0]
    note = "" if len(scored) == 1 else f"also consistent: {[repr(d) for _, d in scored[1:]]}"
    return best[1], note


if __name__ == "__main__":
    for p in sys.argv[1:]:
        d, note = detect(p)
        print(f"{p:14} -> {d!r:6} {note}")

Run on all the files, including three I added to stress it:

python delim_detect.py d-comma.csv d-semi.csv d-tab.tsv d-pipe.txt d-single.csv d-text.csv d-semi-quoted.csv d-double-pipe.txt d-fixed.txt

d-comma.csv    -> ','    
d-semi.csv     -> ';'    
d-tab.tsv      -> '\t'   
d-pipe.txt     -> '|'    
d-single.csv   -> None   no candidate gives a consistent width above 1
d-text.csv     -> ','    
d-semi-quoted.csv -> ';'    
d-double-pipe.txt -> '|'    
d-fixed.txt    -> None   no candidate gives a consistent width above 1

Everything ordinary resolves, including d-text.csv and d-semi-quoted.csv, where a semicolon delimited file has commas and a semicolon inside quoted fields. The single column file gets None, which is honest. A parser votes on structure, not on character frequency, so it is not fooled by what is inside the quotes.

Reading the answers together

When the Python sniffer, DuckDB and the parser vote agree, I stop. The three-way agreement on the four ordinary files is the common case. When two agree and one differs, I look at the file. The harder files are where it matters, and they are worth seeing:

python - <<'PY'
import csv
for f in ("d-semi-quoted.csv", "d-double-pipe.txt", "d-fixed.txt"):
    try:
        print(f"{f:18} Sniffer -> {csv.Sniffer().sniff(open(f).read()).delimiter!r}")
    except csv.Error as e:
        print(f"{f:18} Sniffer -> Error: {e}")
PY
for f in d-semi-quoted.csv d-double-pipe.txt d-fixed.txt; do printf '%-18s DuckDB -> ' $f; duckdb -list -noheader -c "SELECT 'delimiter=' || replace(Delimiter, chr(9), '<tab>') || ' columns=' || len(Columns) FROM sniff_csv('$f')" 2>&1 | head -1; done

d-semi-quoted.csv  Sniffer -> ';'
d-double-pipe.txt  Sniffer -> '|'
d-fixed.txt        Sniffer -> Error: Could not determine delimiter
d-semi-quoted.csv  DuckDB -> delimiter=; columns=2
d-double-pipe.txt  DuckDB -> delimiter=| columns=5
d-fixed.txt        DuckDB -> delimiter=, columns=1

The quoted semicolon file is read correctly by both. The file with a double pipe, a||b||c, is read by both as pipe delimited with five columns, alternating real values and empty ones, and my detector says the same. A delimiter of two characters is not in anyone's candidate list, and the failure looks like a success because the width is consistent. If the parsed table has every second column empty, that is the signature. Tell the reader the delimiter explicitly, or replace || with a single character before parsing.

How to tell it worked

Parse the file with the delimiter you found and check three things: the number of columns is what the header suggests, no column is empty in every row, and the first value of a known row looks right. If you got a table with one column, the delimiter is wrong, and the content of that column will contain the real delimiter. If you got a table with many mostly empty columns, you chose a character that happens to be present, like a comma inside free text.

When it works, write the finding down next to the file. The delimiter, the quote character and the encoding belong in one line in a README, and the contract article lists the rest. The next time the same sender changes their settings, you will know what the file used to be.

Files with no delimiter to find

Some inputs are not delimited at all, and no sniffer rescues them. A fixed width file lines columns up with spaces, as in d-fixed.txt. Python's sniffer raised Could not determine delimiter, my detector returned None, and DuckDB answered comma with one column, which would have loaded each line as a single text cell. For those files use pandas.read_fwf or cut -c with the column positions from the specification.

The same applies to files with a multi-character delimiter, like || above or ~|~, and to files that switch delimiters partway through, usually because two exports were concatenated. If widths are inconsistent for every candidate, count the fields by line and find where the pattern changes; checking row and column counts before loading shows how. And if a file claims to be CSV but contains XML tags, JSON or a log format, the right answer is not a delimiter but a different parser.