Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Best Practices for Migrating SQL Server to Azure - free webinar
 

Mass File Manipulation in SharePoint 2010


By:   |   Last Updated: 2011-03-16   |   Comments   |   Related Tips: > Sharepoint

Problem

SharePoint gives you a friendly, easy-to-use, web interface for uploading files, editing their properties and content, and so on. However, sometimes you need to perform "bulk" operations like moving a group of files to a different folder or library, or a mass rename or selective delete. SharePoint does provide the "manage content and structure" interface, but this can be slow and somewhat unreliable, and is not available in a SharePoint Foundation / WSS-only environment.

Solution

SharePoint provides a way to access the content of folders and document libraries in a site as if they were stored on the disk of your computer. You have probably used the simplest form of this, "explorer view" in a document library, to manipulate files using Windows Explorer. But did you know that you can also access files using the command line, and even write batch files, to perform operations that would be time consuming or even impossible in the web interface?

This tip will look at how to set this access up, including some of the common issues that can stop it working. It will also provide some examples of how you can use this capability to save time.

The starting point: Explorer view

In SharePoint 2007, if you browse to a document library, Explorer View will normally be available from the Views drop-down at the top right.

2007 document library screenshot showing Views drop-down

SharePoint will attempt to open the Explorer View within the browser window, in the same space as the normal documents view.

Explorer view in Windows XP

However, this is not possible in Windows Vista or 7 when protected mode is enabled. In that case you will see this message in the browser (note "Protected Mode: On" in the status bar):

SharePoint message to please wait while Explorer view opens

You may then see a security warning:

Windows security warning about launching Explorer

If you click Allow, you will (finally) see a Windows Explorer open showing the document library:

Windows Explorer showing 2007 document library

In SharePoint 2010, the views drop-down does not include an Explorer View option.

2010 document library screenshot showing Views drop-down

The option is still there but you have to dig further to find it:

  • Select the Library tab on the ribbon
  • Find the Open with Explorer button in the Connect & Export button group, circled in the screenshot below
2010 Open with Explorer option

Depending on your setup, you may be prompted to login again, as you are now using a different program on your computer to access SharePoint. You will then see a Windows Explorer window showing the documents and folders in the library, as if they were on a file share.

Windows Explorer window showing SharePoint documents and folders

Notice that there is an extra folder called "Forms" which was not visible in the web page view. This folder is hidden, so it should only show up if you have configured Windows Explorer to show hidden files and folders. SharePoint (both 2007 and 2010) uses this folder to store the web pages which show views of the library and other "internal" information. The best advice is: leave it alone!

Now you have a Windows Explorer window into the document library, you can use many standard functions to manipulate the files. For example, you can:

  • Highlight files in another Windows Explorer window and drag and drop to copy them into the document library (or hold down the shift key to move instead of copying the files)
  • Highlight files in this window and drag and drop to copy them out of the library
  • Click on a file name to rename it (or select it and press F2)

However, there are significant limitations. Although it looks like Windows Explorer, the documents are in SharePoint not on your hard disk or a file share. For example, SharePoint has tighter restrictions on file names and you will get an error if you attempt to copy a document into SharePoint with a name that is too long or includes a character like "&":

Warning for invalid file name

If you right click a file, you will see typical Windows Explorer options like move and copy, but no SharePoint functions like check in or publish. SharePoint 2001 included client software to let you do functions like that in Windows Explorer, but these days you have to go back to the web browser or SharePoint Designer. So if you have a set of files to import, you can drag and drop them into Explorer View, but you will need to go back to the browser to enter metadata, and check the files in if that is required by the document library.

Also, SharePoint does not support multi-user access to files like a file server does. Don't expect to be able to store an Access database in a document library and use it as a network database.

You can copy or move files between different document libraries or sites by opening two explorer windows and dragging and dropping. However, note that the files will be downloaded from the first library to your computer then uploaded to the other library, not copied directly between them, so performance may be slow. Also, SharePoint metadata will not be preserved unless it is saved in the file (as it may be with Office documents for example) and only the current version will be copied.

So, be cautious about using Explorer View when you are using SharePoint's document management functionality. If it is available, a better option in that case would be to use SharePoint's "Manage Content and Structure" interface.

Under the covers

Explorer view has been around for many years and multiple generations of SharePoint and desktop software. A feature called "Web Folder Behaviors" ("WebFolders") was originally released with Internet Explorer 5. WebFolders uses the FrontPage RPC protocol, also used by Office applications and SharePoint Designer to work with files in SharePoint.

