kwizcom banner advertisement

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!
How to Import Data from SQL Server Table to SharePoint List using PowerShell?

PowerShell to Read Data from SQL Server and Insert into SharePoint:
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"

#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!"
Import Data from SQL Server Table to SharePoint List using PowerShell Import Data from SQL Server Table to SharePoint List using PowerShell Reviewed by Salaudeen Rajack on 8:53 PM Rating: 5

No comments:

Please Login and comment to get your questions answered!

Powered by Blogger.