0

I came across this script and it works great. But I need it to check to see if the list item exists first and do an update if it does. If not, then add the list item as new. Right now, it only keeps adding them.

Can someone help me modify the script so that it checks for the list item by SiteID first and updates it if found? Otherwise adds it as new. I found this link where it could be done but need help doing the same with my script. update SPList using csv via Powershell

if((Get-PSSnapin "Microsoft.SharePoint.PowerShell") -eq $null)
{
      Add-PSSnapin Microsoft.SharePoint.PowerShell
}

function Get-UsefulWebsitesFromSql                        
{                          
    #Connect to the database                        
    $conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=mySQLServer;Initial Catalog=myDB;User ID=username;Password=******")                        
    Write-Progress -Id 1 -ParentId 0 -Activity "Importing Data From SQL into SharePoint" -PercentComplete (1) -Status "Opening Connection to the SQL Server";                        
    $conn.Open();                        
    try                        
    {                                  
        #Execute the query                        
        Write-Progress -Id 1 -ParentId 0 -Activity "Importing Data From SQL into SharePoint" -PercentComplete (2) -Status "Querying SQL Server";                           
        $query = "select * from dbo.dimSite where ContractID = 'I-000124'"                        
        $dap = new-object System.Data.SqlClient.SqlDataAdapter($query,$conn);                        
        $dt = new-object System.Data.DataTable;                        
        $dap.Fill($dt);                                    
        $w = Get-SPWeb https://mysite/dev1;                        
        $list = $w.Lists["dimSite List"];                           
        $listTitle = $list.Title;                        
        $pi = $dt.Rows.Count;                              
        $pci = 1;                                       
        $itemsAdded = 0;                               

        Write-Progress -Id 1 -ParentId 0 -Activity "Importing Data From SQL into SharePoint" -PercentComplete (25/($pi+45)*100) -Status "Importing ($pi) items into SharePoint.";                              

        foreach($r in $dt.Rows)                        
        {                                    
            Write-Progress -Id 1 -ParentId 0 -Activity "Importing Data From SQL into SharePoint" -PercentComplete (($pci+25)/($pi+45)*100) -Status "Importing ($pi) items into SharePoint.";                        
            Write-Progress -Id 2 -ParentId 1 -Activity "Adding new items to $listTitle" -PercentComplete ($pci/$pi*100) -Status "Importing item $pci into SharePoint.";                        
            $pci++;                        
            $i = $list.Items.Add();                        
            try                        
            {                        
                $itemsAdded++;                        
                #Set the Title                        
                $i["Title"] = $r["SiteNm"]; 
                #Set the SiteID
                $i["SiteID"] = $r["SiteID"]; 
                #Set the ContractID
                $i["ContractID"] = $r["ContractID"]; 
                #Save changes to the item                        
                $i.Update();                        
                Write-Host ([String]::Format("Added item: '{0}',",$r["SiteNm"])) -ForegroundColor Green;                        
            }                        
            catch [System.Exception]{                        
                Write-Host ([String]::Format(" Error adding item. Item {0} has been skipped. Error: {1}. ",$r["SiteNm"],$_)) -ForegroundColor Red -BackgroundColor White;                        
                continue;                          
            }                          
        }                              
        Write-Progress -Id 1 -ParentId 0 -Activity "Importing Data From SQL into SharePoint" -PercentComplete (80) -Status "Closing SQL Connection.";                              
        Write-Host ([String]::Format("Finished importing items into the list. Imported {0} items. ",$itemsAdded)) -ForegroundColor Blue -BackgroundColor White;                        
        Write-Progress -Id 1 -ParentId 0 -Activity "Importing Data From SQL into SharePoint" -PercentComplete (90) -Status "Finished importing ($pi) items into SharePoint.";                        
        $w.Dispose();                        
    }                        
    catch [System.Exception]{                        
        Write-Host ([String]::Format("Error: {0} ",$_)) -ForegroundColor Red -BackgroundColor White;                           
        Write-Progress -Id 1 -ParentId 0 -Activity "Importing Data From SQL into SharePoint" -PercentComplete (100) -Status "An error occurred.";                               
    }                        
    finally{                        
        $conn.Close();                         
    }                                  
}                        
#Call the function                        
Get-UsefulWebsitesFromSql;

Thank you for the help!

1

try below code,

if((Get-PSSnapin "Microsoft.SharePoint.PowerShell") -eq $null)
{
      Add-PSSnapin Microsoft.SharePoint.PowerShell
}

