Import Data from SQL Server Table to SharePoint List using PowerShell

Requirement:
We have an External CRM application from which data is to be extracted and inserted into a SharePoint List!

How to Import Data from SQL Server Table to SharePoint List using PowerShell?

PowerShell to Read Data from SQL Server and Insert into SharePoint:

We created a new list “Projects” by mapping relevant columns as per the SQL Server table, and then here is my PowerShell script to import data from SQL Server to SharePoint List.

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#Get Sharepoint List
$Web = Get-SPWeb "https://portal.crescent.com/ProjectPipeline/"
$List = $Web.Lists["Projects"]

$ConnectionString = "Server=CrescentSQL;Database=CRM_Integrated_DB;Integrated Security=True"
$SPName = "rpt_Project_pipeline"  #Stored Procedure outputs data. This can be a table, view or sp

#execute the Stored Procedure to Fetch Data
$sqlConnection = new-object System.Data.SqlClient.SqlConnection
$sqlConnection.ConnectionString = $ConnectionString
$sqlConnection.Open()
$sqlCommand = new-object System.Data.SqlClient.SqlCommand
$sqlCommand.CommandTimeout = 120
$sqlCommand.Connection = $sqlConnection
$sqlCommand.CommandType=[System.Data.CommandType]'StoredProcedure'
$sqlCommand.CommandText = $SPName
   
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $sqlCommand
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$sqlCommand.Dispose()

## Fetch data from Stored Procedure
write-host "Data Import Starts..."
ForEach ($Project in $DataSet.Tables[0]) 
{ 
 #Update Existing Project if the match found
    $SPQuery = New-Object Microsoft.SharePoint.SPQuery 
    $SPQuery.Query = "<Where><Eq><FieldRef Name='ProjectName' /><Value Type='Text'>" + $Project.'Project Name' + "</Value></Eq></Where>"
    $ListItems = $List.GetItems($SPQuery) 
    if($ListItems.Count -gt 0)
    {
        foreach($Item in $ListItems)
        { 
            #update existing Project data
            $Item["Company Type"] = $Project.'Company Type'
            $Item["Sector"] = $Project.'Sector'
            $Item["Country"] = $Project.'Country'
            $Item["Deal Lead"] = $Project.'Deal Lead'
            $Item["Investment Date"] = $Project.'Investment Date'
            $Item["Investment Amount"] = $Project.'Investment Amount'
            $Item["Industry"] = $Project.'Industry'

   try
            {
                $Item.Update() 
            }
            catch
            {
                write-host "Item ID: " $Item["ID"] "not updated"
            }
        }    
    } # Update List Item complete
  
   else #Add New Item in case no match for Project Name
   {
        if([string]::IsNullOrEmpty($Project.'Project Name')){
            #write-host "Project Name is empty and data not updated"
        }
        else{
            $newItem = $List.Items.Add()
            $newItem["Project"] = $Project.'Project Name'
            $newItem["Company Name"] = $Project.'Company Name'
            $newItem["Company Type"] = $Project.'Company Type'
            $newItem["Sector"] = $Project.'Sector'
            $newItem["Country"] = $Project.'Country'
            $newItem["Deal Lead"] = $Project.'Deal Lead'
            $newItem["Investment Date"] = $Project.'Investment Date'
            $newItem["Investment Amount"] = $Project.'Investment Amount'
            $newItem["Industry"] = $Project.'Industry'
       
            TRY
            {
                $newItem.Update()
            }
            CATCH
            {
                Write-Host "Project Name: " $Project.'Project Name' "NOT UPDATED FROM TABLE"
            }
        }
    }
}
write-host "Data Import completed!"

Salaudeen Rajack

Salaudeen Rajack - Information Technology Expert with Two decades of hands-on experience, specializing in SharePoint, PowerShell, Microsoft 365, and related products. Passionate about sharing the deep technical knowledge and experience to help others, through the real-world articles!

11 thoughts on “Import Data from SQL Server Table to SharePoint List using PowerShell

  • Hello , can you tell me where this table or view must be define? In SharePoint or in SQL?

    Reply
    • I have used my custom Stored Procedure “rpt_Project_pipeline” in this script.

      Reply
  • Hello , can you tell me where this table or view must be define?In SharePoint or in SQL? $SPName = “rpt_Project_pipeline”

    Reply
  • Hello, can you be more explicit? I don’t understand where this table or view must be (on the SharePoint or on the database)

    Reply
  • How can we replace stored procedure with view?

    Reply
  • Does this work on SharePoint Online?

    Reply
    • This script is written for On-Premises. For SharePoint Online, the SQL part stays as is. The list addition part should be re-coded.

      Reply
  • Great article, would you describe how to do an export?

    Reply
  • What is $SPName = “rpt_Project_pipeline” for?

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *