Export to Excel

If you handle any sort of data in your job you’ve likely run into the common PowerShell cmdlets for dealing with data; Import-CSV, Export-CSV, Import-CliXML, and Export-Clixml. Sometimes, automation requires the processing of data. CSV and XML are both great ways to store and send data. The only thing is, no human chooses to manipulate data in CSV or XML. That is, for better or for worse, in the realm of Excel.

Opening in Excel

Excel has the ability to natively open CSV files, the text import wizard can handle copy and pasting of delimited information, and Power Query offers the ability to use as CSV as a data source. The process from PowerShell to Excel is simple. Use the Export-CSV cmdlet to export your data and if you’re lazy, have PowerShell invoke the file to start up Excel.

Get-Process | Export-Csv -Path ~\Desktop\junk.csv 
Invoke-Item ~\Desktop\junk.csv

There is a better way.

Import Excel

Doug Finke has written a PowerShell module for exporting information directly into Excel. Not simply data but charts, conditional text, Pivot Tables, and much more.

A quick way to tell if there are services you need to look into?

Get-Service | 
    Select-Object -Property DisplayName,Name,Status,StartType | 
    Export-Excel -show -Path ~\Desktop\Junk.xlsx -AutoFilter -AutoSize -BoldTopRow -ConditionalText $( New-ConditionalText stop New-ConditionalText auto yellow )

Check out the GitHub page for ImportExcel to get all of of the installation instructions as well as demonstrations on exactly how to use it. Automate the Excel reports you’ve been manipulating by hand for all of these years.