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.
In the case of Windows 7 and Server
2008, you don't need to download any extra files, just 'Add Feature' Windows PowerShell.
However, for older operating systems, installing can be confusing because
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, I recommend choosing the
ISE (Integrated Scripting Engine) version, it
will save you buying a text editor.
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.
Note 1: The reason that I used the
variable $File is that I want to remind you to change the value for
the path before
running this script on your machine.
Note 2: Writing this note reminds me of reading those silly
warning notices on packages, but I feel I must remind newbies that you really do need a spreadsheet like Excel to
open the resulting csv file.
Note 3: 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
Note 4: #Type System.Diagnostics.Process in the first row
is the .NET Framework type of the object.
Guy Recommends: Solarwinds' Free 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 to import the users. Optionally, you can
provide the name of the OU where the new accounts will be born.
There are also two bonus tools in this free download, and all 3 have been approved by Microsoft:
Because you cannot use Format-Table, Select-Object will allow you to
choose just the properties that you need for the export from PowerShell to
spreadsheet.
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.
Guy Recommends: A Free Trial of the Network Performance Monitor
(NPM)
Solarwinds'
Orion performance monitor
will help you discover what's happening on your network. This
utility will also guide you through troubleshooting; the dashboard will
indicate whether the root cause is a broken link, faulty equipment or
resource overload.
Perhaps the NPM's best feature is the way it suggests solutions to network
problems. Its
second best feature is the ability to monitor the health of individual VMWare
virtual machines. If you are interested in troubleshooting, and creating network maps, then I recommend that you take advantage of Solarwinds' offer.
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 spreadsheet research always throws up at least one surprise.
»
Summary of PowerShell Export-CSV
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.
If you like this page then please share it with your friends
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.
Windows Management Instrumentation (WMI) is one of the hidden
treasures of Microsoft operating systems.
Fortunately, Solarwinds
have created the
Free WMI Monitor so that you can actually see and understand these gems of
performance information. Take the guess work out of which
WMI counters to use for applications like Microsoft Active Directory,
SQL or Exchange Server.
