Why does my CSV open in one column in Excel?
Quick answer
This happens when the file uses a different delimiter (usually a semicolon) than the comma Excel expects by default in your regional settings - common with files exported from systems set up for a European locale, where a comma is the decimal separator and a semicolon separates fields instead. Converting the file with the correct delimiter detected, or using Excel's Text to Columns feature, fixes it.
- 1Convert the CSV to Excel - the delimiter is detected automatically regardless of which one was used.
- 2Or, in Excel, select the column and use Data > Text to Columns, choosing the correct delimiter.
Free, no sign-up, and your file is read on your own device rather than uploaded.
A CSV file has to use some character to separate one value from the next, and while a comma is the most common choice in English-speaking countries, plenty of systems - especially anything configured for a European locale, where a comma is already used as the decimal point - use a semicolon instead. Excel opens a plain .csv using whatever delimiter your own regional settings expect, and if that does not match the file, every value on a row gets treated as one long piece of text in a single column.
The file is not corrupted and no data is missing - it is just been split in the wrong place, or not split at all.
Step by step
Recognise the symptom
Every row is technically all in column A, usually still visibly separated by commas or semicolons within the text itself.
Convert with the delimiter detected automatically
The correct separator is detected by checking which one consistently splits the file into the same number of columns.
CSV to ExcelCheck the result
Each value should now be in its own column.
Tips
- If you regularly receive files from the same source with the wrong delimiter, it is worth asking whether they can export using a comma - though converting on your end works just as well and does not depend on anyone else changing anything.
- Excel also has a "sep=;" first-line trick some exporters use, which tells Excel explicitly which delimiter to expect - if you see that as the very first line of the file, it is a hint about which delimiter is actually in use.
Common problems
Text to Columns worked, but now dates or numbers look different.
A semicolon-delimited file often comes from a locale that also formats numbers and dates differently (comma as decimal point, day before month) - check those columns after splitting rather than assuming the values transferred exactly.
Frequently asked questions
- Is my CSV file broken if it opens in one column?
- No - the data is intact, it is just been split in the wrong place because the delimiter did not match what Excel expected.
- Why do some CSV files use a semicolon instead of a comma?
- Locales where a comma is the decimal separator (much of continental Europe, for example) commonly use a semicolon to separate CSV fields instead, to avoid ambiguity with decimal numbers.
Related data tools
Related articles
- Excel & Data
Why does Excel drop leading zeros from my CSV?
Excel treats every CSV value as "General" format when it opens the file, which means anything that looks like a number is read as one - and a real number can never start with a zero, so it is dropped. Converting the CSV to Excel with those columns explicitly typed as text, rather than opening the CSV directly, avoids the problem entirely.
- Excel & Data
Why does Excel show my CSV numbers as 1.23E+12?
Excel's number type reliably stores about 15 significant digits. A 16-digit card or account number read in as a number gets rounded to fit and displayed in scientific notation - and the digits past the 15th are genuinely gone, not just hidden behind the display format. The fix is to keep that column as text from the start, rather than trying to reformat it back afterward.
Ready to do it?
Free, no sign-up, and nothing is uploaded to a server.