Guides

CSV Opens in One Column in Excel? Here's Why and How to Fix It

By the Opener team5 min read

When a CSV opens with everything squeezed into column A, the file uses a different separator than the one Excel expects — usually commas in a file opened on a computer set up for semicolons, or the other way round. Nothing is wrong with the data. Open it with a tool that detects the separator, such as the CSV viewer, or tell Excel which separator to use through its import or Text to Columns feature.

Commas, semicolons and the decimal comma

“CSV” stands for comma-separated values, but in practice the separator is whatever the program that wrote the file chose. Here is the same row written both ways:

Date,Customer,Amount
2024-03-15,Müller GmbH,1250.50
Date;Customer;Amount
2024-03-15;Müller GmbH;1250,50

The reason for the second version is the decimal separator. In much of Europe — Germany, France, Italy, Spain, the Netherlands, Poland, Ukraine and many other countries — numbers are written with a decimal comma: 1250,50. If the comma also separated the columns, 1250,50 would split into two cells. So spreadsheet programs in those regions use the semicolon as the column separator instead.

Neither format is wrong. They are just two conventions, and the trouble starts when a file written with one is opened by a program expecting the other. To learn more about the format itself, see what a CSV file is.

Why Excel puts everything in column A

When you double-click a .csv file, Excel does not look at the file to work out its separator. It uses the list separator from your operating system’s regional settings. On Windows, that setting follows your region: a computer set to a decimal-comma country typically has a semicolon list separator, and one set to the United States or the United Kingdom has a comma.

So the clash happens in both directions:

The same applies to exports from web services, online shops and CRMs. Many always write commas regardless of where you are, which is why European users run into this so often. A related problem — letters turning into symbols like é — is about encoding rather than separators; see strange characters in a CSV.

Fix 1: open it with automatic separator detection

If you just need to read the file — check an order, look up a customer, see what an export contains — the fastest route is a viewer that works out the separator itself.

The Opener CSV viewer looks at the file and detects whether it is separated by commas, semicolons, tabs or pipes, then shows a proper table. It also detects the text encoding (UTF-8, Windows-1251 or Windows-1252) and handles quoted values that contain the separator, such as "Smith, John". Nothing to configure, no account, and the file stays in your browser — it is never uploaded. You can search, sort and export the result as CSV or JSON.

Fix 2: use Excel’s import or Text to Columns

To work with the data in Excel, tell it which separator to use instead of double-clicking.

Import from Text/CSV. Open a blank workbook, go to the Data tab and choose “From Text/CSV”. Excel shows a preview with a Delimiter list; pick Comma or Semicolon until the columns look right, then load the data. The same preview lets you set the file’s encoding if letters look garbled.

Text to Columns. If the data is already sitting in column A:

  1. Select column A.
  2. On the Data tab, choose Text to Columns.
  3. Pick Delimited, then tick the separator your file uses (for example Semicolon or Comma).
  4. Finish the wizard.

One thing to watch with both methods: number formats. A file with 1250.50 opened on a decimal-comma system may read the amount as text or as the wrong number. The import preview and the last step of the Text to Columns wizard let you set how columns are interpreted — check a few amounts before you rely on totals.

Fix 3: add a “sep=” line for Excel

There is an Excel-specific trick: add a line at the very top of the file that names the separator.

sep=;
Date;Customer;Amount
2024-03-15;Müller GmbH;1250,50

Open the file in a plain text editor, add sep=; (or sep=, for a comma file) as the first line, save, and double-click it again. Excel reads the hint, uses that separator and does not show the line itself.

It is a workaround, not a standard. Other programs — Google Sheets, LibreOffice, databases, scripts — do not know about sep= and will usually show it as an extra first row or treat it as the header. Use it for files that only ever go to Excel users.

Fix 4: Google Sheets, LibreOffice or your regional setting

Google Sheets. Use File → Import and upload the CSV. The import dialog has a separator type option where you can leave detection on or choose the separator yourself, including a custom character.

LibreOffice Calc. When you open a CSV, LibreOffice shows a text import dialog with checkboxes for comma, semicolon, tab and other separators, and a live preview. It is free on Windows, macOS and Linux.

Changing the Windows list separator. In the classic Control Panel’s region settings, the additional settings include a List separator field. Setting it to a comma makes Excel open comma-separated files directly on double-click. Use this with care: it is a system-wide setting, so it also changes how Excel separates function arguments in formulas and how other programs read and write CSV files. If you share files with colleagues in your country, they will start receiving commas from you. For most people, the import methods above are safer.

Frequently asked questions

Why does the same CSV open fine on my colleague’s computer?

Their regional settings use a different list separator. Excel uses that setting when you double-click a CSV, so the same file can open correctly on one computer and land in a single column on another.

Should I save my CSV files with commas or semicolons?

It depends on who opens them. For other programs, scripts and international colleagues, commas are the common default. For Excel users in decimal-comma countries, semicolons open directly. If you can, ask the recipient.

Why do some rows still have the wrong number of columns?

Usually because a value contains the separator itself, such as a company name with a comma that was not wrapped in quotes. Viewers and importers that follow CSV quoting rules handle quoted values; unquoted ones need fixing at the source.

Does the CSV viewer work with tab-separated and pipe-separated files?

Yes. The CSV viewer detects commas, semicolons, tabs and pipes automatically and opens .csv, .tsv and .txt files.