Ezine 207 – PowerShell Export-CSV Cmdlet

Windows PowerShell Export-CSV Cmdlet

Comma separated values are the ideal format for translating lines of text into rows in a spreadsheet.  Since much of PowerShell’s output is in columns, all we need is a cmdlet which will export CSV data, then we can exploit Excel’s mathematical capability.

PowerShell Export-CSV Topics

 ♣

PowerShell Pre-requisites and Checklist

In the case of Windows 7 and later, you don’t need to download any extra files, just: ‘Add Feature’ –> Windows PowerShell.  However, for older operating systems, there are different versions of PowerShell for XP, Windows Server 2003 and Vista.  For such legacy systems only, you need to download PowerShell from Microsoft’s site.

Once you have installed PowerShell 2.0 or later, I recommend choosing the ISE (Integrated Scripting Engine) version, it will save buying a text editor.

This Week’s Secret

One thing I have missed in PowerShell is the flexibility of a spreadsheet.  While PowerShell has a serviceable ‘If’ statement, in truth I find Excel’s battery of functions much easier to deliver the results that I need.  Also controlling the decimal point is only ever one click away in a spreadsheet.  Now thanks to Export-CSV I have the best of both worlds, PowerShell at the backend collecting the raw data, Excel at the frontend displaying the final figures and charts.  Incidentally, a bonus of using this duo is that I tend to save results more than when I use PowerShell alone.

Example 1: PowerShell Export-CSV

Scenario: We wish to analyse the memory usage of our computer’s processes in a spreadsheet.  The knack is to export all the raw data collected by PowerShell then use the capability of Excel to manipulate and refine the figures.

# PowerShell Export-CSV Example Processes
Clear-Host
$FilePath = "E:\PowerShell\Process\mem3.csv"
Get-Process | Export-CSV $FilePath

Note:  One reason that I have used a variable to control the path, is that I want to remind of the need to change its value before you run this script on your machine.

Note:  Writing this reminds me of reading those silly warning notices on packages, but you really do need a spreadsheet like Excel to open the resulting csv file.

Note:  Save Frustration
You cannot use Format-Table with the Export-CSV cmdlet.  However you can use Select-Object to choose just the properties that you want to see in the spreadsheet.  See Example 2.

Excel Spreadsheet Screenshot

PowerShell Export-CSV

Note: #Type System.Diagnostics.Process in the first row is the .NET Framework type of the object.

Guy Recommends:  SolarWinds’ Free Bulk Import ToolFree Download Solarwinds Bulk Import Tool

Import users from a spreadsheet.  Just provide a list of the users with their fields in the top row, and save as .csv file.  Then launch this FREE utility and match your fields with AD’s attributes, click and import the users.

Optionally, you can provide the name of the OU where the new accounts will be born. Download your FREE bulk import tool.

If you need more comprehensive software, download a free trial of SAM (Server & Application Monitor)

Example 2: Export-CSV with Select-Object

Because you cannot use Format-Table, Select-Object will allow you to choose just the properties that you need for the export.

# Export-CSV Select-Object to filter output
Clear-Host
$FilePath = "e:\PowerShell\Process\Serv5s.csv"
Get-Service | Select-Object DisplayName, CanStop | Export-CSV $FilePath

Note: While I chose DisplayName and CanStop, you could research other properties in the underlying cmdlet Get-Service.

Example 3: Filtering With Where-Object Clause

Scenario: You need to export a list of just services that are running, you don’t need those that are stopped.  Export-CSV, with a where {clause}, is a useful technique for filtering the data before you export to the spreadsheet.

# PowerShell Export-CSV Example Services Running
Clear-Host
$FilePath = "E:\PowerShell\Process\Serv1s.csv"
Get-Service | Where{$_.status -eq "Running"} |Export-CSV $FilePath

Engineer's Toolset v10Guy Recommends: SolarWinds Engineer’s Toolset v10

This Engineer’s Toolset v10 provides a comprehensive console of 50 utilities for troubleshooting computer problems.  Guy says it helps me monitor what’s occurring on the network, and each tool teaches me more about how the underlying system operates.

There are so many good gadgets; it’s like having free rein of a sweetshop.  Thankfully the utilities are displayed logically: monitoring, network discovery, diagnostic, and Cisco tools.  Try the SolarWinds Engineer’s Toolset now!

Download your fully functional trial copy of the Engineer’s Toolset v10

Research Export-CSV Parameters

While you cannot append format-table, you can research the properties of Export-CSV

# PowerShell Export-CSV Parameters
Clear-Host
Get-Help Export-CSV -full

Checking the help file will reveal useful parameters, for instance you can change the delimiter from the default comma to the semicolon.  Another useful parameter, albeit one with a strange name, is -NoClobber, this prevents you from over-writing a previously created file.

Export-CSV Alias EpCsv

With Microsoft, there are always at least three ways of doing everything, what seems like redundancy when you are an expert, seems like perspective when you are a beginner.  One obvious example is that you can abbreviate format-Table to ft.  As you increase your range of PowerShell commands, keep an eye out for another PowerShell Alias, for example gci (Get-Childitem).

 

Researching Similar PowerShell Cmdlets

# PowerShell CSV Cmdlet Research
Clear-Host
Get-Command -noun CSV

As expected there is an Import-CSV cmdlet, but you may not realize there is a ConvertTo-Csv cmdlet.  PowerShell -noun or -verb research always throws up at least one surprise.

  ˚

Summary of PowerShell Export-CSV

Spreadsheets are the best tool for manipulating and charting tabular data, thus seek the PowerShell Export-CSV cmdlet and display your data in Excel.  My biggest surprise was that I couldn’t use Format-Table with Export-CSV.  Fortunately, you can use Select-Object, or a where clause, to reduce the raw data to a more manageable size.

Guy Recommends: Tools4ever’s UMRAUMRA The User Management Resource Administrator

Tired of writing scripts? The User Management Resource Administrator solution by Tools4ever offers an alternative to time-consuming manual processes.

It features 100% auto provisioning, Helpdesk Delegation, Connectors to more than 130 systems/applications, Workflow Management, Self Service and many other benefits. Click on the link for more information onUMRA.

If you like this page then please share it with your friends

 


See more Microsoft PowerShell output tutorials:

PShell Home   • Out-File   • Out-GridView   • ConvertTo-Csv   • ConvertTo-Html   • ConvertFrom-Csv

Tee-Object   • Import-CSV   • Format-Table   • PowerShell Here-String  • ConvertFrom-JSON

Export-CliXml   • Format-List   • Read-Host    • PowerShell Get-History   • -f format   • Pipe to file

Please email me if you have a better example script. Also please report any factual mistakes, grammatical errors or broken links, I will be happy to correct the fault.