Archive for February, 2015

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

Tuesday, February 24th, 2015

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.

Four ways to Get Report of SharePoint Folders and Files for a Library

Wednesday, February 11th, 2015

Version: 1.0

Scenario

You might have many layers of folders insides folders and files insides those folders in a SharePoint Library. How to get a quick report of folders and files for a single Document Library?

There are different ways to get that information:

a. Using Generate File Plan Report Feature of Document Library
b. Using Mapped Drive with Dir Command
c. Using SQL Query
d. Using Power Shell

a. Using Generate File Plan Report Feature of Document Library

SharePoint has a feature in Document Library called ‘Generate File Plan Report’. Follow the steps below:

1. Go to a document library for which you need to get the list of folders and files
2. Go to Library > Library Settings
3. Click on ‘Generate File Plan Report‘ under Permissions and Management

SharePoint Document Library - Generate File Plan Report
SharePoint Document Library – Generate File Plan Report

5. On next page, Save location field will default to the same library. That is the place where the system will generate and save the report file. Click Ok
6. Report will be generated and you will see a link on next page. Click on the page to open the report in MS Excel

SharePoint Document Library - Click the link to review report
SharePoint Document Library – Click the link to review report

7. Click OK and you will back to the Document Library. You can locate the file in the same library or to a place you picked in step 5

Example Report File

b. Using Mapped Drive with Dir Command

The above report doesn’t show you the folders and files that are inside those folders. It just reports the Total # of items at each folder layer.

To get the full hierarchy of folders including files inside each level of hierarchy, you need to switch back to basics. My 1992 study of  Peter Norton’s Dos Guide came to rescue from here.

1. Copy the URL http://sharepoint.svlab.com/sites/biz1/project1/ProjectsDocLib/                                [notice I removed .aspx view file and form folder that contains the view pages]
2. Map an available drive letter on your PC say W: and use the above URL
3. Open a command prompt and go to W: drive
4. Use the command
dir /s > c:FilesFolders.txt

You can add /b switch to get rid of file / folder details and just get the files / folder names.
dir /s /b > c:FileFolders.txt

5. Open the txt file and you will see the folders and files in hierarchical structure
6. Make sure to disconnect the W: drive.

Navigating using Windows Explorer with SharePoint is not the fastest way to work with SharePoint.

c. Using SQL Query

You can use the SQL query to get the list of files and folders. Run at your own risk in a pre-production system.

— Query to list Files and Folders within Sites
— By Faisal Masood
SELECT 
Webs.Title AS ‘WebApp’,
AllLists.tp_Title AS ‘ListName’,
AllDocs.DirName AS ‘URL’,
AllDocs.LeafName AS ‘FileName’,
AllDocs.ExtensionForFile AS ‘FileType’,
AllDocs.TimeCreated
FROM AllDocs 
JOIN AllLists
ON AllLists.tp_ID = AllDocs.ListId
JOIN AllDocStreams
ON AllDocStreams.Id = AllDocs.Id
JOIN Webs
ON Webs.Id = AllLists.tp_WebId
WHERE — Webs.Title LIKE ‘%SharePoint Company Sitet%’ AND
AllDocs.DirName like ‘documents%’
–AND AllDocs.TimeCreated > ‘2010-05-10 00:00:00.000’
ORDER BY Webs.Title

c. Using Power Shell

PowerShell might be the best option to get customized report with selective information. The PowerShell code below will get show all documents under a document library names that has Documents in name and that are under site sharepoint.svlab.com site.

# List Files and Folders under a Documents Library
# By: Faisal Masood – PMP, MCSE, MCITP, MCSA
#Add SharePoint PowerShell SnapIn if not already added
 if ((Get-PSSnapin “Microsoft.SharePoint.PowerShell” -ErrorAction SilentlyContinue) -eq $null) {
    Add-PSSnapin “Microsoft.SharePoint.PowerShell”
}

# http://sharepoint.svlab.com/
Get-SPWeb http://sharepoint.svlab.com/ |
  Select -ExpandProperty Lists |
    Where { $_.GetType().Name -eq “SPDocumentLibrary” -and
            -not $_.Hidden } |
    Select -ExpandProperty Items |
    Where { $_.Url -like “*Documents*” } |
    Select Name, {$_.File.Length}, url

Download .ps1 PowerShell Script File

Keywords:

  • get report of files and folders in SharePoint
  • report folders and files in SharePoint
  • Four ways to get Files Folder structure
  • SQL query to get Files Folder structure
  • PowerShell to Files Folder structure
Visit www.softvative.com for professional services.

Project Web Access 2010 Manage Timesheets and IE 11 Compatibility

Thursday, February 5th, 2015

Version: 1.0

Scenario

I came across a scenario where a user needed to recall and delete some of his Project Web Access (PWA) 2010 timesheets. Its not straightforward, keep reading :). The user was using Internet Explorer (IE) 11 on Windows 7 running under VMware VDI.

Normally a user would click on ‘Manage Timesheets‘ from PWA to delete / recall timesheets. With IE 11 the user was getting error:

Error
This Project Server 2010 feature requires at least Microsoft Internet Explorer 7.0.
Correlation ID:####
Date and Time: MM/DD/YYYY TT:TT

