Automating Data Entry into SharePoint with PnP Powershell

In the past, we’ve written  about the use of PnP PowerShell automation tools and their various uses, such as automatic SharePoint site creation. In this post, we will cover a common process that clients require from us, that being the migration of data from Excel sheets into SharePoint.

This can be for various reasons, whether it be for storing formerly physical library records, or to more securely store a customer ledger in Office365 – whatever the purpose, this process will take a fraction of the time when it’s handled by a PowerShell script.

For this to work, you’ll need a source spreadsheet like the one below, with a top line containing headers for each column – ideally, these should match the field names in your SharePoint list as closely as possible.

Now that we have a source for our data, we have to build a script to move it to SharePoint. Before doing this, it’s important to ensure that you have the required tools installed on your system, which can be found by following this link:

PnP Powershell Required Tools

This will allow you to use all PnP PowerShell cmdlets that will be mentioned in this post.

The script itself is simple enough, and must run through the following steps:

1.       Identify the local path to the source document

2.       Sign into the SharePoint site

3.       Identify the list

4.       Loop through the source items and create a list item for each one

In order to identify the path to the file, we use the following command:

$inFile Import-Csv ‘C:\csvscript\customers.csv’

I recommend you place your source file in a subfolder directly on your drive directory, in order to avoid issues finding the correct file path. Place the reference to the csv in a variable, so that it can be referenced later in the loop.

After this is set up properly, connect to your site using the Connect-PnPOnline command. Ensure that this is the site that contains your list.

$cred = Get-Credential -Message “Enter SPO Credentials”
$SPAddress =”[YOUR-TENANT].sharepoint.com/sites/[YOUR-SITE]”
Connect-PnPOnline -Url $SPAddress -Credentials $cred

This will allow the script to interact with your site and the list you wish to migrate the CSV data to. Now that this is established in the script, we can move on to the main focus of the script, which is the loop that runs through every line of the spreadsheet. The loop starts as follows:

foreach ($row in $infile)

This establishes a condition for the loop – it will run once for every non-header row in the document, which is referenced using the variable we created earlier.

Within the loop, we run a command called “Add-PnPListItem” against every row to create a list item based on the row’s cells. The parameters of this command are the list name (ensure that it’s typed exactly as it is on SharePoint), and the values you wish to port over. Examples of both can be seen below:

Add-PnPListItem -List ‘Customers’ -Values @{

              “Customer_x0020_Address”                     = $row.’Customer Address’;

}

Note how the values are set out. The SharePoint column value is referenced first, as the information contained in the spreadsheet cell is being contained within the field like a variable. Ensure that when you use any punctuation or spaces in your field name that you’re accounting for these in the script, as you need to use the internal name of the field, and punctuation will affect it, as seen in the Customer Address field above.

After referencing the field in SharePoint, you have to point to the row that will populate this part of the list item. This is done by adding an equals sign like a variable assignment, and adding $row.’[COLUMN NAME]’. Contrasting with the SharePoint field, it’s important to type this exactly as it appears in the spreadsheet.

With the values added and the proper excel column tiles assigned, you should be able to use these commands to migrate large amounts of list items or, with a few changes, documents to SharePoint in a fraction of the time it may take otherwise.