Importing data to SharePoint from Powershell

SharePoint is a widely popular tool amongst developers for many reasons. Firstly, most of the core functionality, such as security, data management and more are already in place, meaning one less job for developers.

Secondly, SharePoint lists is a highly efficient system for managing records. In the most recent Microsoft update to the core design, lists can now handle a phenomenal 30 million records, therefore providing organizations with scalable record management systems for business applications. What makes this even more valuable, is that Microsoft increases this limit almost every year at Ignite.

So, building new applications utilizing SharePoint is evidently a great opportunity to enhance business processes, but what if we need to migrate historical data from a legacy system?

I came across a situation with a customer whilst on a project developing a custom application utilizing SharePoint Online. Once the application was ready to go live, my customer requested to move their historical data from the Microsoft Access database to the new system. After analyzing the current dataset from the CSV file provided by the customer, I discovered my customer was generating around 70,000 records a year and wanted to migrate three years’ worth of records.

Essentially, my objective here was to migrate the historical data (210,000 records) provided in a large CSV file to a new system designed in SharePoint Online.

If I were to achieve this objective by migrating the data manually it would be an extremely time-consuming, inefficient process. In order to make this process more automated and streamlined, the best tool to use is PowerShell.

In this blog, I will explain the steps I took to create a more simplified method for data migration and demonstrate how you can do this in your environment.

The Plan of Action in 4 Steps

  1. Review the exported CSV dataset to ensure that the SharePoint fields match the CSV data
  2. Download and install SharePoint Online Management Shell
  3. Install the PowerShell PnP library
  4. Write and run the script in PowerShell

Below is a graphic on how this process looks. Let’s get started!

Migrating data from CSV file to SharePoint graphic

Step 1: Review the exported CSV dataset to ensure that the SharePoint fields match the CSV data

The first step is very important to ensure data imports to SharePoint with no errors. We need to check the CSV file columns to make sure they match the associated SharePoint fields datatypes. Let’s open a CSV file and review the data set.

In my example, I have a CustomerData.csv file with a few records.

CSV File and Review

A few things to be aware of:

  • The first row of the CSV file must represent the column name
  • All the data in each column should have the same data type for a consistent result
  • If your SharePoint list has a choice column, you need to make sure that the data in the CSV file column matches all available options in the SharePoint choice column. In my example above, SharePoint list has a column named Status which is a choice column with only two options: Active and Inactive. If my CSV dataset has a line record with the status as Open, then this would import with an error

We can see in my CSV file example that Title, Address, City, and State are one-line text fields. Since the Detailed Notes column may have more than 255 characters, we will consider this field as a multiline text field. Contract Start Date is a date field and Status is a choice column with two options (Active and Inactive).

Now, let’s review SharePoint list to make sure the list can accommodate the data from CSV file. Navigate to your SharePoint site and open your list. In my example, I have a SharePoint list called Customer Database.

To open SharePoint list settings, click on the gear icon at the top right corner of the screen and choose List settings from the drop-down menu.

List Setting

Scroll down to the Columns section and verify the data type of your SharePoint list’s columns matches all the columns in the CSV file.

SharePoint List Columns

In my example, I have a perfect match between the CSV file and SharePoint list. If you find inconsistency in your dataset, you will have to decide either to fix your historical data (CSV file) or change SharePoint column types. Let’s move on to the next step of the process.

Step 2: Download and install SharePoint Online Management Shell

Like any other Microsoft products, SharePoint has its own management shell. It is a PowerShell module that can be used to manage SharePoint Online/SharePoint on-premises site collections and sites. You can download the SharePoint Online Management Shell here, and make sure you check the System Requirements and download the right version of the Management Shell.

Note: To use SharePoint Online Management Shell, you must have SharePoint Online Global Admin rights.

Step 3: Install PowerShell PnP library

Let’s talk very briefly what PnP library is, PnP PowerShell is a library that contains PowerShell cmdlets to manage SharePoint artifacts. You can provision site and site collections, manage lists, libraries, and more. For more details, visit Microsoft’s documentation – PnP PowerShell overview.