Project Web Access - Manage Timesheets page Error when using IE 11
Project Web Access – Manage Timesheets page Error when using IE 11

Background

Looking at the official Project Web Access 2010 Support page, I noticed that IE 11 is not supported browser for by Microsoft Project Web Access (PWA).

Reference:
Plan browser support (Project Server 2010)
https://technet.microsoft.com/en-us/library/ff631137(v=office.14).aspx

The way to go with IE 11 with Project Web Access 2010

Solution 1:

Add your PWA URL *.companyname.com in the local intranet zone of Internet Explorer.

Besides that review the Compatibility View Settings under Tools menu. The default compatibility view settings display the local intranet sites in Compatibility View.

PWA Manage Timesheets page error - Default IE 11 Compatibility View settings
PWA Manage Timesheets page error – Default IE 11 Compatibility View settings

The above method has few benefits:
a. user will not be prompted with the login to PWA site on local intranet
b. Compatibility view default settings will make the PWA URL to work in compatibility mode

Solution 2:
If adding sites to IE Local Intranet zone is locked down by IT, then review the Compatibility View settings and make sure Local Intranet sites option is checked (above screenshot). If it is not than check it or just add the companyname.com url into the field above and click Add

Solution 3:
If IE Local Intranet zone and Compatibility View settings are locked down by IT, then your next option is to use IE Developer Tool using F12 key and changing Document Mode and User Agent settings.

To recall / delete timesheet using IE11, follow these steps:

Log on to your VDI or virtual desktop environment and launch default Internet Explorer session. In this case IE 11. then follow steps listed below.

1. Go to your PWA 2010 portal http://pwa.svlab.com/pwa
2. Click on Manage Timesheets link on bottom left. The page link is http://pwa.svlab.com/pwa/_layouts/pwa/timesheet/mytssummary.aspx
3. You will get error saying, it need at least IE 7. Press F12 key on keyboard to bring IE developer tools window
4. Go to Emulation tab
5. Change the mode to following

  • Document Mode: 8 (Default)
  • User agent string: Internet Explorer 9

6. The page will automatically refresh and will bring the user timesheets
7. Select a timesheet and click Recall or delete icon for the timesheet

  • You can Delete a Timesheet with Status In Progress
  • You can Recall a Timesheet with status Approved

8. Click on the timesheet link for the period that you want to adjust. For deleted timesheet click on ‘Click to Create’ link
9. Save and submit the timesheet or proceed as needed.

Project Web Access - Manage Timesheets page in IE 11 using F12 Dev Tools and changing Mode
Project Web Access – Manage Timesheets page in IE 11 using F12 Dev Tools and changing Mode

Solution 4:
There are few other tricks but I’ll not cover those details here for the sake of time. Those are around manipulating the user agent & x-ua Compatible settings.

Solution 5:
Roll out Local Intranet Sites and Compatibility view settings via Active Directory Group Policy (GPO) settings.

Alternatively for a Virtual Desktop Infrastructure (VDI), Remote Desktop / Terminal Server or Citrix type environments, make the configuration as part of the global / admin change that all users in that virtual environment will automatically get.

Scope:
This article is not about details of Browser mode, user agent, compatibility view or other related settings.

References:

  1. Project Web App 2010 can use IE 10 in compatibility mode
  2. Erik Van Hurck – Internet Explorer 11, Windows 8.1 and Project Server 2013
  3. Cory Peters – SharePoint and Internet Explorer 11
  4. IEInternals MSDN – Internet Explorer 11’s Many User-Agent Strings

Keywords:

  • using IE 11 with PWA 2010
  • using IE 11 with Microsoft Project Web Acccess 2010
  • manage timesheets page error in pwa 2010
  • this feature requires at least Internet Explorer 7
  • using compatibility view settings with PWA 2010
  • using compatibility view settings in IE 11
Visit www.softvative.com for professional services.

SharePoint Server 2016 Announcement and My Wish List

Tuesday, February 3rd, 2015

Version: 1.1,

Updated: 2/3/2015

Microsoft has been pushing for Office 365 and SharePoint Online for last few recent years. They recently announced that next version of SharePoint Server will be released in 2016.

SharePoint Server 2016 will offer customers enhanced, flexible deployment options, improved reliability and new IT agility, enabled for massive scale. SharePoint Server 2016 is expected to provide rich hybrid capabilities with Office 365 version.

SharePoint Server 2016 – My Wish List of Feature

I wish for many new and improved features in next version of SharePoint Server in 2016. Following are some of the top wishes based on my experience with customers.

  1. More enriched workflows
  2. Document Scanning Feature
  3. Making Project Server as completely independent application that can be used without the need of full SharePoint Enterprise. (in recent years we have seen Office Web Apps as an application that requires separate server for deployment now)
  4. Making better across the farm security reporting (without the need to 3rd party tools)
  5. Better Forms solution that has better cross platforms compatibility (InfoPath replacement)
  6. Making an On-Prem version of Yammer as a service application or separate app that can be tightly integrated with SharePoint
  7. Making Video Encoding features available for more enriched video publishing within on-prem
  8. Making Delve available in On-Prem as well

I’ll be adding more items later. Read the following Office blog post with recent announcement details.

References:

Visit www.softvative.com for professional services.