Things I Learned #6: Formatting output in PowerShell

1 minute read til learning   powershell Comments

I've been using PowerShell a lot more lately, and learning something almost everyday, but this one was a pleasant surprise.

Imagine you're grabbing a list of Resource Groups from Azure, or maybe a list of all the services on your machine, but you want to pull those results into Excel to do some filtering and sorting. How would you do it from the command line using PowerShell?

PowerShell has a couple cool cmdlets for this kind of thing. I'll look at each, but first I'll start with simply formatting the data on the command line.

For example, if you retrieve a list of resource groups from Azure,

the default output is kinda ugly, and not something you could easily import into Excel.
The output from executing Get-AzResourceGroup.
The default output from Az-GetAzResourceGroup.

You can format the output of a command by piping the output to the Format-Table cmdlet.

Az-GetAzResourceGroup | Format-Table
The output from executing Get-AzResourceGroup and piping the results to the Format-Table cmdlet.
The output from Az-GetAzResourceGroup piped to the Format-Table cmdlet.
That gets closer, but there's no easy way to get that into Excel, although I could send it to an editor window in Visual Studio Code by doing this
Az-GetAzResourceGroup | Format-Table | code -


Ok, so I learned that there's a really handy PowerShell cmdlet to export results to CSV and is as easy to use as

Az-GetAzResourceGroup | ExportCSV resourceGroups.csv
With that, you can now import the resulting file into Excel and start slicing and dicing the data any way you want.

Here's a non-Azure example to show you can really use it with anything

ls | Export-CSV dirListing.csv

This one is a two-fer - there's another cmdlet that let's you export results to HTML and works much the same way Export-CSV works.

Az-GetAzResourceGroup | ConvertTo-HTML > resourceGroups.html

In learning you will teach, and in teaching you will learn. ― Phil Collins