CSV in one column in Excel: how to open it properly
The file is fine, Excel works, and yet the whole table has landed in column A. The cause is almost always the same: the file and the program disagree about what counts as a delimiter.
Where the mismatch comes from
The name of the format promises a comma. But in locales where the comma is the decimal separator, a number like “10,5” would be split into two columns if the comma also separated fields. So Excel in those regions, and most local software, uses a semicolon.
Which delimiter Excel expects is set by the Windows regional settings — the “List separator” parameter. The file knows nothing about that setting.
Four ways to fix it
- Import instead of open. Data → From Text/CSV, and Excel asks for the delimiter itself. You can assign column types at the same time, which also saves codes with leading zeros.
- A hint line. If you add
sep=;as the first line of the file, Excel recognizes it and applies that delimiter. Other programs, however, will take the line for data. - Change the delimiter in the file. A blind search-and-replace is dangerous: a delimiter inside a quoted field gets replaced too, and the table falls apart. It must be done by a tool that understands quotes.
- Do not open it in Excel. A viewer that detects the delimiter from the content removes the question altogether.
How to detect the delimiter reliably
The trick is simple: try the candidates — semicolon, comma, tab, pipe — and for each one count the fields in the first couple of dozen lines. The right delimiter gives the same number of fields on every line.
An important subtlety: count with quotes taken into account. An address such as "221B Baker Street, London" contains a comma, and without parsing the quotes the statistics break — and the detection with them.
If there is no confident answer (the rows really are ragged), it is more honest to say so plainly and let the user switch by hand than to show a wrong table in silence. That is how the file-parsing panel in Tabulens works: the delimiter is detected, and changing it rebuilds the table at once.
Neighboring symptoms
If the delimiter is detected and the table still looks strange, check two other signs:
- the columns shift starting from some row — there is an unpaired quote somewhere, and parsing continues with an offset;
- accented letters turn into symbols — that is not about the delimiter but about encoding;
- some rows have stuck together — a field contains a line break, and the parsing goes line by line without regard to quotes.
FAQ
Why does the same file open fine for my colleague?
Their Windows regional settings are different. The file is the same; the environment is not.
Can I change the delimiter for the whole system?
Yes, in the Windows region settings. But it affects every program — importing is the better route.
What should I choose when exporting for colleagues?
If they use Excel in a region with the decimal comma: a semicolon and UTF-8 with BOM, so that the file opens on a double-click with correct characters.
Free for personal use. Your file is not uploaded to a server. Windows version — 3.5 MB, no installation: details. Organizations: license.