SharePoint Metadata Uploader

SharePoint-Metadata-Uploader.md

SharePoint Metadata Uploader

A PowerShell script that uploads document metadata in a CSV file to a SharePoint document library.

This script works for accounts that have 2FA enabled and requires fewer SharePoint permissions than other similar scripts I've come across.

Quick Start Guide

Step 1: Confirm Windows PowerShell version

First, you will need to ensure that you are using the old version of Windows PowerShell (5.1). You can check what version of PowerShell you're using with the command $PSVersionTable.PSVersion.

Version 5.1 comes preinstalled with some versions of Windows. If you don't have it, you can install it from here.

Step 2: Install dependencies

Microsoft.Online.SharePoint.PowerShell Module

To check if it's already installed:
Get-Module -Name Microsoft.Online.SharePoint.PowerShell -ListAvailable | Select-Object Name,Version

To install: Install-Module Microsoft.Online.SharePoint.PowerShell

For more detailed information, check the docs here.

SharePointPnPPowerShellOnline Module

To install: Install-Module SharePointPnPPowerShellOnline
NOTE: This will install the legacy version of the module. This is necessary for the script to work.

Step 3: Edit config values

You will need to change a few lines in the script.

SharePoint CSOM Assemblies

Change the below lines to match the directory where the following files were installed. It should be somewhere in the WindowsPowerShell\Modules\Microsoft.Online.SharePoint.PowerShell folder, wherever your Windows PowerShell was installed. You might have to search to find them.

# Load SharePoint CSOM Assemblies
Add-Type -Path "C:\your-path-here\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\your-path-here\Microsoft.SharePoint.Client.Runtime.dll"

PnP Core

Similar to the previous step, change the following line to match the directory where the OfficeDevPnP.Core.dll file was installed. It should be somewhere in the WindowsPowerShell\Modules\SharePointPnPPowerShellOnline folder, wherever your Windows PowerShell was installed.

# Load PnP Core
Add-Type -Path "C:\your-path-here\SharePointPnPPowerShellOnline\3.29.2101.0\OfficeDevPnP.Core.dll"

Parameters

Edit the following lines to match your files:

# Parameters
$SiteURL = "https://yoursite.sharepoint.com/sites/yoursite"
$LibraryName = "Library Name"
$LibraryRelativeURL = "/sites/yoursite/Library Name/"
$CSVFile = "C:\your-path-here\metadata_file.csv"

File Name Field

Make sure that the file name field matches the field name in your CSV file. The default is "File Name". This can be changed if necessary.

$SourceFilename = $Row."File Name"

Metadata Fields

Finally, you should update the metadata fields to match the ones in your CSV file and document library. The default ones ("DocTypeName", "DocDate", "FiscalYear"...) are the ones needed for my project; they can be changed to whatever you need them to be. The name in brackets ($ListItem["FiscalYear"]) is the name of the field in your SharePoint document library. The name not in brackets ($Row."Fiscal Year") is the corresponding field name in your CSV file. They can be different.

# Update metadata fields
$ListItem = $File.ListItemAllFields
$ListItem["DocTypeName"] = $Row."DocTypeName"
$ListItem["DocDate"] = $Row."DocDate"
$ListItem["FiscalYear"] = $Row."Fiscal Year"
...

Step 4: Upload all files to SharePoint

This script does not upload files; it only changes the metadata. I could not get uploading files to work without elevated permissions in SharePoint, which did not work for my case.

First, you will need to create a SharePoint document library with all of the correct metadata fields.

Next, you will need to upload all of the files, either manually or by using another script. I was able to drag-and-drop 1,000 files at a time from my computer to SharePoint, so it didn't take too long. If you would rather use a script, you can check the Resources section below for some help with that (assuming permissions are not an issue).

Step 5: Run the script!

1. Run Windows PowerShell. Make sure that you are using the old version (5.1).
2. In the Windows PowerShell window, use cd to move to the folder containing the script.
3. Run the script using the command .\SharePoint-Metadata-Uploader.ps1.

Troubleshooting

I found that I had to put all of the SharePoint fields into the script as single words (no spaces). The actual fields on SharePoint can have spaces, but those spaces must be removed in the script (e.g. my "Fiscal Year" field in SharePoint was written in the script as "FiscalYear"). I'm not sure why, but if you're having issues, that might be something to try.

Other Resources

Here are some links that I found helpful while putting this together:

• SharePoint Diary - SharePoint Online: How to Update Document Properties using PowerShell?
• SharePoint Diary - SharePoint Online: Import Files from a CSV using PowerShell
• SharePoint Diary - Upload Files to SharePoint Library Remotely using Client-Side Object Model (CSOM) and PowerShell
• Stack Exchange - update document properties in library using csom

SharePoint-Metadata-Uploader.ps1

# SharePoint Metadata Uploader
# Written by Sophie Garrett (with help from AI and StackOverflow)
# A PowerShell script that uploads document metadata in a CSV file to a SharePoint document library.
# See the readme file for more info.

# This script uses the SharePoint Client-Side Object Model (CSOM) and the AuthenticationManager module from PnP PowerShell.
# Note: It will only work with the old version of Windows PowerShell (5.1).
# The library must have already been created with all fields, and all files must have already been uploaded. The script does not upload files.
# Edit the fields below to customize.

# Load SharePoint CSOM Assemblies
Add-Type -Path "C:\your-path-here\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\your-path-here\Microsoft.SharePoint.Client.Runtime.dll"

# Load PnP Core
Add-Type -Path "C:\your-path-here\SharePointPnPPowerShellOnline\3.29.2101.0\OfficeDevPnP.Core.dll"

# Parameters
$SiteURL = "https://yoursite.sharepoint.com/sites/yoursite"
$LibraryName = "Library Name"
$LibraryRelativeURL = "/sites/yoursite/Library Name/"
$CSVFile = "C:\your-path-here\metadata_file.csv"

# Authenticate and set up context
$AuthenticationManager = new-object OfficeDevPnP.Core.AuthenticationManager
$Context = $AuthenticationManager.GetWebLoginClientContext($SiteUrl)

# Get document library
$Library = $Context.Web.Lists.GetByTitle($LibraryName)

# Get data from CSV file
$CSVData = Import-CSV $CSVFile

# Loop through CSV
ForEach($Row in $CSVData) {
    Try {
        # Get path to file
        $SourceFilename = $Row."File Name"
        $FileRelativeURL = $LibraryRelativeURL + $SourceFilename

        # Load file
        $File = $Context.Web.GetFileByServerRelativeUrl($FileRelativeURL)
        $Context.Load($File)
        $Context.ExecuteQuery()

        # Update metadata fields
        $ListItem = $File.ListItemAllFields
        $ListItem["DocTypeName"] = $Row."DocTypeName"
        $ListItem["DocDate"] = $Row."DocDate"
        $ListItem["FiscalYear"] = $Row."Fiscal Year"
        $ListItem["ProviderName"] = $Row."Provider Name"
        $ListItem["ProgramName"] = $Row."Program Name"
        $ListItem["Department"] = $Row."Department"
        $ListItem["Description"] = $Row."Description"
        $ListItem["DocumentSection"] = $Row."Document Section"
        $ListItem["DocHandleLink"] = $Row."Doc Handle Link"
        $ListItem["DocumentHandle"] = $Row."Document Handle"
   
        # Save file
        $ListItem.Update()
        $Context.ExecuteQuery()
    }
    Catch {
        write-host -f Red "Error: " $_.Exception.Message
    }
}