Why Your CSV Opens as One Column — and How to Fix It

Every row is in column A. The header is one long string. It looks like the file is corrupt, and it almost never is.

The short answer

The file's delimiter is not the one your machine expects — usually semicolons on a system set to commas. Nothing is broken; the reader is guessing wrong. In DataTray, open Data Explorer and drop the file in: it detects the delimiter, states what it found on screen, and lets you change it before the file is read. Then export a copy in whatever dialect you actually need.

Why this happens at all

A CSV file does not record its own delimiter. There is no header, no declaration, nothing inside the file that says "the separator is a semicolon". A program opening one has to guess.

Excel does not really guess — it uses your Windows list separator, which follows your regional settings. Comma in the US and UK, semicolon across much of Europe, because those locales use the comma as the decimal separator and 1,5 already means one-and-a-half.

So a perfectly ordinary file written in Berlin opens as one column in Chicago, and vice versa. Neither file is wrong.

Three ways to fix it

Open it in something that asks. The cleanest option, because it changes nothing about your system and nothing about the file. State the delimiter once, read the file, move on.

Use Excel's import path rather than double-clicking. Data → From Text/CSV gives you a delimiter dropdown. It works, and it is several more clicks every single time.

Change your Windows list separator. Control Panel → Region → Additional settings. This fixes every future double-click and breaks the opposite case, so it is only right if all your files come from one region.

The tell that it is not the delimiter

If changing the delimiter does not fix it, look for these instead:

• Quoted fields containing the delimiter. A value like "Smith, John" is legal and should be handled — but a file that was concatenated or edited by hand may have broken quoting.
• A header block above the data. Some exports put a title and a date on the first few lines. The reader needs to skip them and start at the real header row.
• Tabs, pipes, or fixed-width columns. A .csv extension does not guarantee commas. Files from banks and government systems are frequently fixed-width text wearing a CSV name.

DataTray handles all four, and it shows you what it detected rather than deciding silently.

Make the guess visible

The general principle worth taking away, whatever tool you use: a reader should show you its guess. Delimiter, quote character, encoding, which row the header is on. When those are on screen, a wrong guess takes one click to correct. When they are silent, you get a file in one column and no idea why.

Fix it once for a recurring export

If the same system sends you the same badly-delimited file every month, set the dialect once, save it as a recipe, and re-run it on next month's file rather than rediscovering the problem.

---

Every other free way to do this asks you to upload the file first — which is a lot to ask for a problem that is one dropdown wide.