Why your CSV opens as one column, and what that means.
The single most common CSV complaint has nothing to do with the file, and the second most common one is invisible until somebody searches for a customer.
- Read5 min
- Sections3
Why does my CSV file open in one column in Excel?
Because it is separated by something your copy of Excel is not expecting, usually a semicolon. Excel both reads and writes CSV using the list separator from your operating system's regional settings, which is a semicolon across much of Europe because the comma is the decimal mark. The file is not broken and needs no repair; it needs to be opened through the import dialogue with the right delimiter.
01
The separator is a regional setting
Excel uses your operating system's list separator when reading and writing CSV. On a machine set to most European locales that is a semicolon, because the comma is already doing duty as the decimal mark.
So the same file genuinely opens correctly on one colleague's laptop and as a single column on another's, and neither of them has done anything wrong. It also means a CSV exported from Excel on one machine may not open cleanly on another.
Do not fix it by find-and-replacing semicolons with commas. If any text field contains a comma, you will have converted a working file into a broken one.
Open it through the data import dialogue rather than by double-clicking. That is the only path that lets you choose the delimiter before the file is read.
02
The encoding problem you will not notice
A file written as Windows-1252 and read as UTF-8 turns an accented character into a pair of symbols. It affects text only, so every number reconciles perfectly and the file looks correct until somebody searches for a name.
This is why it survives review. The person checking looks at the totals, and the totals are right.
If a file has come from a system you do not control, look specifically at the text columns for characters outside the alphabet before trusting anything. Fixing an encoding after the file has been edited and saved is considerably harder than catching it on the way in.
03
Quoted line breaks split rows
An address or comment field containing a line break is legal inside quotes. A parser that splits on newlines first — which many hand-rolled ones do — produces extra rows with the columns shifted sideways, and the only clue is a row count that is higher than you expected.
The signature is a handful of rows where a text field contains what looks like the start of the next record. Check the row count against the source system before anything else.
See it on a real project
Product A produced 68% of total growth while repeat purchasing fell from 31% to 24%.
What is making this store's growth less durable?Related
Try it on your own file.
Every check described here runs automatically, and your first findings arrive in full before you pay for any of it.
No account needed to start. You only pay when you like what you see.