Marrying CSV file type with Microsoft Excel with SharePoint as the Chaperone

Version: 1.3, Modified On: 03/25/2015

The title of this post can be read as following in SharePoint terms:
How to open CSV files in Microsoft Excel when using CSV from SharePoint?

Scenario

.CSV file type is not tied with Microsoft Excel in SharePoint. When users click on a CSV file in a SharePoint Library, it opens the CSV file in Notepad on the user’s computer. In contrast, if user clicks on a .CSV file on their PC, it opens the file in Microsoft Excel.

Antoher scenario where a user has the CSV file checked out, but he closed the CSV file without checking in the file. Now the users clicks on the checked out CSV and it opens in the notepad.

Another issue, the CSV files don’t have any icon associated with the file in SharePoint.

CSV File in SharePoint – Default Behavior

Let’s review the default behavior of CSV file in SharePoint. We have a test CSV file already uploaded to a library in SharePoint.

1. You can see there is no descriptive icon associated with the CSV file unlike Excel file. See the Excel file and .TXT file has relevant icon.

SharePoint - CSV File with no icon - Default behavior
SharePoint – CSV File with no icon – Default behavior

2. Click on the CSV file and you will get a prompt to open, save or save as the file

SharePoint - CSV File type clicked - Default behavior
SharePoint – CSV File type clicked – Default behavior

3. Click Open and it will open the CSV file in Microsoft Excel.

SharePoint – CSV File Opened in MS Excel – Default behavior

4. Now let’s check out the CSV file

SharePoint - Check Out CSV File - Notice Edit in Excel not an Option in menu - Default behavior
SharePoint – Check Out CSV File – Notice Edit in Excel not an Option in menu – Default behavior

5. Click on the checked out CSV file

SharePoint – Check Out CSV File – Click to open – Default behavior

6. Click ok on the Open Document dialog box

SharePoint - Check Out CSV File opens Document dialog box - Default behavior
SharePoint – Check Out CSV File opens Document dialog box – Default behavior

7. The opens up in Notepad!!!

SharePoint - Check Out CSV File opens up in Notepad - Default behavior
SharePoint – Check Out CSV File opens up in Notepad – Default behavior

We will review the inside behavior and how we can enhance the user experience for the CSV files.

Different Areas To Review and Resolution

We’ll review few areas related to this issue:

a. Default Programs settings on the user’s PC
i. Set your default programs
ii. Associate a file type or protocol with a program
b. IIS Web Server MIME Settings for .CSV on SharePoint Servers
c. Adding line item for CSV file and behavior in DocIcon.xml on SharePoint Servers
d. Changing Hkey_Classes_Root.csv on user’s PC

a. Default Programs settings on the user’s PC

Excel is the default program for CSV and is usually not the issue for the .CSV file to open in Microsoft Excel when the CSV file is on the PC or network share.

i. Set your default programs

1. Go to Control Panel > Default Programs
2. Click on Set your default programs

CSV File Association - Control Panel > Default Programs
CSV File Association – Control Panel > Default Programs

3. Click on Excel from the list of program on left side. Then click on ‘Choose defaults for this program’

CSV File Association – Control Panel > Default Programs – Excel Defaults

4. Make sure the csv extension is checked for Excel. Then click Save.

CSV File Association – Control Panel > Default Programs – Excel Defaults

ii.    ii. Associate a file type or protocol with a program

Let’s review the file type association under Default Programs.

1.  Go to Control Panel > Default Program
2. Click on ‘Associate a file type or protocol with a program’

CSV File Association - Control Panel > Default Programs - Associate File Type or Protocol
CSV File Association – Control Panel > Default Programs – Associate File Type or Protocol

3. On next page, make sure .csv file type is associated with Excel. Click on Change Program button if it is not or if you want to change it.

CSV File Association - Control Panel > Default Programs - CSV File Type Associate
CSV File Association – Control Panel > Default Programs – CSV File Type Associate

4. From Open With dialog screen, choose Excel. If Excel is not listed then expand ‘Other Programs’ section by clicking on the small arrow on right side. If Excel is not listed there then use Browse button to go to folder location where Microsoft Excel is installed and select the Excel.exe executable file. Make you to check the box that says ‘Always use the selected program to open this kind of file’

CSV File Association - Control Panel > Default Programs - CSV File Type Association with Excel
CSV File Association – Control Panel > Default Programs – CSV File Type Association with Excel

5. Click OK and close Default Program window.

b. IIS Web Server MIME Settings for .CSV on SharePoint Servers

We’ll now review the MIME type in IIS.

1. On SharePoint Servers, open IIS Manager
2. Click on the Server name on the left side of the console. On the right side of console make sure you have Features View tab highlighted

Note: You can make this change for selective web application (IIS site) rather than to the whole server.

3. From the features list, click on the MIME Types

SharePoint Servers - IIS Manager > MIME Types for CSV Changes
SharePoint Servers – IIS Manager > MIME Types for CSV Changes

4. Look for .CSV under the MIME Types. [Hint: You can select the first item in list and type .csv on kleyboard. It will take you right to the CSV extension]

