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.