Extract SharePoint Lists with more than 5000 items into DataTable

I have been trying to use UiPathTeam.SharePoint.Activities v1.3.0 and 1.7.0 and I can’t get it to extract the items.

Anyone have some guidance on how to read a complete list into a DataTable without taking into account how many columns or rows there are?

Hi @mdiv,

Not a solution but a suggestion. See the discussion in this thread: Help understanding sharepoint activity (Get List Item) - Help / Activities - UiPath Community Forum

I am not a fan of UiPathTeam.SharePoint.Activities module since it takes too long to do the required operations. Great for development but not for production. In addition, it is not an official UiPath library. Not sure why the authors choose to name it like one.

@jeevith - when the package was developed, by convention, all unofficial packages built by the UiPath team had the UiPathTeam prefix, instead of UiPath. You’ll see this happen for a lot of other packages, not just this one. I hope this clears out the mystery.

New Package SharePoint Activity - #9 by radu_bucur - Regarding the slowness, I answered here. If you can tell me more about the exact scenario and activity you’re using I’ll try to give you more details

Hi @radu_bucur,

Thanks for the clarification. I also found out today that the InternalLabs is a repository/account of UiPath employees who have developed libraries but may or may not be officialy posted in Marketplace by UiPath.

So the Library is not in the Official feed but is posted as InternalLabs work. I will ensure to keep this in mind next time when someone has an query.

Our usecase on which we decided not to use the library was when we have need multiple entries to a SharePoint List. The PowerShell script was choosen instead of the SharePoint.Activities on the basis of ease of authentication and execution time.

This still doesn’t solve the 5000 items issues.
I have changed to PowerShell aswell, but it is not as flexible as using Activities, but it works.
I have spent multiple days on trying to get the Activities to work, and spend just a couple of hours to implement powershell, but then I do loose some of the control, and ease of reading the what happens.

@radu_bucur do you have a solution on this? I don’t mind it takes 2 seconds longer to load the list using activities, compared to PowerShell.

My use case is that I have a list of 8000+ items in 7 columns that I need to download to validate if an item exists inside. I need to this once when my robot starts.

Hey Michael,

The 5000 items issue is a SharePoint specific issue, no SP query can return more than 5000 items, it’s not specific to this activity package (you can try to create a view in a SharePoint site and you’ll see that it cant display anything if the filters return more than 5000 items, even if they are spread across multiple pages). The causes for this are related to the SQL database that SharePoint instances use to store their data.

To go around this issue, you need to retrieve the items using multiple queries, such that each query doesn’t exceed more than 5000 items. The way you decide to split the items into multiple queries depends on the usecase. One idea would be to use a query to return all items created between today and last 6 months, another query for the items from 6 months ago up to 1 year ago, etc…

But this won’t work until all the columns you need to use in the queries are indexed . See this for help with indexing: Add an index to a list or library column

Usually, if a list is already over 5000 items, you’ll need help from an admin to index the column, still, give it a try and see if you can do it yourself first.

I know this is very frustrating and it’s an issue I’ve struggled with in the past, but it’s 100% related to SharePoint itself, not the Activities Package

I know it isn’t related to your Activity package, but I don’t know what you do behind the scene.
Any how I go with the PowerShell, because every usecase is different, so it may be easier to copy the PowerShell and use it.

Thank you for your reply, just hoped you had a workaround. :smiley:

Ok Michael, thanks.

Unfortunately, the only workaround is to simply index the columns and make sure you never execute a query that returns more than 5000 items.

The 5000 limit can also be increased by an Admin, but at huge performance costs, so it’s never recommended that you go down that road

You can connect the list to an access database. It’s a two way feed. Keep the database in the project file and use database activities or export to excel.

@mdiv , I would be really helpful , If you can share me the PowerShell script. I am also facing the SharePoint Lists Limitation issue.

Thanks in Advance.
Saravana

Param
(
[Parameter(Mandatory=$true)] [string]$UserName,
[Parameter(Mandatory=$true)] [securestring]$Password,
[Parameter(Mandatory=$true)] [string]$CSVOutputPath
)
#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
   
#Variables for Processing
$SiteUrl = "https://[YOURDOMAIN].sharepoint.com/sites/[YOURSITE]"
$ListName="[LISTNAME]"
 
  
#Setup Credentials to connect
$Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($UserName,$Password)
  
#Set up the context
$Context = New-Object Microsoft.SharePoint.Client.ClientContext($SiteUrl) 
$Context.Credentials = $credentials
   
#Get the List
$List = $Context.web.Lists.GetByTitle($ListName)
$Initials = New-Object Collections.Generic.List[string] #This is my array name (looking for initials)
$Query = New-Object Microsoft.SharePoint.Client.CamlQuery
$Query.ViewXml="<View Scope='RecursiveAll'><Query></Query><RowLimit Paged='TRUE'>5000</RowLimit></View>"
     
    
#write-host "Number of List Items found starting with letter: $($char):"$ListItems.Count
#Get List Items in Batch
Do
{
    $ListItems = $List.GetItems($Query)
    $Context.Load($ListItems)
    $Context.ExecuteQuery()
    #$ListItems.count
    $ListItems | ForEach-Object {
        #Get the Title field value
        #write-host $_["Title"]
        $Initials.Add($_["Title"]) # Add the title to the initials array
    }  
    $Query.ListItemCollectionPosition = $ListItems.ListItemCollectionPosition
}
While($Query.ListItemCollectionPosition -ne $null)

write-host "Total Number of List Items found:"$Initials.Count
$obj_list = $Initials | Select-Object @{Name='Init';Expression={$_}}
$obj_list | export-csv -NoTypeInformation -Path $CSVOutputPath
1 Like