PowerShell:Bulk load files into SharePoints

Here is a script I wrote to bulk upload files and metadata into SharePoint. To make this work you need two things, a CSV file containing the names of the files to upload and the metadata associated with the item.

In this example, the CSV file has the following format:

  • filename,cust_number,document_type

The script reads the CSV file, creates a folder in the document library named with the value of the “cust_number” field, and then uploads the file “filename” and populates the “document_type” column.

The WebClient command is used to upload the file into the document library. The script also checks the item in (if required).

Write-Progress -Activity “Connecting to SharePoint Site,” -Status “Please wait …”
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

$CSVFile = “C:\FilesToImport\filelist.csv”

$SPWebURL = “http://sharepoint/site”
$SPListURL = “http://sharepoint/site/library/”
$BaseFolder = “C:\FilesToImport\Files”
$Credentials = [System.Net.CredentialCache]::DefaultCredentials

$SPWebObject = Get-SPWeb $SPWebURL
write-host $SPListURL
$SPListObject = $SPWebObject.GetListFromUrl(“library/Forms/AllItems.aspx”)
$WebClient = New-Object System.Net.WebClient
$WebClient.Credentials = $Credentials

Write-Progress -Activity “Importing CSV File,” -Status “Please wait …”

$CSVObject = Import-CSV $CSVFile
$Index = 0
$Max = $CSVObject.Count

ForEach($CSVItem in $CSVObject)
Write-Progress -Activity “Updating Metadata” -Status “Processing Item $Index of $Max”

$FileName = $CSVItem.File_name + “.pdf”
$ID_Number = $CSVItem.Cust_Number
$DocumentType = $CSVItem.Document_Type

$FullFileName = $BaseFolder + “\” + $FileName
write-host $FullFileName
if (Test-Path ($FullFileName))
$UploadPath = $SPListUrl + “/” + $Cust_Number + “/” + $FileName
$WebClient.UploadFile($UploadPath, “PUT”, $FullFileName)$SPListItemsObject = $SPListObject.Items | where {$_[‘Name’] -eq $FileName}
ForEach($SPListItem in $SPListItemsObject)
$SPListItem[‘Document_Type’] = $DocumentType

if ($SPListItem.file.CheckOutStatus -ne “None”)
Add-Content ErrorLog.txt $FullFileName

I’ve used this script in a few scenarios. I hope you find it useful too.

SharePoint Powershell List Column Queries

I recently had the need to locate all list columns in a SharePoint site of a particular data type. My first thought was someone must have done this before and after a quick hunt around in Google discovered that it wasn’t the case.

I wrote a simple script to output the names of lists and fields of a particular type using PowerShell. It is easy to modify the script to look for other field types or to change the field configuration e.g. switching from plain text to rich text.
This MSDN Article contains details of the “SPField Class” and is very handy if you are looking to create a variation of my script.

A small word of warning…always test this in a test environment before running in production. Use this script at your own risk!

Here is my base script:

$SPwebApp = Get-SPWebApplication "http://myintranetURL"
 foreach ($SPsite in $SPwebApp.Sites)
  foreach($SPweb in $SPsite.AllWebs)
   write-host "Site URL: ", $spweb.url
   foreach($list in $spweb.lists)
     write-host "List Name: ", $list.title
      foreach($field in $list.fields)
        if ($field.type -eq "Note" -and $field.title -ne "Approver Comments")
           write-host "field title: ", $field.title, " > ", $field.type -nonewline
           if ($field.richtextmode -eq "FullHTML")
              write-host "* Rich Text Enhanced ** " -nonewline
          write-host ""

This scripts walks its way through every list in a given web application and outputs a list of “sites, lists and field names where the field type = “Note” (multi-line text).

Works on both SharePoint 2010 and SharePoint 2013.

Managing Printers with PrintUI.DLL

Managing printers on client PC’s via the login script can be a useful way to ensure users have consistent printer configuration. This can be achieved by scripting calls to PRINTUI.DLL. These scripts will work on PC’s running Windows 2000, XP and Vista. The calls can be made from batch files, VB Scripts and PowerShell.

Delete a printer a network printer quietly:

rundll32.exe printui.dll,PrintUIEntry /q /dd /n\\server\printer

