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

Question

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.

score 145 · Answer 1 · edited Sep 19 '17 at 23:43

If you are not looking to modify the format of the file, and are ONLY targeting Excel, you can use the following Excel trick to help you.

Add a new line at the top of the file with the text "sep=," (including quotes) in order for Excel to open the file with "," as the list separator.

It´s a very easy trick to avoid changing your Windows regional settings and get a consistent result. But it is Excel specific.

score 111 · Accepted Answer · edited May 22 '17 at 14:50

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

Click the Windows Start menu.

Click Control Panel.

Open the Regional and Language Options dialog box.

Click the Regional Options Tab.

Click Customize / Additional settings (Win10).

Type a new separator in the List separator box.

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.

score 20 · Answer 3 · edited Jun 02 '17 at 15:02

20

You don't need the quotes around the sep=, - as long as it's the first line of the file it'll work, at least with Excel 2016.

I discovered that if the file is tab delimited, sep=\t works fine, with and without the quotes.

edited Jun 02 '17 at 15:02

Vylix

1,935

answered Jun 02 '17 at 12:27

Stephen Hemingway

301

score 10 · Answer 4 · answered Jul 21 '18 at 11:55

10

Accepted answer is correct but I am a visual person. Here is every single step in screenshot format of how to do this in windows 10.

answered Jul 21 '18 at 11:55

Sam B

457

score 3 · Answer 5 · edited Jun 25 '18 at 15:09

3

When the separator in the regional settings is not a comma but a semicolon (Dutch separator), rename the CSV file to a TXT file. Right-click the TXT file and select "Open with" and select "Excel". In Excel select the first column, select data in the ribbon and separate text to columns.

OR

Install LibreOffice and open the CSV file with LibreOffice Calc.

edited Jun 25 '18 at 15:09

robinCTS

4,407

answered Jun 25 '18 at 14:46

Eric VDB

31

score 1 · Answer 6 · answered Sep 23 '19 at 12:03

I had a .csv file with separator ; and regional settings set to List separator ;. However Excel still did not parse the columns.

It turned out to be an encoding issue. The files were exported from SQL server Management Studio 2005 and become encoded in UCS-2 LE BOM. Using Notepad ++ I switched encoding to UTF-8 and everything started working.

score 0 · Answer 7 · answered Jun 12 '17 at 17:22

Be sure to inspect the CSV file in a simple editor like Notepad to verify it is properly formatted.

I added this answer after I solved a stupid bug wherein CSV files I created with VB weren't opened with separate columns in Excel. I discovered that the way I had written the lines wrapped each line with quotation marks. Excel hid the quotation marks and showed the whole line in column A, making it appear as though it ignored my comma separators.

score 0 · Answer 8 · answered Jul 31 '17 at 06:30

best way will be to save it in a text file with csv extension

Sub ExportToCSV()
    Dim i, j As Integer
    Dim Name  As String

    Dim pathfile As String


    Dim fs As Object
        Dim stream As Object

        Set fs = CreateObject("Scripting.FileSystemObject")
    On Error GoTo fileexists

    i = 15
    Name = Format(Now(), "ddmmyyHHmmss")
    pathfile = "D:\1\" & Name & ".csv"

    Set stream = fs.CreateTextFile(pathfile, False, True)

fileexists:

    If Err.Number = 58 Then
        MsgBox "File already Exists"
        'Your code here
        Return
    End If
    On Error GoTo 0

    j = 1
    Do Until IsEmpty(ThisWorkbook.ActiveSheet.Cells(i, 1).Value)

        stream.WriteLine (ThisWorkbook.Worksheets(1).Cells(i, 1).Value & ";" & Replace(ThisWorkbook.Worksheets(1).Cells(i, 6).Value, ".", ","))

        j = j + 1
        i = i + 1
    Loop

stream.Close

End Sub

Paul Ogilvie · Answer 9 · 2018-01-24T07:52:55.563

For Dutch I had a problem that Excel 2008 on Windows 7 did not adhere to RFC4180:

"Fields containing line breaks (CRLF), double quotes, and commas should be enclosed in double-quotes."

A comma-separated file correctly had fields with a comma (Dutch decimal separator) enclosed in double quotes. In the Dutch locale the decimal separator is , and list separator is ; so Excel could not read a file with , used for both (not even after explicitly changing the list separator to , in Control Panel).

The input looks like:

"06-07-2017 17:03:18","********",0:01:04,Uitgaand,"0,1879","0,2273","0,0395",21

The solution was given by @user280725:

Use Notepad to insert as a first line:

sep=,

(This means the Note of user @zakinster in his solution no longer needs to apply.)

Interesting: with the csv file still loaded, if you now set the locale to US English in Control Panel and save the file, it will have been converted to US English format (comma as list separator and dot as decimal separator).

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

9 Answers9

Linked