The default .CSV MIME Type config is set to: application/octet-stream

5. Double click on the .CSV (or select .cSV and choose Edit from actions area on right).
Enter this value for CSV: application/vnd.ms-excel

Note: I’ve also noticed few people used below .CSV MIME Type value:
application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

SharePoint Servers - IIS Manager > Updated MIME Types for .CSV Extension
SharePoint Servers – IIS Manager > Updated MIME Types for .CSV Extension

The IIS MIME type change will make the CSV file open in Microsoft Excel. Read below for details. Some scenarios may require below changes as well.

c. Adding line item for CSV file and behavior in DocIcon.xml on SharePoint Servers

Now to better user experience, we would like to add some icon with CSV file and enabled ‘Edit in Microsoft Excel’ in file menu in SharePoint. Look into the icon for CSV file in the first image at the top of this post.

On SharePoint Servers, we have DocIcon.xml file at following location that holds the key to the file type and behavior.

Location:
C:Program FilesCommon FilesMicrosoft SharedWeb Server Extensions14TEMPLATEXMLDOCICON.xml

Under the ByExtension section add the line for CSV file type. Notice the CSV16.gif is the icon file that I’m using for the CSV. You can use other icons like icxltx.png
<Mapping Key=”csv” Value=”CSV16.GIF” EditText=”Microsoft Excel” OpenControl=”SharePoint.OpenDocuments”/>

The location to store your own file icon or use one already there:
C:Program FilesCommon FilesMicrosoft SharedWeb Server Extensions14TEMPLATEIMAGES

Note:
I’ve noticed people have used one of these configuration for the CSV file type in DocIcon.xml. I choose the above line entry.
<Mapping Key=”csv” Value=”CSV16.GIF” OpenControl=””/>

<Mapping Key=”csv” Value=”CSV16.GIF” OpenControl=”SharePoint.OpenDocuments”/>

<Mapping Key=”csv” Value=”CSV16.GIF” EditText=”Microsoft Excel” OpenControl=”SharePoint.OpenDocuments” OpenApp=”ms-excel”/>

Important:
After performing the MIME Type change and this DocIcon.xml addition on all SharePoint Servers, make sure to perform the IISreset/noforce on SharePoint Servers.

d. Changing Hkey_Classes_Root.csv on user’s PC

On few user systems, I noticed the checked out files will still open in notepad if clicked by the same user.

To resolve that issue, I had to delete “PerceivedType”=”text” from the following registry location. [Use at your own risk and with backups].

HKEY_CLASSES_ROOT.csv

SharePoint - Deleted PerceivedType entry from Registry for .CSV Extension on user PC
SharePoint – Deleted PerceivedType entry from Registry for .CSV Extension on user PC

That registry location had these value:

Windows Registry Editor Version 5.00

[HKEY_CLASSES_ROOT.csv]
“PerceivedType”=”text”
@=”Excel.CSV”
“Content Type”=”application/vnd.ms-excel”

[HKEY_CLASSES_ROOT.csvExcel.CSV]

[HKEY_CLASSES_ROOT.csvExcel.CSVShellNew]

[HKEY_CLASSES_ROOT.csvPersistentHandler]
@=”{guid}”

Few other notable Registry Location:
HKEY_CLASSES_ROOT.***
HKEY_CLASSES_ROOT<ProgId>shellopencommand
HKEY_CLASSES_ROOTMIMEDatabaseContent Type

HKEY_LOCAL_MACHINESOFTWAREClasses.csv

After these changes, the CSV filetypes will show the icon on the left, the SharePoint file menu will show Edit in Microsoft Excel as an option and edit the file will open the file in Microsoft Excel.

SharePoint - CSV File Types Showing Icon, Menu Showing Edit in Microsoft Excel Option
SharePoint – CSV File Types Showing Icon, Menu Showing Edit in Microsoft Excel Option

The short summary of this overall jargon can be explained in this Softvative Graphics.

Quick Process to Make CSV filetype work in MS Excel on SharePoint
Quick Process to Make CSV filetype work in MS Excel on SharePoint

@@@@@ At the end, the CSV and MS Excel lived happily ever after. 🙂  @@@@@

Few good resource to read on MIME Types and CSV

Keywords:

  • How to open CSV file in Sharepoint
  • How to open CSV file in Microsoft Excel
  • How to open CSV file in Excel
  • Open CSV in Excel
  • Open CSV from SharePoint
  • open .CSV from in Excel
  • open .CSV from SharePoint
  • CSV MIME Type changes
  • CSV MIME Type changes in SharePoint
  • CSV MIME Type changes for SharePoint
  • CSV MIME Type changes for Excel
  • CSV open document behavior
  • CSV default program association
  • Marry the CSV to Excel
  • CSV Registry locations
Visit www.softvative.com for professional services.

One Response to “Marrying CSV file type with Microsoft Excel with SharePoint as the Chaperone”

  1. create google plus account says:

    Excellent blog you have here.. It’s difficult to find good quality writing like yours these
    days. I really appreciate people like you! Take care!!

Leave a Reply

Enter the CAPTCHA * Time limit is exhausted. Please reload CAPTCHA.