Why Excel Shows a CSV in One Column and What Causes It

When a CSV opens in Excel as a single column, with commas or semicolons visible in the text of each cell, the file is almost never broken. Excel made a decision about how to split it, and the decision did not match the file. There are five different ways to land there, and they look the same on screen, which is why advice that fixes one of them ("use Text to Columns") fails on the next. The answer is to look at the file, not the symptom. This article explains how Excel decides, lists the five causes, and gives you a ten-line script to tell them apart. For the repair steps once you know the cause, see opening CSV files in Excel and Sheets without damage.

How Excel picks the delimiter

A CSV carries no marker saying which character separates the fields. RFC 4180 says comma and the registered media type says the same, but in practice a CSV is whatever the producer decided, and the reader has to guess. Excel does not look at the file. When you double-click a .csv, it splits on the list separator from the operating system's regional settings. On a United States or United Kingdom machine that is a comma. In Germany, France, Italy, Spain and many other regions it is a semicolon, because those regions write decimals with a comma and need something else to separate fields.

Two consequences follow. A file written by a French Excel is semicolon separated and opens as one column in New York. A file written by an American script is comma separated and opens as one column in Paris. And the same file opens correctly on one colleague's machine and wrongly on the next, which is the signature of this class of problem. The file did not change, the setting did.

The import route (Data > From Text/CSV) looks at the file, shows a preview and lets you choose, which is why it avoids most of this. The double-click and the old File > Open do not.

Five causes of one column

All five show the same screen. Read the first line of the file in a plain text editor, or let a script do it, and match it to one of these.

A delimiter mismatch. The file uses a character that is not the machine's list separator. This is the common case: a comma or semicolon file on the wrong machine. The first line looks like id;name;qty or id,name,qty, and the cure is to import with the correct delimiter (opening a semicolon separated CSV in Excel has the steps).

Every line is wrapped in quotes. The first line is "id,name,qty" with one opening quote at the start and one closing quote at the end. The exporter treated the entire line as one string and quoted it. A CSV parser correctly reads that as a single field containing commas, so no delimiter setting helps, and Excel shows one column. The file is double-encoded. It typically comes from a report tool that joined the fields with commas and then passed the whole line through a CSV writer. The fix is to strip the outer quotes before parsing.

A tab or a pipe. The first line is id<TAB>name<TAB>qty or id|name|qty. Tab separated files are common as .tsv and often saved as .csv or .txt by tools that expect Excel to cope. Excel's import dialog offers both as delimiters, and the double-click does not (TSV versus CSV covers why tabs are popular).

UTF-16. Some Windows tools write "Unicode text", which is UTF-16 little-endian with a byte order mark, and Excel's own Save As > Unicode Text produces a tab-separated UTF-16 file. Opened as UTF-8, the text is full of null bytes, and delimiters are not recognized where the parser expects a single byte. Excel usually detects the byte order mark and copes, and other readers do not. If the file's first two bytes are FF FE, it is UTF-16.

**A sep= directive that names something else.** A first line sep=; tells Excel to use a semicolon for the file. If the directive says sep=; and the data uses commas, or if another tool dropped the line into the middle, the result is one column. A related trap is that the directive line itself is not data, and a script that reads the file sees it as the header.

A ten-line diagnostic

Counting delimiters in the first line identifies four of the five instantly. Here is the script, which also guesses the encoding from the first bytes and checks for the wrapped-line and directive cases:

import sys

raw = open(sys.argv[1], "rb").read(4096)
if raw.startswith(b"\xef\xbb\xbf"):
    enc = "utf-8-sig"
elif raw[:2] in (b"\xff\xfe", b"\xfe\xff"):
    enc = "utf-16"
else:
    enc = "utf-8"
first = raw.decode(enc, errors="replace").splitlines()[0]
print("encoding guess:", enc)
print("first line:", first)
for name, ch in [("comma", ","), ("semicolon", ";"), ("tab", "\t"), ("pipe", "|")]:
    print(f"{name:10} {first.count(ch)}")
print("whole line quoted:", first.startswith('"') and first.endswith('"') and first.count('"') == 2)
print("sep= directive:", first.lower().startswith("sep="))

Run against a healthy comma file and then the file whose lines are wrapped in quotes:

python3 diag.py good.csv
python3 diag.py wrapped.csv

encoding guess: utf-8
first line: id,name,qty
comma      2
semicolon  0
tab        0
pipe       0
whole line quoted: False
sep= directive: False

encoding guess: utf-8
first line: "id,name,qty"
comma      2
semicolon  0
tab        0
pipe       0
whole line quoted: True
sep= directive: False

The comma count is identical, which is why the symptom hides the difference. Only whole line quoted: True separates a good file from a double-encoded one. For the other three: a tab file prints tab 2, a sep=; file prints sep= directive: True, and a UTF-16 file prints encoding guess: utf-16. A short list of delimiter counts, one dominant and the others zero, is what a clean file looks like. Two close counts, say 3 semicolons and 4 commas, means a file with decimal commas inside a semicolon file, and you need a parser that knows about quoting rather than a count. For files that are not obvious, telling what delimiter an unknown CSV file uses goes further.

Fixing each case

Match the repair to the cause, because they differ.

For a delimiter mismatch, Data > From Text/CSV and choose the delimiter, or Data > Text to Columns on the open sheet. For tabs and pipes, the same dialogs have checkboxes.

For a wrapped file, strip one layer of quotes and let the CSV parser split the result. The simplest way to do it without hand-editing is to read each line, remove the first and last character, and write the line back out. In Python, that is a loop over open("wrapped.csv") writing line.rstrip("\n")[1:-1]. If doubled quotes appear inside (""), they were doubled by the second encoding too and need to collapse to one. Check a few rows by eye before and after, and see how CSV quoting works for what the escaping rules say.

For UTF-16, convert to UTF-8 with iconv -f UTF-16 -t UTF-8 in.csv > out.csv, or tell Excel the file origin during the import. See detecting the encoding of a CSV file when the origin is unknown.

For a stray sep= line, delete it if no Excel user depends on it, or keep it and make sure the character it names is the one the data uses.

When the file really has no delimiter

Sometimes a single column is the correct reading. A fixed width report has no delimiter at all, only columns that line up by position, and Excel's Text to Columns has a Fixed width option for it. A log file with one message per line is one column by design, and so is a list of identifiers. A report-style export with title lines above the data has a single cell of text in those rows and real columns below; skip the top lines or parse the multi-line header instead of splitting the whole file.

One more case is a file that Excel did split correctly and that looks wrong because the column width hides it. A column of long text with commas can look like unsplit data in a narrow cell; click the cell and read the formula bar. And a file pasted from a web page or an email is not a CSV file at all; the clipboard text is one column per line until you run Text to Columns on it. When in doubt, count the delimiters in the first line and decide from the numbers. Microsoft's description of the two import routes is in import or export text files.