During one of my assignments, I have come across a situation where we need to fix the data issues in SharePoint Lists. One of the issues that we found was the presence of duplicate data. In order to fix that problem, I developed a Powershell Script to find out the duplicate data based on a specific or a group of columns.
For the sake of demo, I have added a SharePoint List with some duplicate records in it, as shown below.
Now, let’s look into the code to understand implementation details-
In Step 1, we are getting references to the Site and Web where the SharePoint List resides
In Step 2, we are splitting the list of columns based on which we want to find out the duplicate data.
We can see there are two input variables “ColumnToValidate” and “ColumnToDisplay”. “ColumnToValidate” provides columns based on which duplicity needs to be checked while “ColumnToDisplay” contains the list of columns that need to be the part of the data export.
In Step 3, we are creating the export folder that will hold the CSV files exported with duplicate records.
In Step 4, we are creating the list object that will give the handle on the list which needs to be validated.
In Step 5, we are getting the list of Items from SharePoint List and grouping them based on the validation columns.
In Step 6, we are creating the directory for the export files.
In Step 7, we are exporting all the groups which are having item count greater than 1 (this logic identifies the duplicate items).
That is all for the code. Now, we will see the variation in outputs depending on the columns specified for duplicacy-check.
In Step 8, we specify the validation and display columns. For the first execution, we will check duplicate values in “Title” column.
In Step 9, we are calling the “Get-DuplicateListItems” function to find the duplicate values.
After the function executed successfully, we can see the following output.
In Step 10, we can see the output of this execution and can see 6 duplicate items found in the Title Column.
In Step 11, we can see the CSV file that is exported by the execution considering “Title” Column to be validated.
In Step 12, we see the output file and can notice duplicate values in “Title” Column.
Join the conversation! Your thoughts help the community grow.