function Get-UsefulWebsitesFromSql                        
{                          
    #Connect to the database                        
    $conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=mySQLServer;Initial Catalog=myDB;User ID=username;Password=******")                        
    Write-Progress -Id 1 -ParentId 0 -Activity "Importing Data From SQL into SharePoint" -PercentComplete (1) -Status "Opening Connection to the SQL Server";                        
    $conn.Open();                        
    try                        
    {                                  
        #Execute the query                        
        Write-Progress -Id 1 -ParentId 0 -Activity "Importing Data From SQL into SharePoint" -PercentComplete (2) -Status "Querying SQL Server";                           
        $query = "select * from dbo.dimSite where ContractID = 'I-000124'"                        
        $dap = new-object System.Data.SqlClient.SqlDataAdapter($query,$conn);                        
        $dt = new-object System.Data.DataTable;                        
        $dap.Fill($dt);                                    
        $w = Get-SPWeb https://mysite/dev1;                        
        $list = $w.Lists["dimSite List"];                           
        $listTitle = $list.Title;                        
        $pi = $dt.Rows.Count;                              
        $pci = 1;                                       
        $itemsAdded = 0;                               

        Write-Progress -Id 1 -ParentId 0 -Activity "Importing Data From SQL into SharePoint" -PercentComplete (25/($pi+45)*100) -Status "Importing ($pi) items into SharePoint.";                              

        foreach($r in $dt.Rows)                        
        {                                    
            Write-Progress -Id 1 -ParentId 0 -Activity "Importing Data From SQL into SharePoint" -PercentComplete (($pci+25)/($pi+45)*100) -Status "Importing ($pi) items into SharePoint.";                        
            Write-Progress -Id 2 -ParentId 1 -Activity "Adding new items to $listTitle" -PercentComplete ($pci/$pi*100) -Status "Importing item $pci into SharePoint.";                        
            $pci++;                        
            #Check for existing item
            $SPItem = $list.Items | Where { $_["SiteID"] -eq $r["SiteID"]; } 
            If ($SPItem -ne $null) { 
                #Set the Title                        
                $SPItem["Title"] = $r["SiteNm"]; 
                #Set the SiteID
                $SPItem["SiteID"] = $r["SiteID"]; 
                #Set the ContractID
                $SPItem["ContractID"] = $r["ContractID"]; 
                #Save changes to the item                        
                $SPItem.Update();           
                Write-Host ([String]::Format("Updated item: '{0}',",$r["SiteNm"])) -ForegroundColor Green;                        
            }
            else{
                $i = $list.Items.Add();                        
                try                        
                {                        
                    $itemsAdded++;                        
                    #Set the Title                        
                    $i["Title"] = $r["SiteNm"]; 
                    #Set the SiteID
                    $i["SiteID"] = $r["SiteID"]; 
                    #Set the ContractID
                    $i["ContractID"] = $r["ContractID"]; 
                    #Save changes to the item                        
                    $i.Update();                        
                    Write-Host ([String]::Format("Added item: '{0}',",$r["SiteNm"])) -ForegroundColor Green;                        
                }                        
                catch [System.Exception]{                        
                    Write-Host ([String]::Format(" Error adding item. Item {0} has been skipped. Error: {1}. ",$r["SiteNm"],$_)) -ForegroundColor Red -BackgroundColor White;                        
                    continue;                          
                }  
            }                        
        }                              
        Write-Progress -Id 1 -ParentId 0 -Activity "Importing Data From SQL into SharePoint" -PercentComplete (80) -Status "Closing SQL Connection.";                              
        Write-Host ([String]::Format("Finished importing items into the list. Imported {0} items. ",$itemsAdded)) -ForegroundColor Blue -BackgroundColor White;                        
        Write-Progress -Id 1 -ParentId 0 -Activity "Importing Data From SQL into SharePoint" -PercentComplete (90) -Status "Finished importing ($pi) items into SharePoint.";                        
        $w.Dispose();                        
    }                        
    catch [System.Exception]{                        
        Write-Host ([String]::Format("Error: {0} ",$_)) -ForegroundColor Red -BackgroundColor White;                           
        Write-Progress -Id 1 -ParentId 0 -Activity "Importing Data From SQL into SharePoint" -PercentComplete (100) -Status "An error occurred.";                               
    }                        
    finally{                        
        $conn.Close();                         
    }                                  
}                        
#Call the function                        
Get-UsefulWebsitesFromSql;

If this answer has helped you, kindly mark it as answer, so that it will help some one in future.

  • Thank you Karthik. Just what I needed. – gsr51 Nov 21 at 18:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.