131

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.

1
  • I really understand that the chosen response do answer your question, but the second one should be highlighted because it offer an global solution, that works acros sharing by email, network, etc.
    – Rafareino
    Sep 10, 2016 at 12:59

9 Answers 9

109

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.

17
  • Thanks, this makes sense. I guess then it just won't interpret tabs as delimiters by default (which also makes sense as CSV stands for Comma Separated Values, but people do use tabs in CSVs in practice and I need to deal with that).
    – Szabolcs
    Jun 11, 2013 at 11:17
  • @Szabolcs Excel won't try to detect the delimiter while opening a CSV file (using file->open or the explorer), it will just use the system setting. It will however manage to detect automatically the delimiter (tab, comma, semicolon or space) while using the import feature.
    – zakinster
    Jun 11, 2013 at 11:29
  • 1
    Your answer was valid on OS X too: it's still the decimal point/comma that causes the "trouble" (as you mention in the answer).
    – Szabolcs
    Jun 11, 2013 at 13:46
  • 1
    Later edit: apologies! this DOES work! Apparently one needs to restart the program and/or log-out/-in again to apply the change. I don't know exactly when, but a short while after my change this suddenly worked.
    – mmo
    Jun 24, 2015 at 17:54
  • 2
    On Mac OS X, don't forget to QUIT Excel after making the changes. Excel remains open if you only close all open sheets. Click Excel > Quit Excel or just hit Command+Q and you're done. Aug 21, 2015 at 10:41
141

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.

5
  • 2
    Make sure to -only- use this to -look- at the file, and not modifying it, otherwise you might run into one of the following issues: theonemanitdepartment.wordpress.com/2014/12/15/…
    – Wouter
    Dec 16, 2014 at 15:53
  • Even better if you need to redistribute this file to others, so you make sure excel will be able to understand the file, no matter what are their configurations. I always use csv to be able to use any version control system to share and colaborate on these files, but have struggled with comma vs semicolons, and always botthered everyone to change their configs, no more. Thanks a LOT!
    – Rafareino
    Sep 10, 2016 at 12:56
  • 1
    Alone on top of what?
    – NateS
    Oct 18, 2016 at 19:13
  • 1
    This is EXACTLY what I needed, thank you! It seems insane to me that you should need a system regional setting for what should be an option in the Excel File Open dialog, maybe, or in Excel's options.
    – Binney
    Jun 3, 2020 at 11:47
  • Is is possible to setup Excel or better format that hack directly to be removed on load?
    – mvorisek
    Dec 23, 2021 at 21:05
19

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.

2
  • Is is possible to setup Excel or better format that hack directly to be removed on load?
    – mvorisek
    Dec 23, 2021 at 21:05
  • In the past i used .tsv files, but latest excel does not use tabs separator in "tab separated files" what is very stupid (old excel version works fine with tabs and tsv). Thanks to your commend I found a way for new excel. But I am A bit unsure how other programs would work with this extra data in the first line, I am afraid most programms would interpred the sep= line as headline or first line of the content
    – Radon8472
    Jan 12, 2023 at 12:34
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.

enter image description here enter image description here enter image description here enter image description here enter image description here enter image description here

2
  • 4
    Very useful! Just remember (as already mentioned somewhere above) that List separator will be ignored if the same symbol is used for Decimal symbol. You will often find , as decimal symbol, so make sure to change it to something else (e.g. .) when setting List separator to ,.
    – Dirk
    Oct 23, 2018 at 14:55
  • not very usefull, if you need excel to read command separated or tab separated files like it should do. Your hint is only usefull, if you tty to establish your own standard
    – Radon8472
    Jan 12, 2023 at 12:36
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.

1

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

enter image description here

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.

0

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.

0

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
0

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).

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .