none
Read excel data delete share point list item RRS feed

  • Question

  • Hi,

    I have the requirement for "Read excel data(excel contains user names) and delete corresponding user item in share point list(Sp list contains user column) using csom".

    Example;- the excel sheet contains the user 'AAA', the share point list contains user column with 'AAA' the record/item needs to be delete.

    anyone help me on this.

    Thanks in advance.

    Regards,

    siddu


    • Edited by SidduSagar Saturday, December 7, 2019 5:22 AM
    Saturday, December 7, 2019 5:17 AM

All replies

  • Hi Siddu,

    We can use PowerShell with CSOM script to achieve it. The following example for your reference.

    The Users.csv file.

    The PowerShell script.

    Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll"  
    Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"  
      
    $siteURL = "http://sp2013/sites/team"  
    $username="administrator"
    $password="xx"
    $domain="contoso"
    $listName="CL1209"
    $userField="User"
    $csvFilePath="C:\temp\Users.csv"
    
    $creds = New-Object System.Net.NetworkCredential($username, $password, $domain);
    $ctx = New-Object Microsoft.SharePoint.Client.ClientContext($siteURL)  
    $ctx.credentials = $creds
    
    #import data from csv
    $contents = Import-CSV $csvFilePath
    try{
        $lists = $ctx.web.Lists  
        $list = $lists.GetByTitle($listName)  
        $listItems = $list.GetItems([Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery())  
        $ctx.load($listItems)      
        $ctx.executeQuery()
    	For($i = $listItems.Count-1; $i -ge 0; $i--)
    	{
    		if($listItems[$i][$userField])
    		{
    			$user=$listItems[$i][$userField].LookupValue
    			
    			foreach ($row in $contents)
    			{
    				if($row.UserName -eq $user)
    				{
    					$row.UserName
    					$listItems[$i].DeleteObject()
    				}
    			}
    		}
    	}
    	$ctx.ExecuteQuery()
    }  
    catch{  
        write-host "$($_.Exception.Message)" -foregroundcolor red  
    } 
    

    Best Regards,

    Dennis


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    SharePoint Server 2019 has been released, you can click here to download it.
    Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.

    Monday, December 9, 2019 7:05 AM
    Moderator
  • Hi Siddu,

    Would you please provide us with an update on the status of your issue?

    Best Regards,
    Dennis

    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    SharePoint Server 2019 has been released, you can click here to download it.
    Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.

    Monday, December 16, 2019 7:08 AM
    Moderator