Sheet ReserveSpreadsheets worth keeping.

Formats

Why does Excel put my whole CSV in one column?

Short answer

Excel splits a CSV using the list separator from your computer's regional settings, which is a semicolon in many countries. If the file uses commas, or the reverse, every row lands in column A. Import the file with Data > From Text/CSV and pick the right delimiter, or use Text to Columns on the pasted data.

Updated

When you double-click a .csv file, Excel does not inspect the file to find the delimiter. It uses the list separator defined in your operating system's regional settings. In the United States and the United Kingdom that is a comma. In countries that write decimals with a comma, such as Germany, France and Turkey, the list separator is a semicolon. A file that uses the other character opens with each row as one long text string in column A.

How do I import a CSV with the right delimiter in Excel?

This works in current Excel for Microsoft 365 and does not change any settings.

  1. Open a blank workbook. Do not double-click the CSV.
  2. Choose Data > From Text/CSV and select the file.
  3. In the preview, set Delimiter to Comma, Semicolon, Tab or another character. Check that the columns look right.
  4. Click Load, or Transform Data if you need to change column types first.

How do I split CSV data that is already in one column?

  1. Select the column that holds the text.
  2. Choose Data > Text to Columns.
  3. Select Delimited and click Next.
  4. Tick the delimiter that appears in your data, such as Comma, and click Finish.

Text to Columns also guesses types, so leading zeros and long numbers can be altered. In step 3 of the wizard you can set a column to Text.

How do I change the Windows list separator for CSV files?

If every CSV you receive uses the other separator, change the setting once.

  1. Open the Control Panel (or search the Start menu for "Region") and choose Region.
  2. Click Additional settings.
  3. Change the List separator to a comma or semicolon and click OK.

This changes how Excel and some other Windows programs read and write CSV, so use it only if that suits your other work.

Can a sep line in the CSV tell Excel which delimiter to use?

Add a first line containing exactly sep=, (or sep=;). Excel for Windows uses it as the delimiter and hides the line. Other programs will show it as a data row, so use this only when the file is meant for Excel.

How do I set the separator for a CSV in Google Sheets or Calc?

  • Google Sheets: File > Import > Upload, then set Separator type to Detect automatically, Comma, Semicolon, Tab or Custom.
  • LibreOffice Calc: File > Open. The Text Import dialog appears with Separated by options; tick the right ones and check the preview.

Why does a CSV saved in a German-locale Excel use semicolons?

This line, saved in a German-locale Excel, opens correctly only with semicolons:

Name;Preis
Stift;1,50

The same data with commas as delimiters would read Stift,1,50 and be split into three fields, because the decimal comma looks like a delimiter. The CSV writer must either quote the value as "1,50" or use a semicolon as the delimiter. This is why files exported from European systems often use semicolons.

What else can go wrong when a CSV opens in Excel?

  • If the preview shows odd characters such as é, the encoding is wrong, not the delimiter. In Data > From Text/CSV, change File Origin to 65001: Unicode (UTF-8).
  • A file with a few commas inside quoted text fields splits correctly only if those fields are wrapped in double quotes.
  • After any fix, save as XLSX to keep the layout. Re-saving as CSV uses your regional separator again. For more on that, see how to convert XLSX to CSV without losing data and the guide to opening CSV in Excel without breaking data.