0

I have a PS script that is taking CSV files (the largest being about 170MB) and splitting it into multiple smaller CSVs based on the ListGuid value. It is then taking each file and uploading it to a specific path in SharePoint using PnP based on the Web Guid and List Guid. This script it taking forever to run and I am having trouble finding ways to optimize it. Any help would be appreciated. Here is the script:

$PermissionsFile = Get-ChildItem -Path $downloadFilePath -Filter *.csv

foreach ($file in $PermissionsFile) {

    $SiteCollectionReport = Import-Csv -Path "$downloadFilePath/$($file.Name)"

    $filteredListTestFile = $SiteCollectionReport | Where-Object {$_.Type -eq "List"}
    $groupedListFile = $filteredListTestFile | Select-Object Url, ListGuid -Unique
    $subWebsConnection = Connect-SharePoint -WebUrl $SiteCollectionReport.Url[0] -CheckForAppCredentials
    $subWebs = Get-PnPSubWebs -Recurse -IncludeRootWeb -Connection $subWebsConnection | Select-Object Url, Id
    $permissionsSiteConnection = Connect-SharePoint -WebUrl "https://company.sharepoint.com/sites/edmsc/Internal" -CheckForAppCredentials
    
    foreach ($guid in $groupedListFile) {
        $webGuid
        $listGuid = $guid.ListGuid
        $SiteCollectionReport | Where-Object {$_.ListGuid -like $listGuid -and $_.Type -eq "List"} | Export-Csv -Path "Path\Permissions $($listGuid).csv" -NoTypeInformation
        $url = $guid.Url

        $siteCollectionName = $url.Split("/")[4]
        
        if ($url.Contains(" ")) {

            $url = $url.Replace(" ","%20")
        }

        $split = $url.substring(0, $url.LastIndexOf("/"))

        if ($split.Contains("Lists")) {

            $split = $split -split "Lists"

        }

        foreach ($web in $subWebs) {

            if ($web.Url -eq $split) {

            $webGuid = $web.Id
            #Write-Host "Adding permissions reports for $split"
            #Write-Host "List Guid $listGuid" 
            Write-Host "Web Guid $webGuid"
            }
        }

        $fieldValues = @{"ObjectType"="List/ListItem"; "WebGuid"=$webGuid; "ListGuid"=$listGuid}
        #$permissionsSiteWeb = Get-PnPWeb -Connection $permissionsSiteConnection
        Add-PnPFile -Path "Path\Permissions $($listGuid).csv" -Folder "SiteCollectionPermissions/$siteCollectionName/$webGuid" -Values $fieldValues  -Connection $permissionsSiteConnection
    }

    Write-Host "Deleting Permissions Files..."
    Get-ChildItem -Path "Path" -Include *.csv* -File -Recurse | ForEach-Object { $_.Delete()}
}
Schmit
  • 41
  • 5
  • 12
  • 2
    If you add some logging with time stamps you’ll see which bit of the script is the “hot path” - that might help you work out where to spend time optimising. – mclayton Jun 12 '22 at 16:54
  • The slowest piece seems to be with adding the files to SP using PnP. The splitting of the files into smaller files seems to be rather quick. I am not sure why the PnP calls would be going so slow – Schmit Jun 12 '22 at 17:11
  • @mclayton It also seems like Where-Object cmdlet is causing issues and taking a while to sort through. Would it be better to use a hastable somehow instead of Where-Object? – Schmit Jun 12 '22 at 17:20
  • 1
    Instead of ```Where-Object {$_.ListGuid -like $listGuid -and $_.Type -eq "List"}``` you could *try* ```Where-Object {$_.ListGuid -eq $listGuid}```. You already know ```$_.Type -eq "List"``` because ```$filteredListTestFile```, and if the guide are *identical* you can use ```-eq``` instead of ```-like```. You’ll have to test to confirm if it still works though… – mclayton Jun 12 '22 at 18:44
  • @mclayton Thanks, so I tried this `$SiteCollectionReport.Where({$_.ListGuid -eq $listGuid -and $_.Type -eq "List"})`. I replaced the Were-Object cmdlet with the Where method and changed it to -eq instead of -like. Seems to be a little faster. I need the `$_.Type -eq "List"` though because if I don't it also picks up other items in the file that besides List items – Schmit Jun 12 '22 at 18:49
  • 1
    Ok, just a thought, perhaps a bad thought since I haven't tried this, but if PowerShell 5.1 then maybe [Foreach -Parallel](https://learn.microsoft.com/en-us/powershell/module/psworkflow/about/about_foreach-parallel?view=powershell-5.1), and if PowerShell 7.x then maybe [Foreach-Object -Parallel](https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.core/foreach-object?view=powershell-7.2). – Darin Jun 12 '22 at 22:00
  • @Darin Thanks for the advice, looking into this now and going to give it a test run! Will update with how it goes – Schmit Jun 12 '22 at 22:54
  • You doing a *recurse* `Get-ChildItem` for each `$guid`. I don't know how big the folder is, but you better do this once and index the `csv` files in a hashtable like: `$CsvFiles[$BareName]=$FilePath1,$FilePath2,...` (assuming that there could be multiple files with the same name in de folder) – iRon Jun 13 '22 at 07:44

0 Answers0