Export Exchange Online query to CSV

Question

I'm trying to export a list of mailbox sizes of users in Exchange Online to a CSV file on the desktop of the user running the command.

The command to retrieve the information I want from Exchange Online works. So, I don't need help with that part. The only part I need assistance with is getting the results to export to a .csv on my desktop. PowerShell is running as an administrator.

Here is what I have:

# Get the current user's desktop path
$desktopPath = "$env:USERPROFILE\Desktop"

# Run the command and export the results to a CSV file
Get-EXOMailbox -RecipientTypeDetails UserMailbox -ResultSize Unlimited |
    Get-EXOMailboxStatistics |
    Select-Object DisplayName, TotalItemSize, MailboxGuid |
    Sort-Object TotalItemSize -Descending |
    Export-Csv -Path "$desktopPath\MailboxStatistics.csv" -NoTypeInformation

Here is the error I'm getting:

Export-Csv : Could not find a part of the path 'C:\Users\[username]\Desktop\MailboxStatistics.csv'.
At line:5 char:5
+     Export-Csv -Path "$desktopPath\MailboxStatistics.csv" -NoTypeInfo ...
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OpenError: (:) [Export-Csv], DirectoryNotFoundException
    + FullyQualifiedErrorId : FileOpenFailure,Microsoft.PowerShell.Commands.ExportCsvCommand

Answer 1

Your error message implies one of two causes:

• The directory in which you want to store the CSV file, C:\Users\[username]\Desktop, doesn't actually exist.

• The directory path contains [ and/or ] characters (unlikely in your case; I'm assuming that [username] is just a placeholder), which PowerShell interprets as wildcard metacharacters, due to your use of the -Path rather than the -LiteralPath parameter.

---

• Using -LiteralPath eliminates the potential misinterpretation of the path as a wildcard expression.

• While you could use $null = New-Item -Type Directory -Force $env:USERPROFILE\Desktop to create the directory on demand, the absence of this standard directory suggests that the current user's desktop is located elsewhere, which can happen if the user-profile directory ("$env:USERPROFILE") is configured to not be the same as the user's home directory ("${env:HOMEDRIVE}${env:HOMEPATH}")

  • Using "${env:HOMEDRIVE}${env:HOMEPATH}\Desktop" is one option, though a more direct expression of the intent is to use [Environment]::GetFolderPath('Desktop')

Therefore:

# Get the current user's desktop path using the relevant .NET API.
$desktopPath =  [Environment]::GetFolderPath('Desktop')

# Run the command and export the results to a CSV file.
# Note the use of -LiteralPath.
Get-EXOMailbox -RecipientTypeDetails UserMailbox -ResultSize Unlimited |
    Get-EXOMailboxStatistics |
    Select-Object DisplayName, TotalItemSize, MailboxGuid |
    Sort-Object TotalItemSize -Descending |
    Export-Csv -LiteralPath "$desktopPath\MailboxStatistics.csv" -NoTypeInformation