Excel – How to get Excel to interpret the comma as a default delimiter in CSV files

csvmicrosoft excel

I have a number of .csv files. Some of them are comma delimited, some are tab delimited (maybe they should be called .tsv …)

The csv extension gets associated with Excel when Excel is installed. However, if I open one of these files with excel, everything gets dumped into the same column and the comma is not interpreted as a delimiter.

I can instead of File -> Import…, select the file, and choose the precise way to interpret the contents (delimiters, data types, etc.) But most of the time I just want to look at the file through a clear table view. I do not want to process it with Excel.

Is there a way to get Excel to auto-interpret the delimiter and show the CSV file as a proper table as soon as it's opened? I need this so I can use Excel as a quick viewer for such files.

I suspect there must be a way, otherwise Excel wouldn't associate itself with CSV files.

Best Answer

While opening CSV files, Excel will use a system regional setting called List separator to determine which default delimiter to use.

Microsoft Excel will open .csv files, but depending on the system's regional settings, it may expect a semicolon as a separator instead of a comma, since in some languages the comma is used as the decimal separator. (from Wikipedia)

On Windows, you can change the List separator setting in the Regional and Language Options as specified on the Office support website :

Change the separator in a CSV text file

  1. Click the Windows Start menu.
  2. Click Control Panel.
  3. Open the Regional and Language Options dialog box.
  4. Click the Regional Options Tab.
  5. Click Customize / Additional settings (Win10).
  6. Type a new separator in the List separator box.
  7. Click OK twice.

Note: this only works if the Decimal symbol is not also designated as comma (in line with the Wikipedia citation above). If it is, Excel will not use comma as the List separator, even if chosen. For many non-United States regions, comma is the default Decimal symbol.

On Mac OS X, this setting seems to be deduced from the decimal separator setting (in the Language & Region pane of System Preferences, go to Advanced). If the Decimal Separator is a point then the default CSV separator will be a comma, but if the Decimal Separator is a comma, then the default CSV separator will be a semicolon.

As you said yourself in the comment, there is an alternative for Mac users to quickly look at those CSV files. It's plugin for Quick Look called quicklook-csv that handles separator detection.