How to Use Excel to Manage Command Output from Windows PowerShell

This excerpt from Windows PowerShell Cookbook, Second Edition will show you how to use Excel to manipulate or visualize the output of a Windows PowerShell command.

---

Use PowerShell’s Export-Csv cmdlet to save the output of a command in a CSV file, and then load that CSV in Excel. If you have Excel associated with .CSV files, the Invoke-Item cmdlet launches Excel when you provide it with a .CSV file as an argument.

Example 10-7 demonstrates how to generate a CSV containing the disk usage for subdirectories of the current directory.

Example 10-7. Using Excel to visualize disk usage on the system

PS > $filename = "c:\temp\diskusage.csv"
PS >
PS > $output = Get-ChildItem | Where-Object { $_.PsIsContainer } |
    Select-Object Name,
        @{ Name="Size";
           Expression={ ($_ | Get-ChildItem -Recurse |
               Measure-Object -Sum Length).Sum + 0 } }

PS > $output | Export-Csv $filename
PS >
PS > Invoke-Item $filename

In Excel, you can manipulate or format the data as you wish. As Figure 10-1 shows, we can manually create a pie chart.




Although used only as a demonstration, Example 10-7 packs quite a bit into just a few lines.

The first Get-ChildItem line gets a list of all the files in the current directory and uses the Where-Object cmdlet to restrict those to directories. For each of those directories, you use the Select-Object cmdlet to pick out the Name and Size of that directory.

Directories don’t have a Size property, though. To get that, we use Select-Object’s hashtable syntax to generate a calculated property. This calculated property (as defined by the Expression script block) uses the Get-ChildItem and Measure-Object cmdlets to add up the Length of all files in the given directory.