Problem
You want to use Excel to manipulate or visualize the output of a command.
Solution
Use PowerShell’s ExportCsv 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 InvokeItem cmdlet launches Excel when you provide it with a .CSV file as an argument.
Example 87 demonstrates how to generate a CSV containing the disk usage for sub directories of the current directory.
Example 87. Using Excel to visualize disk usage on the system
PS >$filename = "c:\temp\diskusage.csv" PS > PS >$output = GetChildItem | WhereObject { $_.PsIsContainer } | >> SelectObject Name, >> @{ Name="Size"; >> Expression={ ($_ | GetChildItem Recurse | >> MeasureObject Sum Length).Sum + 0 } } >>
Example 87. Using Excel to visualize disk usage on the system (continued)
PS >$output | ExportCsv $filename PS > PS >InvokeItem $filename
Discussion
Although used only as a demonstration, Example 87 packs quite a bit into just a few lines.
The first GetChildItem line gets a list of all the files in the current directory and uses the WhereObject cmdlet to restrict those to directories. For each of those directories, you use the SelectObject cmdlet to pick out the Name and Size of that directory.
Directories don’t have a Size property though. To get that, we use SelectObject’s hashtable syntax to generate a calculated property. This calculated property (as defined by the Expression script block) uses the GetChildItem and MeasureObject cmdlets to add up the Length of all files in the given directory.
