1

I am struggling to find a solution to my case where I have like 300k items in a SharePoint Online list, by accident. I have generated a file using PowerShell that contains all duplicated items. I was helped through this article: https://www.sharepointdiary.com/2016/12/sharepoint-online-get-all-items-from-large-lists-powershell-csom.html The file is built-up as follows:

310384,205600
311260,205600
312004,205600
312859,205600
313672,205600
314464,205600
315256,205600
316066,205600
1516,205601
9937,205602
10615,205602
11300,205602
11956,205602
12671,205602
13345,205602`

So two columns, left is ID, right is ProjectReferenceNumber.

What I would like to achieve, is that the ProjectReferenceNumber is deduplicated. And I would probably need to combine these two articles (because the second one does not work for large lists) but I really have no clue how to make this work.

https://www.sharepointdiary.com/2016/12/sharepoint-online-get-all-items-from-large-lists-powershell-csom.html https://www.sharepointdiary.com/2018/06/sharepoint-online-find-and-delete-duplicate-list-items-using-powershell.html

Could someone please help me out with this one?

Thanks a lot!

Improve this question
2
  • Are you wanting to remove all the duplicate values or update them to something else?
    – Taco_Buffet
    Commented Aug 18, 2020 at 22:34
  • Hi Taco thanks for your swift reply. I'm sorry this was not clear. I would like to remove all duplicate ProjectReferenceNumber values, so that this will contain unique values. Hope this is clear.
    – joostvdlinden
    Commented Aug 19, 2020 at 6:35

2 Answers 2

Reset to default
1

I can provide more PowerShell help if needed.

  1. Export the list data to Excel, be sure to include the SharePoint ID field.
  2. Open Excel and use the de-dupe function to identify duplicates, remove the non-dupe items from the dataset, then save the file. You should now have an Excel file with nothing but duplicate values.
  3. Use PowerShell to read from the Excel file and delete items from SharePoint.

Idea:
I'm not sure what the import limitations are for SP and Excel, but you could import the Excel file into a new SP list, then use a Flow to remove the duplicates from your source list. If there are limits on the import size, break the Excel file into smaller chunks, then use Flow to process the data.

Improve this answer
1
  • Hi Taco I started working on an approach by building a 1-column Excel (xlsx) file, only containing the list item ID's. I started off using a Power Automate flow and by importing the file. I quickly ran into the Power Automate Excel list rows limit and for each limit. So I switched over to Logic Apps. I have a 100.000 limit there. Eventually that now seems to work. Trigger = Recurrence (i.e. once a Month) Action 2 = List rows present in a table (Excel Online for Business) For each: ['value'] Action inside for each = Delete item (SharePoint) where id = int(items('For_each')?['Column1'])
    – joostvdlinden
    Commented Aug 20, 2020 at 20:03
1
  1. Trigger = Recurrence (i.e. once a Month)
  2. Action 2 = List rows present in a table (Excel Online for Business)
  3. For each: ['value']
  4. Action inside for each = Delete item (SharePoint) where id = int(items('For_each')?['Column1'])

enter image description here

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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