To install PnP PowerShell library, open SharePoint Online Management Shell and type the cmdlet below:

Install-Module SharePointPnPPowerShellOnline

Note: To successfully run the command, make sure you open the Management Shell as an administrator.

You can use PowerShell PnP library for SharePoint online, on-premises 2016 and on-premises 2013. You will have to install different modules for each version of the product.

SharePoint Online: Install-Module SharePointPnPPowerShellOnline

SharePoint 2016:  Install-Module SharePointPnPPowerShell2016

SharePoint 2013: Install-Module SharePointPnPPowerShell2013

After the module is installed, we are ready to write our script to import thousands of records from CSV file to SharePoint Online.

Step 4: Write and run the script

Before we write the script, let’s consider its mechanics:   

  • Connect to SharePoint Online environment
  • Load the content of the CSV file to a variable, which creates an array
  • Loop through each item in the variable (array) and create associated Item in the SharePoint Online list

First, we need to connect to our SharePoint Online environment. Again, make sure that you have SharePoint Global Administrator rights to run cmdlets below. To connect to the environment, we will save admin credentials into the variable called $credentials.

$credentials = Get-Credential -Message “Please Enter SharePoint Online credentials”

After running this command, you will be prompted to type your username and password.

Username and password

Remember that everything saved in the variables is good for running a PowerShell session. If you close and re-open management shell, you will have to re-create all the variables.

Next, we will create a variable called $Site with the URL of your SharePoint site:

$Site=”https://SITE-URL.sharepoint.com/”

Note: You don’t have to include SharePoint list/library name in the URL.

Now, using these two variables ($credentials and $Site), we will use PnP cmdlet to connect to the SharePoint Online environment:

Connect-PnPOnline -Url $Site -Credentials $credentials

To make sure that you are in the right site collection you can run Get-PnPlist cmdlet to locate SharePoint list that you are trying to work with.

Check list

According to the result of Get-PnPlist cmdlet, we are in the right place.

Next, we are going to load CSV file data into the variable named $CustomerData.

$CustomerData = Import-CSV “C:\Project\CustomerData.csv”

To verify that your data set loaded correctly, you can display the content of the CSV file. Type the name of the variable and hit enter.

Data set

If you have a large data set, you may not want to display the entire list. Run the cmdlet below to show the first three records in the dataset.

$CustomerData | Select-Object  -First 3

First three items

Great, we are connected to the right environment, and we have loaded the dataset for us to use. Next, we are going to loop through each item and in the $CustomerData variable and create an associated item in the SharePoint list.

Note: Pay close attention to the names of the fields.

