How to export managed metadata from subfolders and documents to csv

Question

I have a document library and at the top level we have a number of folders for each year i.e. 2024, 2023, 2022, 2021 etc. Against these yearly folders we have a Content Type of "Yearly Folder".

Under each yearly folder we have a huge list of Folders each folder is classed as a case and has a Content type of "Case Folder"

In the script if I change the value in this line <Query><Where><Eq><FieldRef Name='ContentType'/><Value Type='Text'>Case Folder</Value></Eq></Where></Query></View>"

to Yearly Folder then I get the information of the top level yearly folders but when i change it to Case Folder. The script runs but nothing is exported

I currently am using the below script but it doesn't seem to be working.

#Add-Content -Path C:\ManagedMetadata.csv -Value '"Case Folder", "CaseName"'

#Get the following folders

#Case Number / Folder Name CaseNumber

#Case Name

#Case Type

#Aircraft Type

#Case GUID

#########################################

#Set following three variables before run

$siteUrl ="https://url here"

#$siteUrl ="https://url here/"

$libraryName = "Case"

$csvLocation = "C:\ManagedMetadata.csv"

#########################################

#Add folder header

$folderHeader = "Location,Title,Case Number,Case Name,Case Type,Aircraft Type,Case GUID,"

Add-Content -Path $csvLocation -Value $folderHeader

Connect-PnPOnline -url $siteUrl -UseWebLogin

$items = Get-PnPListItem -List $libraryName -Query "<View><ViewFields><FieldRef Name='Title' /><FieldRef Name='CaseNumber' /><FieldRef Name='CaseName' /><FieldRef Name='CaseType' /><FieldRef Name='AircraftType' /><FieldRef Name='CaseGUID' /></ViewFields><Query><Where><Eq><FieldRef Name='ContentType'/><Value Type='Text'>Case Folder</Value></Eq></Where></Query></View>"

foreach($item in $items){

$folderInfo += '","'

$folderInfo += $item.FieldValues["Title"]

$folderInfo += '","'

$folderInfo += $item.FieldValues["CaseNumber"]

$folderInfo += '","'

$folderInfo += $item.FieldValues["CaseName"]

$folderInfo += '","'

$folderInfo += $item.FieldValues["CaseType"].Label

$folderInfo += '","'

$folderInfo += $item.FieldValues["AircraftType"]

$folderInfo += '","'

$folderInfo += $item.FieldValues["CaseGUID"]

$folderInfo += '",'

$folderInfo

Add-Content -Path $csvLocation -Value $folderInfo
```}

Answer 1

I don't know if this helps at all, but since you have a "PowerShell" tag on this question, here's a less cluttered version of your script:

#Add-Content -Path C:\ManagedMetadata.csv -Value '"Case Folder", "CaseName"'
#Get the following folders
#Case Number / Folder Name CaseNumber
#Case Name
#Case Type
#Aircraft Type
#Case GUID

#########################################
#Set following three variables before run
$siteUrl = "https://url here"
$libraryName = "Case"
$csvLocation = "C:\ManagedMetadata.csv"
#########################################

$row = [ordered]@{}
Connect-PnPOnline -url $siteUrl -UseWebLogin
$Query = "<View><ViewFields><FieldRef Name='Title' /><FieldRef Name='CaseNumber' /><FieldRef Name='CaseName' /><FieldRef Name='CaseType' /><FieldRef Name='AircraftType' /><FieldRef Name='CaseGUID' /></ViewFields><Query><Where><Eq><FieldRef Name='ContentType'/><Value Type='Text'>Case Folder</Value></Eq></Where></Query></View>"
Get-PnPListItem -List $libraryName -Query $Query |
    ForEach-Object{
        $row.Clear                                          # remove any previous keys and values
        $row["Location"]        = "????"                    # $LibraryName maybe???
        $row["Title"]           = $_.FieldValues["Title"]
        $row["CaseNumber"]      = $_.FieldValues["CaseNumber"]
        $row["CaseName"]        = $_.FieldValues["CaseName"]
        $row["CaseType"]        = $_.FieldValues["CaseType"].Label
        $row["AircraftType"]    = $_.FieldValues["AircraftType"]
        $row["CaseGUID"]        = $_.FieldValues["CaseGUID"]
        [PSCustomObject]$row
} | Export-CSV $csvLocation -NoTypeInformation

I don't know what goes into the "Location" column of your CSV since that line appears to be missing in your posted code.One of the SharePoint folks (I hope) will help with the query problem.

Answer 2

Hi @Glenn Skinner,

Thank you for posting in this community.

Try adding the -FolderServerRelativeUrl parameter to specify that the results are fetched from a certain folder.

Get-PnPListItem -List Samples -FolderServerRelativeUrl "/sites/Spe24/Document1/2024%20Yearly%20Folder"

Reference: Get-PnPListItem.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Hi @Glenn Skinner,

Sorry for the late reply, as it took me a little time to find a solution.

This issue occurs because SharePoint Online uses the Large List Resource Throttling feature. By default, the list view threshold is configured at 5,000 items. When the defined item limit in a list is exceeded, the message is displayed.You can add <RowLimit>1000</RowLimit> in the View tag in your query. This way we can fix the error.

Reference: SharePoint Online: Get List Items from Large Lists ( >5000 Items) using PowerShell without List View Threshold Exceeded Error.

Note: Microsoft is providing this information as a convenience to you. The sites are not controlled by Microsoft. Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. Please make sure that you completely understand the risk before retrieving any suggestions from the above link. 

For Question: Also this would only cover the 2024 yearly folder but i have folders for every year going back to 2012 so would I need to run it separately for each year

I'm afraid yes, we need to set the name of Yearly Folder manually, because in -FolderServerRelativeUrl we are specifying to get the contents of the Yearly Folder.

In addition, exporting by year is not prone to 5000 view threshold errors.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

Hi @Glenn Skinner,

I viewed your script with no problems, but it is true that the script encounters a view limit of 5000. I consulted my colleague and we can also use Power Automate to export managed metadata field values. This way there is no limit and no need to modify the destination folder every time. We can extract the entire document library of managed metadata field values.

Here are steps:

1. Create a New Flow:
   • Go to Power Automate and create a new flow. You can choose an instant cloud flow if you want to trigger it manually or a scheduled cloud flow if you want it to run at specific intervals.
2. Get Files (Properties Only):
   • Add the "Get files (properties only)" action from the SharePoint connector. Select the site address and the document library where your files are stored.
3. Create an Excel File:
   • Add the "Create file" action from the OneDrive or SharePoint connector to create a new Excel file. Specify the folder path and file name.
4. Create a Table in Excel:
   • Use the "Create table" action to create a table in the Excel file. You need to specify the file, table range, and column names. The column names should match the properties you want to export (e.g., File Name, Created Date, Modified Date).
5. Add Rows to the Table:
   • Add the "Add a row into a table" action. Use a loop to iterate through each file retrieved in step 2 and add a row to the table for each file. Map the file properties to the corresponding columns in the table.
6. Save and Test the Flow:
   • Save your flow and test it to ensure it works as expected. You should see the file properties exported to the Excel file.

Hope this is helpful.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.