When Windows XP was released some years later, Microsoft provided the WebClient service. This uses the WebDAV (web distributed authoring and versioning) protocol, an open and more general purpose protocol than FP RPC. However, WebFolders continued to be used in parallel. With Windows Vista, the WebClient service was enhanced. For example, it now supports connections over SSL (https). 

Microsoft's web server software (IIS) has a WebDAV server feature, but SharePoint has its own WebDAV server function built in and you should not enable IIS WebDAV support on a SharePoint server.

With SharePoint 2010, Microsoft has completed the move away from WebFolders to the more general purpose WebClient. This support is built into all modern Windows versions (XP and above) but is typically not enabled by default on servers (see below for how to do this). The advantage of the WebClient service is that it sits in the network stack on the computer and allows applications to access files as if they were on a file server. For example, even a simple application like Notepad can open a text file in a SharePoint document library through the WebClient service.

Because the WebClient service sends the WebDAV commands are sent over standard http or https connections to the SharePoint server, you can work with files on SharePoint servers through the same proxy servers and connections you use with the web browser, without requiring additional ports open on a firewall, or VPN access.

If you have transparent authentication configured, which logs you in automatically to SharePoint using network credentials, then Explorer view will also be able to log in automatically. If you log into SharePoint using a login form, Explorer view may be able to use the credentials so you don't have to login again. Otherwise, you may be prompted to login, or may even be unable to use Explorer view.

Troubleshooting Explorer view

Check the WebClient service

Explorer view requires the WebClient service to work. Typically, this is set to "Manual" startup, and will run when required rather than always starting when the computer boots up. If you get an error attempting to open Explorer view, check that this service is not disabled and can be started (right click Computer, select Manage, and find the WebClient service in Services).

Windows Services console showing WebClient service

Non-Internet Explorer browsers

Although the WebClient service is separate from the browser, and even with the improved support for 3rd party browsers in SharePoint 2010, it seems that Internet Explorer is still the only browser that will allow you to select the "Open in Windows Explorer" button on the ribbon in a document library. However, you can directly open the library through Windows Explorer without relying on the browser, using "network locations" as discussed below.

Explorer view on servers

As noted earlier, the WebClient service is not running on servers by default, on the principle that it is not usually required and therefore security best practice is to not install it. However, you can install it, and it is useful particularly in test or development environments because it gives you an easy way to "bulk copy" files into SharePoint document libraries.

For Windows Server 2003, the WebClient service is generally installed but disabled. You can use the Services console to enable it. If the 2003 version of WebClient does not do what you need (for example, if you use SSL), you can also install WebFolder support - see http://support.microsoft.com/kb/888123. In that case, you should also install the update http://support.microsoft.com/kb/907306.