foreach ($Record in $CustomerData){
Add-PnPListItem -List "Customer Database" -Values @{
"Title"= $Record.'Title';
"Address"= $Record.'Address';
"City"= $Record.'City';
"State"= $Record. 'State';
"Zip"= $Record.'Zip';
"Detailed_x0020_Notes"= $Record.'Detailed Notes';
“Contract_x0020_Start_x0020_Date"= $Record.'Contract Start Date';
“Status"= $Record.'Status'
}

The left side of the statement is the name of the SharePoint filed, and the right side is a CSV column name.

SharePoint Field Name

Note: For SharePoint fields make sure you use internal name NOT the display name of the field. To find the internal name of the field, navigate to your SharePoint list, click on the gear icon and select List settings.

How to migrate data from a CSV file to SharePoint using PowerShell

Scroll down to the Columns section and click on the column that you would like to know the internal name.

Column name

When you click on the column name, you will be navigated to the column’s settings page. If you look at the tail of the URL, you will find an internal name of the column that you need to use in the script.

Settings Edit Column

Now we are all set to run the script. Let’s review the entire script to have a full picture of what we just created:

$credentials = Get-Credential -Message “Please Enter SharePoint Online credentials”
$Site=”https://SITE-URL/sites/”
Connect-PnPOnline -Url $Site -Credentials $credentials
$CustomerData = Import-CSV "C:\Project\CustomerData.csv"

foreach ($Record in $CustomerData){
Add-PnPListItem -List "Customer Database" -Values @{
"Title"= $Record.'Title';
"Address"= $Record.'Address';
"City"= $Record.'City';
"State"= $Record. 'State';
"Zip"= $Record.'Zip';
"Detailed_x0020_Notes"= $Record.'Detailed Notes';
“Contract_x0020_Start_x0020_Date"= $Record.'Contract Start Date';
“Status"= $Record.'Status'
}
Run Script

After running the script, all the items have been added to SharePoint list. You can verify this by navigating to the SharePoint list.

SharePoint Result

Now, we’re all done, and all the records have been imported to the SharePoint list. You can always check the total number of items in SharePoint list by navigating to Site Contents and locating your list which shows the total number of items.

Site Contents
Item Count

In a matter of minutes, we were able to automate, what could have been a time-consuming manual process, by entering data into SharePoint Online list utilizing PnP PowerShell library cmdlets.

PnP PowerShell library is a powerful tool for SharePoint artifacts management such as site collections, sites, lists, and libraries. You can find more information here. For code examples, you can visit the PnP GitHub repository.

To conclude this how-to guide on migrating large capacities of records from a CSV file to a SharePoint application, I would like to highlight the time PowerShell can save for IT professionals by enabling them to automate time-consuming processes and therefore spend more time learning new Office 365 technologies.

About the Author

Daler Sayfiddinov

Daler is an MCSE specialising in Office 365. He builds business applications using SharePoint, PowerApps, Flow and PowerBI. Daler is passionate about automating day to day business processes to save customers time and increase revenue (automateiq.rocks). Connect with Daler on LinkedIn.

Comments

  1. JOSE Zaldicar

    Thanks for the detailed description. I am looking for how we include lookup values. Still looking for a good example that I can follow. I have over 10000 items to go through.

  2. rohit

    Will this pnp work for more than 5000 data in a excel?

  3. Jesper Enavall

    Hi,

    I have many Lookup-columns that are collected from another list and it seems not to work even if the value is exactly the same. Do I need to do something more logical in the script to solve that? For example pick up the value from the lookup-list and enter it to the current list to import to?

    1. Priya

      I am also really after importing records with lookup columns, any luck with it?

      1. JOSE Zaldicar

        Any progress on this? Going through the same issue.

  4. Nicola

    Hi Daler,

    thanks for this awesome script.

    4 lines of codes that can spare a lot of time!

    In my experience, I waste a bit of time because I was using the wrong column names.
    Although you mention this, in your article. The suggetsion to lock in the list setting is not enough in all cases.

    I was stuck, and if anybody is interested I found another nice script that provides the exact column names.

    https://www.sharepointdiary.com/2017/02/sharepoint-online-get-all-list-fields-using-powershell.html

    My columns went through different modification, and after a couple of modification one would hardly guess the exact column name.!

    Thanks

    Nicola

  5. murali

    can`t update date and time field. throws below error

    String was not recognized as a valid DateTime

    1. Simon

      I had this error. I formatted the .csv date columns to represent the US standard datetime as opposed to UK standard. Which worked for me.

      1. dhananjay

        I am also having same error for blank values in csv any idea?

  6. Emi

    Hi,

    I once tried this and it worked, but now it suddenly doesn’t. Nothing happens at all. I only got once the error while trying multiple times again:

    Add-PnPListItem : The remote server returned an error: (500) Internal Server Error

    I would really appreciate help.

    Thank you

    Emi

    1. Emi

      I solved the problem myself. For anyone who might have the same issue as me, I had to include the wanted list to the url. At first it worked without it (as it was written in the explanation), but now it only works for me when I include it.

      1. Emi

        *I meant the SharePoint library name where the list is located.

  7. Emi

    Really great explanation! Helped me a lot!

  8. Murali

    Will it work for LoopUp columns ..? SPList(Lookup Field) is Configured with parent list field. can we write that loopup values from CSV file..?

    1. Jesper Enavall

      Hi, I wonder the same thinkg. I have many Lookup-columns that are collected from another list and it seems not to work even if the value is exactly the same. Do I need to do something more logical in the script to solve that? For example pick up the value from the lookup-list and enter it to the current list to import to?

  9. Dennis

    Nice article with a great explanation!
    Helped me with what I needed 🙂

    Thanks,
    Dennis

  10. Peter G.

    Great write-up! Thanks for sharing!
    I am trying to use this approach but my script imports 39 records out of 100 and then fails with the message below. Anyone have any thoughts on this? Any help will be much appreciated!

    format-default : The collection has not been initialized. It has not been requested or the request has not been
    executed. It may need to be explicitly requested.
    + CategoryInfo : NotSpecified: (:) [format-default], CollectionNotInitializedException
    + FullyQualifiedErrorId : Microsoft.SharePoint.Client.CollectionNotInitializedException,Microsoft.PowerShell.Comma
    nds.FormatDefaultCommand

    1. Peter G

      I think I found a way around the error. I assigned the Add-PnPListItem cmdlet to a variable:

      $MyVar = Add-PnPListItem -List ….

      1. Tim

        Peter, I was running into this too. Thanks for sharing the fix.

  11. Christian Ebert

    Hello,

    thanks for this tutorial. It works, but finally my SharePoint has Empty rows but i can deleted after input. what can be the mistake here?

    1. Daler

      The only one thing I can think of is the data in Excel spreadsheet did not match available options in SharePoint drop down values.

    2. Heather Drattlo

      I know this is way late, however it may help someone else down the line. If you are importing empty rows, check your csv file and make sure there aren’t a bunch of empty rows being added by Excel. To do this, simply open your csv with a text editor like notepad. Scroll to the bottom of the document and see if there are rows of just commas with no data. If there is, just delete those rows and try again. If you want to do it programatically, pipe the following code to your import-csv line:
      | Where-Object { $_.PSObject.Properties.Value -ne ” }

      1. Darshan

        Hi,
        I have few rows where some of the columns are blank and it should be like that.
        My columns in the list are as well not of required type. But still the item does not get created due to a single blank column in my row. What can be a solution for this.
        The error comes that Item cannot be created if its value is null.

  12. Carlos I

    Not sure if this change lately. I used this PNP approach in SharePoint ONLINE but did not work with Internal Name. Instead it worked with the Display Name. (no label Name).
    You may want to update info here.

  13. shiva

    A very detailed explanation. Keep up the great work 🙂

  14. Michael H

    In step 1 you already have your csv in Excel. Why do you not simply create a table, and then go to ‘Design – Export – Export Table to SharePoint List…’?

    1. Daler Sayfiddinov

      Hi Michael, Thank you for the comment.
      Yes, you can use this method for a simple excel table.
      Also, this method only supports limited types of columns and much more difficult to manage large datasets.
      Another benefit of using PowerShell is that you can use complex logic during the import process.

      Thank you

  15. Oleg K

    Great article. Though there are a few mistakes/ In a picture it says SP field is on the left, but in the text it says vice versa. Also screenshot just below it talks about List settings but arrow points to Site settings (duplicate of a screenshot even more below).

    Btw, i have installed and used SP Online Management Shell on Windows Server 2012 R2 before as i didn’t have spare Windows 10 machine to do administration of Office 365 via PowerShell, but i was under impression that at least now everything should be possible to do via regular PowerShell by downloading relevant modules from Gallery. Or is SPO Management Shell required just for this scenario?

    1. Daler Sayfiddinov

      Hi Oleg,
      Thank you for your comment. We will correct the content.

      Yes, you are correct, you can import required modules to PowerShell and everything should work. The reason I am recommending to use the SP Management shell is to have everything available to you during script execution.

      Thank you

Leave a Reply