Powershell script taking too long to export results to .csv

Sharon Beckett 1 Reputation point
2023-01-13T13:29:54.7966667+00:00

I have a script that works perfectly until it comes to exporting the csv file. I have tried the script with a sample batch of data (50) and it runs without any issue. However, I have over 70,000 objects in the source data and even breaking it down to multiple csv's with chunks of 10,000 takes an age to get the .csv exported.

I know that the for each part does not take long (10,000 lines took about 20 mins to pull the data) but the bottleneck appears to be the export-csv part. Can anyone suggest any improvements I could make?

N.B The paths marked as HIDDEN are valid in my script but just replaced here for privacy

$mailboxes = @(Import-Csv -Path 'HIDDEN'|get-mailbox)

$report = @()

foreach ($mailbox in $mailboxes)
{
    $stats = Get-MailboxFolderStatistics $mailbox -FolderScope Recoverableitems|where {$_.FolderPath -eq "/DiscoveryHolds"}

    $mbObj = New-Object PSObject
    $mbObj | Add-Member -MemberType NoteProperty -Name "Display Name" -Value $mailbox.DisplayName
    $mbObj | Add-Member -MemberType NoteProperty -Name "UPN" -Value $mailbox.UserPrincipalName
    $mbObj | Add-Member -MemberType NoteProperty -Name "Size" -Value $stats.FolderSize
    $report += $mbObj
}

$report| Export-CSV "HIDDEN"
Microsoft Exchange Online
Microsoft Exchange Online Management
Microsoft Exchange Online Management
Microsoft Exchange Online: A Microsoft email and calendaring hosted service.Management: The act or process of organizing, handling, directing or controlling something.
4,338 questions
Exchange Server Management
Exchange Server Management
Exchange Server: A family of Microsoft client/server messaging and collaboration software.Management: The act or process of organizing, handling, directing or controlling something.
7,461 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. MotoX80 32,551 Reputation points
    2023-01-13T15:58:46.0433333+00:00

    I don't have access to AD/Exchange, so I tested with the file system. One thought is that you are adding an object into the $mbObject values, when you only need a string that you can then export. 

    cls
$report = @()
(measure-command {
    Get-ChildItem -Recurse -File -ErrorAction SilentlyContinue | foreach {
        $mbObj = New-Object PSObject
        $mbObj | Add-Member -MemberType NoteProperty -Name "Display Name" -Value $_.Name
        $mbObj | Add-Member -MemberType NoteProperty -Name "UPN" -Value $_.CreationTime
        $mbObj | Add-Member -MemberType NoteProperty -Name "Size" -Value $_.VersionInfo
        $report += $mbObj
    }
}).TotalSeconds
$report.count
(Measure-Command {$report | Export-Csv c:\temp\test.csv -Force}).TotalSeconds

"------------"
$report = @()
(measure-command {
    Get-ChildItem -Recurse -File -ErrorAction SilentlyContinue| foreach {
    $report += [PSCustomObject] @{
                 "Display Name" =  $_.Name.tostring();
                 "UPN" = $_.CreationTime.tostring();
                 "Size" = $_.VersionInfo.tostring()
        }
    }
}).TotalSeconds
$report.count
(Measure-Command {$report | Export-Csv c:\temp\test.csv -Force}).TotalSeconds

    I ran this against C:\programData and got these results.

    148.5061734
62466
1.0384613
------------
98.8008639
62466
0.564858

    The second technique of building $report was a lot faster. If you run that script, you would need to run it twice to populate the system file cache to get good results.

    Try that method.