Import Data from SQL Server Table to SharePoint List using PowerShell
Requirement:
We've an External CRM application from which data is to be extracted and inserted into a SharePoint List!
PowerShell to Read Data from SQL Server and Insert into SharePoint:
We created a new list "Projects" 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.
We've an External CRM application from which data is to be extracted and inserted into a SharePoint List!
PowerShell to Read Data from SQL Server and Insert into SharePoint:
We created a new list "Projects" 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 "http://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 incase 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!"
What is $SPName = "rpt_Project_pipeline" for?
ReplyDeleteThat's a Stored Procedure outputs data. This can be a table, view or sp!
DeleteGreat article, would you describe how to do an export?
ReplyDeleteSure, Here you go: How to Export SharePoint List data to SQL Server table using PowerShell
DeleteDoes this work on SharePoint online?
ReplyDeleteThis script is written for On-Premises. For SharePoint Online, the SQL part stays as is. The list addition part should be re-coded.
DeleteHow can we replace stored procedure with view?
ReplyDelete