Add a printer a network printer quietly:

rundll32.exe printui.dll,PrintUIEntry /q /in /n\\server\printer

Set the default printer:

rundll32.exe printui.dll,PrintUIEntry /y /n “printer”

A full list of parameters for the PRINTUI.DLL can be obtained by running this command:

rundll32 printui.dll,PrintUIEntry /?

Powershell Overview

Scripting has been around for a long time in virtually every major Operating System since the 1970’s. The humble DOS batch file is probably the most well known and widely used scripting technology in Windows. VB Script improved Windows scripting and is definitely a big step up from the batch file.

The latest scripting technology for the Microsoft platform is PowerShell which can be found at the heart of Windows 2008 Server and many of the latest generation of Microsoft products. Practically every command that can be done via the GUI interface can be scripted and in many cases the GUI will actually show you the PowerShell commands to do the task.

Before you decide to replace your old login script with something new check that you environment meets the requirement of Windows XP SP2 or Windows 2003 SP1 or later. If everything is in order, downloaded PowerShell from here: www.microsoft.com/technet/scriptcenter/topics/msh/download.mspx

Once you have PowerShell installed on your client devices it’s time to start upgrading your login script. Here is a short example that maps a network drive:

# MyScript.ps1
$DriveLetter = “F:”
# Create the Com object
$net = New-Object -com WScript.Network;
# Disconnect the drive if it exists
# Map the network drive
# Open the folder with Windows Explorer
$shell = new-object -comObject Shell.Application

Powershell commands can be called using the command below from other scripts or as a Group Policy script.

  • powershell.exe  \\server\share\myscript.ps1

The script above is a simple example. The real power of PowerShell comes from more advanced automation e.g. scripting the configuration of an Exchange 2007 or MOSS 2007 environment or create backup jobs in DPM 2007. PowerShell scripts can be used to build a system and then repeat the process in a consistent way. PowerShell is also leveraged by a number of third-party developers to build management functionality into their products e.g Quest and F5 Networks.

If you are serious about developing in PowerShell a number of useful third-party tools are available to help with development. PrimalScript is a power editor with debugging capabilities. PowerGadgets is a library that does many graphical functions including the creation of Vista Sidebar Gadgets.

Powershell Resources:

Free Network Improvements

Once EBS 2008 is up and running you have a great network foundation on which to build. Here is a short list of things you can do to enhance your network for free using features of Windows 2008, Exchange 2007 or free downloads. You don’t need EBS 2008 to use these features, they are available for SBS 2008 and Windows 2008 Server editions.

Replicating your data

Distributed File System-Replication (DFS-R) allows files to be replicated between Windows servers and works well even across WAN links. Replication can be in one way, two way or a more mesh arrangement between multiple servers.

DFS-R overview

Accessing your documents remotely using Outlook Web Access

Outlook Web Access (OWA) 2007 includes a direct document access feature that can be configured from Exchange 2007. This provides a quick way to publish read only access to network shares for remote users. CIFS permissions need to be altered if the shares are not on the Exchange Server hosting OWA.

Direct File Access Tutorial

Standardising Office application settings

Download the Office Admin pack for Office 2003 or 2007 and add the .ADM files to the Group Policy Management console. Once installed Group Policies can be created to ensure Office users get consistent settings e.g. File locations, language settings etc.

Office .ADM files

Printer Management

The Printer Management Console allows management of printers on local and remote print servers from a central location. This greatly reduces the amount of effort required to manage environments with large numbers of printers on multiple Window print servers. It also eases migration of printers between print servers.

Printer Management Console

Email Disclaimers

Exchange 2007 Transport Rules can be used to add email disclaimers to out going email messages.

How to configure email disclaimers

Learn PowerShell

Powershell can be used to automate tasks that are normally done using the GUI interface. Using Powershell will ensure tasks are done consistently, saves time on repeatitive tasks and allows tasks to be done out side of hours by scheduling scripts.

Powershell Tutorial

Extend the EBS 2008 Console

A number of free add-ins can be added to the EBS 2008 Management Console. Currently add-ins are available for SQL 2008, Dynamics and Data Protection Manager.

EBS Console Add-ins