130

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.

Szabolcs
  • 2,857
  • 5
  • 25
  • 42
  • 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

9 Answers9

137

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.

O'Rooney
  • 287
  • 2
  • 14
user280725
  • 1,479
  • 1
  • 9
  • 2
  • 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: http://theonemanitdepartment.wordpress.com/2014/12/15/the-absolute-minimum-everyone-working-with-data-absolutely-positively-must-know-about-file-types-encoding-delimiters-and-data-types-no-excuses/ – 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
  • 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 03 '20 at 11:47
  • Is is possible to setup Excel or better format that hack directly to be removed on load? – mvorisek Dec 23 '21 at 21:05
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.

yosh m
  • 2,226
  • 3
  • 25
  • 28
zakinster
  • 2,509
  • 1
  • 15
  • 13
  • 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
  • I ended up looking for a different program to view these files quickly and went with this: https://code.google.com/p/quicklook-csv/ Otherwise you are correct about why it wouldn't handle commas. – Szabolcs Jun 11 '13 at 11:50
  • @Szabolcs Since you're on OSX and not on Windows, I updated my answer to be less specific, and I included your suggestion for potential future visitors. – zakinster Jun 11 '13 at 12:13
  • 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
  • Excel may be observing the Windows convention of using file extensions to indicate file contents. Handing it a tab-separated file with a CSV extension is like handing your friend a bowl labeled "Sugar" but putting salt in it. Good way to lose a friend! I don't think Excel has any specific extension that it associates with Tab separated files. – Steve Rindsberg Dec 10 '13 at 14:49
  • I already have that set as the system delimiter, but excel still doesn't auto-delimit. – Elliot Sep 23 '14 at 20:41
  • Same here: I have adjusted the list separator to be ',' but Excel dumps everything into the first column. Esp. when having to wade through many files always having to go through the import wizard is SOOOOO tedious! Is there really no way to redefine the defaults? – mmo Jun 24 '15 at 17:47
  • 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
  • 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. – Paulo Pedroso Aug 21 '15 at 10:41
  • Sadly doesn't work for me... – Frederik Witte Feb 19 '16 at 10:04
  • @FrederikWitte could you specify what system you're using and what version of Excel ? This answer may need an update. – zakinster Feb 19 '16 at 14:49
  • @zakinster Doesn't work for me either, with Excel v15.18 on OS X 10.10.5. The locale decimal character is set to `.`, but Excel uses a semicolon. (And also, annoyingly, writes ISO-8859-1 output.) – slhck Feb 23 '16 at 15:01
  • Got it – seems to be a bug with Excel 2015. – slhck Feb 23 '16 at 15:11
  • Damn.. Did you find a workaround @slhck? – Frederik Witte Mar 10 '16 at 13:22
  • @FrederikWitte Not really, I've used a Windows computer in the meantime. LibreOffice would be an alternative, though. – slhck Mar 10 '16 at 20:14
  • Excel on Mac OS X behaves a little more complicated. Excel not only takes the actual decimal separator into account, but also the default decimal separator of the chosen locale formatting language. For example, one would expect that if the grouping and decimal separator set to "," and "." respectively, the CSV list separator is set tot simply ",". This is true if your locale is set to en (English), but false if your locale is set to nl (Dutch). More info at http://www.macfreek.nl/memory/Decimal_Seperator_in_Mac_OS_X#Microsoft_Excel. – MacFreek May 29 '16 at 22:29
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.

Vylix
  • 1,716
  • 4
  • 22
  • 26
Stephen Hemingway
  • 191
  • 1
  • 3
  • Is is possible to setup Excel or better format that hack directly to be removed on load? – mvorisek Dec 23 '21 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 '23 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

Sam B
  • 447
  • 4
  • 8
  • 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 '18 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 '23 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.

robinCTS
  • 4,327
  • 4
  • 20
  • 29
Eric VDB
  • 31
  • 2
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.

Ogglas
  • 1,754
  • 5
  • 20
  • 35
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.

edj
  • 161
  • 1
  • 3
  • 6
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
AzizD
  • 1
  • 2
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).
Paul Ogilvie
  • 193
  • 1
  • 8