93

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.

  • 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 '16 at 12:59
78

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.

  • 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 '13 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 '13 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 '13 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 '15 at 17:54
  • 1
    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. – Paulo Pedroso Aug 21 '15 at 10:41
91

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.

  • 1
    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 '14 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 '16 at 12:56
  • 1
    Alone on top of what? – NateS Oct 18 '16 at 19:13
10

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.

4

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
    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 '18 at 14:55
1

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.