Load CSV File Value Into SharePoint List Using Powershell Script

Introduction

In this blog, you will learn how to load CSV file values into SharePoint list, using PowerShell Script.

Prerequisite

Create a SharePoint custom list with the following site columns:

• SalesMaster List with Title Column
• ServiceMaster List with Title Column
• RegionMaster List with Title Column
• TestMaster List
  
  Title - Single line of text
  
  Subject - Single line of text
  
  Sales - Lookup value to SalesMaster List
  
  Service - Lookup value to ServiceMaster List
  
  Region - Lookup value to RegionMaster List
  
  
  

Steps Involved

There are several different ways to import the data from a Spreadsheet to a SharePoint list. Depending on your requirements, you can copy the data into SharePoint lists. Add the list items involved in manual work in SharePoint. For huge number of items, it's very complex work. We can put the same content CSV file and write the code to add to SharePoint lists programmatically. The following code snippet allows you to add CSV files to a SharePoint list.

1. Add the reference to Microsoft.SharePoint.PowerShell, if it is not not already added.
   

   #Setup the correct modules
   for SharePoint Manipulation
   if ((Get - PSSnapin - Name Microsoft.SharePoint.PowerShell - ErrorAction SilentlyContinue) - eq $null) {
       Add - PsSnapin Microsoft.SharePoint.PowerShell
   }
   $host.Runspace.ThreadOptions = "ReuseThread"

2. Open the Web, using Get-SPWeb object.
   

   #Open SharePoint List
   $SPServer="http://siteURL.com/"
   $spWeb = Get-SPWeb $SPServer

3. Get the list, using GetList method.
   

   $SPAppList="/Lists/TestMaster/"
   $spData = $spWeb.GetList($SPAppList)

4. Give the local location of CSV file.
   
   $InvFile="D:\Users\ImportCSVDataToLists\TestMaster.csv"
   
   
5. Test the path of CSV file, using Test-Path. If it exists, load the file, using Import-CSV otherwise exit the PowerShell script.
   

   #Get Data from Inventory CSV File
   $FileExists = (Test - Path $InvFile - PathType Leaf)
   if ($FileExists) {
       "Loading $InvFile for processing..."
       $tblData = Import - CSV $InvFile
   } else {
       "$InvFile not found - stopping import!"
       exit
   }

6. Loop through all the items in CSV file, using the for each loop.
   
   Add the item to the list, using AddItem method.
   
   Add the look up column by getting the look up list item ID and the value. Add all the look up columns in the same way.
   Update the Item to complete the AddItem function.
   
   # Loop through Applications add each one to SharePoint
   
   "Uploading the data to SharePoint...."
   

   foreach($row in $tblData)
   {
       "Adding entry for " + $row.
       "Title".ToString()
       $spItem = $spData.AddItem()
       $spItem["Title"] = $row.
       "Title".ToString()
       $spItem["Subject"] = $row.
       "Claim".ToString()
       $looValue1 = $row.
       "Sales".ToString()
       $LookupList1 = $spWeb.Lists["SalesMaster"];
       $LookupItem1 = $LookupList1.Items | Where - Object {
           $_.Title - eq $looValue1
       }
       $Lookup1 = ($LookupItem1.ID).ToString() + ";#" + ($LookupItem1.Title).ToString()
       $spItem["Sales"] = $Lookup1
       $looValue2 = $row.
       "Service".ToString()
       $LookupList2 = $spWeb.Lists["ServiceMaster"];
       $LookupItem2 = $LookupList2.Items | Where - Object {
           $_.Title - eq $looValue2
       }
       $Lookup2 = ($LookupItem2.ID).ToString() + ";#" + ($LookupItem2.Title).ToString()
       $spItem["Service"] = $Lookup2
       $looValue3 = $row.
       "Region".ToString()
       $LookupList3 = $spWeb.Lists["RegionMaster"];
       $LookupItem3 = $LookupList3.Items | Where - Object {
           $_.Title - eq $looValue3
       }
       $Lookup3 = ($LookupItem3.ID).ToString() + ";#" + ($LookupItem3.Title).ToString()
       $spItem["Region"] = $Lookup3
       $spItem.Update()
   }
   "---------------"
   "Upload Complete"

7. Finally, dispose spWeb object.
   
   $spWeb.Dispose()

Summary

Thus, you have learned, how to load CSV file value into SharePoint list, using PowerShell Script.