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 |
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 |
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 |
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 |
7. The opens up in Notepad!!!
 |
| 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 |
3. Click on Excel from the list of program on left side. Then click on ‘Choose defaults for this program’
 |
|
|
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
@@@@@ At the end, the CSV and MS Excel lived happily ever after. 🙂 @@@@@
Few good resource to read on MIME Types and CSV
- MIME Type Detection in Windows Internet Explorer
- Edit a MIME Type
- Default Programs
- Opening .CSV files for ‘Direct editing’ via Excel
- How can I define mime types on my Windows/IIS based site?
- SharePoint – Client API Reference
- IETF – Common Format and MIME Type for Comma-Separated Values (CSV) Files
- MIME Types List
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
One Response
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!!