For Windows Server 2008 and 2008 R2, the WebClient service is not installed at all by default. It is included in the "Desktop Experience" feature (even though it is not mentioned in that feature's description!). In server manager, in Features, select the link to Add Features and select that feature. This will install the enhanced (Vista and above) version of WebClient that supports SSL.

Direct access from the desktop

As mentioned earlier, the WebClient service is integrated with Windows, so you can link to SharePoint sites and libraries without even opening a browser. You can do this through Windows Explorer, or from a command line.

Network places / locations

You can use Network places (Windows XP) or Network locations (Windows Vista  / 7) to save shortcuts to SharePoint sites and libraries that you want to open with Windows Explorer.

In Windows XP, select My Network Places from the Start menu or the task bar in My Computer. Select Add a network place from the task bar. In Vista or 7, select Computer. Right click in the window and select Add a network location.

Windows will launch a wizard, which will display a welcome screen. It looks significantly different on different versions of Windows, but the content is almost identical:

Welcome to the Add Network Location wizard screen

Select Next. On Windows XP, it will then annoyingly waste some time trying to download information about service providers. All versions then display a list of service providers for you to choose. Despite XP's time wasting, I have only ever seen one option on this screen: something like "Choose another location" or "Choose a custom network location". Select that option and the Next button will be enabled.

Select service provider screen

Select Next. You will be asked to enter the Internet or network address. Enter the URL of a SharePoint site or a document library. You can copy the URL from a web browser, but remember to strip off the last part or parts of the URL that refer to the web page and its location. For example, if the browser URL is "http://www.mytest.com/Documents/Forms/AllItems.aspx", then the URL to the document library is "http://www.mytest.com/Documents". In general, get rid of anything with .aspx on the end, and anything immediately before it that is "/forms" or "/pages".

Add network location - specify path dialog

Press Next and Windows will attempt to connect to the library. You may be prompted for credentials. If Windows is able to connect to the library, you will be prompted for a name to give the location. The default name is probably not very descriptive so edit it as required.

Add network location - give name dialog

The final dialog of the wizard confirms success and gives you the option of opening the location.

Closing dialog of add network location wizard

You can now use this network location as a quick way of accessing files in SharePoint without going through the browser. In Windows 7 or Vista, it appears with your drives in Computer, while in Windows XP it is in My Network Places.

Command line operations

The WebClient service also allows you to work with SharePoint sites and document libraries from the command line. For example, if you open a command prompt (Start > All Programs > Accessories > Command Prompt), you can map the site or library to a drive letter with a command like this:

net use z: http://www.mytest.com/documents

Note that you may be prompted for a user name and password. (Alternatively, you can achieve the same result using the "Map network drive" option in Windows Explorer.)

You can now use the power of the command line with SharePoint. For example, I was asked a while ago for a list of the documents in a SharePoint library, including those in subfolders. The quickest way to do this is with the command line, with a command like this:

Command prompt showing dir command

"dir" is the command to list files in a directory. The "/s" is a "switch" that tells it to list files in subdirectories (or folders) as well.  As with all command line commands, you can issue it with a /? to get information about the available "switches". These let you change the sort order, the format of the information displayed, and so on.

You can copy the information from the command window to paste into a document, but that is a bit awkward. The easier alternative is to do something like this:

dir z: /s > files.txt

Adding "> files.txt" on the end tells the command line to redirect the output of the command into a file instead of just displaying it in the command line. A file called "files.txt" will be created in the current folder where you are running the command, which you can then open with Notepad.

Those of us who "grew up" with using the command line, using DOS or Unix or maybe even an earlier system like CP/M, know its power for mass manipulation of files. For example, say you wanted to move all spreadsheets into a folder called "Archive". 

Command prompt showing move command

By issuing the z: command first, we set that as the default location for all further commands. "md" is the make directory command, to make the "Archive" folder. Note the use of the '*' wildcards in the file name for the move command to match files with any names and with an extension that starts with "xl", for example .xls or .xlsx.

Using wildcards is where you can get the real power of the command line. For example, say you wanted to rename all the pdf files to .Old.pdf, to show they are old (but keep the .pdf on the end to allow them to still be opened in Adobe Reader). You can do this with a single command:

Command prompt showing rename command

Batch file scripting

Often, you will need to perform a set of operations regularly. Batch files are a way of saving a set of commands so you can re-issue them when required. The following is a brief introduction, if you are not familiar with them.

Batch files have limitations such as very limited error checking. Today, Microsoft recommends PowerShell for administrative scripting. However, batch files still have their place as a quick and easy way to automate file operations. If you want to learn more, the information about them in the Windows XP area of TechNet is still relevant for current versions of Windows.

A batch file is simply a text file containing a sequence of command line commands, saved with the extension .bat or .cmd. You can run the batch file by just opening a command line, changing to the directory where the file is located, and entering its name.

As you get more experienced, you can write batch files that can use information typed by the user so you don't have to edit the file each time you use it. For example, imagine you are regularly sent a set of files that need to be uploaded into a new folder in a SharePoint library. You could write a batch file like this in NotePad and save it as "CopyDocs.bat":

@echo off
if '%1'='' goto nofoldername
net use z: http://www.mytest.com/documents
md "z:\%1"
copy d:\docs\*.* "z:\%1"
net use z: /d
echo All done
goto done
:nofoldername
echo Please run this command with the name of a folder to create
echo For example: copydocs "folder 1"
:done 

The "echo off" command tells the batch file not to display each command in the command prompt window as it executes. Putting the '@' sign at the start tells it not to display the "echo off" command itself.

The batch file checks whether the user gave the name for a folder with the command, and displays a message if not. It then connects to the SharePoint library, makes the folder, copies the files and disconnects from the library again.

Next Steps
  • Set up network locations on your machine for all the document libraries you regularly use.
  • When you need to make changes to a set of documents, try doing it from a command line. But be careful! With the ability to perform mass changes comes the ability to make big mistakes. Test your commands in a "playground" environment before using them on production data. Learn more about command line commands in Microsoft TechNet's Command-line Reference.
  • If you regularly perform similar operations, note the commands you use and try saving them into a batch file. Learn more about batch file commands in Microsoft TechNet's Microsoft Windows XP - Batch files.
  • Check out Copying Multiple Files to SharePoint Document Libraries for a description of how to use Explorer View for copying files into SharePoint.
  • Check out SharePoint Windows Explorer Mode Not Working for a solution to an issue that can stop Explorer View working.


Last Updated: 2011-03-16


next webcast button


next tip button



About the author
MSSQLTips author Knox Cameron Knox Cameron

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools