Tuesday, August 25, 2015

Out-GridView vs. Export-Csv

This question is something that came to me recently. It came to me because I have a wonderful script that dumps out server specs to a Out-GridView. The Out-GridView works great for quickly seeing information in a grid. The one down fall I have noticed is when selecting the data from it and copying it to me a program like Excel the header rows do not come with the copy.

Well with the script I talked about earlier I almost always end up viewing the data in Excel. So, I figured why not skip the copy paste to excel step.

$List is the data that has been collected previous to this point that I want to export.

This is how I viewed the data before.

$List | Out-GridView 

Ok, so lets change the output.

$List | Export-Csv ‘c:\scripts\export\export.csv

Well that worked it dumped the information to a csv and I open it in excel it has the headers.

At this point I said why am I opening Excel and then opening it in excel. Lets automate it.

Invoke-Item ‘c:\scripts\export\export.csv

This works great except for some reason there is some file header information. The header information was:

#TYPE System.Management.Automation.PSCustomObject

I found out this is the type of the output and can be removed with the –notype option like this.

Invoke-Item ‘c:\scripts\export\export.csv –noType

This worked nicely but I wanted to clean it up in a couple of ways.

  1. Create the directory that the script will go to.
    1. Catch the error it if it is already made.
  2. Create the file dynamically so that we can keep a history of the files
    1. this will be done dynamically with the get-date cmdlet.

This is what I ended up with.


$randomString = Get-Date -format M.d.yyyy.HH.mm.ss



md "c:\scripts\export\"





$csvFileName = 'c:\scripts\export\' + $randomString + '_GetSpecsExport.csv'




$List | Export-Csv $csvFileName -noType



Invoke-Item $csvFileName


Here is the whole script for anyone who wants to see it.


# Get Server Hardware specs

# 2015 Edward

# CPU / RAM / Disk Size / IP Address





##### Set Variables #######

$fileCount = 0

$List = @()

$i = 0




#get the list of servers to scan from serverlist.txt

$serverlist = "C:\scripts\computers.txt"





#get the count of servers for updates to the user.


$fileCount = (Get-Content $serverlist | Measure-Object).Count




#Loop the server list.

foreach ($server in Get-Content $serverlist) {


#increment the number of records proccessed



#Write to the screen whats happening.

Write-Progress -activity "Connecting to server $server" -status "Scanning: $i of $($fileCount)" -percentComplete (($i / $fileCount)  * 100)


#do a try if there is an issue spit out the error.

try {


    #IF there is an error stop and go to the next record.

    $ErrorActionPreference = 'Stop'


    $bios = Get-WmiObject Win32_BIOS -ComputerName $server

    Write-Progress -activity "Scanning server $server -  getting BIOS information" -status "Scanning: $i of $($fileCount)" -percentComplete (($i / $fileCount)  * 100)


    Write-Progress -activity "Scanning server $server - getting Processor information" -status "Scanning: $i of $($fileCount)" -percentComplete (($i / $fileCount)  * 100)

    $Proc = Get-WmiObject Win32_processor -ComputerName $server | Select-Object -First 1



    Write-Progress -activity "Scanning server $server - getting Memory information" -status "Scanning: $i of $($fileCount)" -percentComplete (($i / $fileCount)  * 100)

    $memory = Get-WmiObject Win32_physicalmemory -ComputerName $server



    Write-Progress -activity "Scanning server $server - getting System information" -status "Scanning: $i of $($fileCount)" -percentComplete (($i / $fileCount)  * 100)

    $system= Get-WmiObject Win32_ComputerSystem -ComputerName $server



    Write-Progress -activity "Scanning server $server - getting Disk information" -status "Scanning: $i of $($fileCount)" -percentComplete (($i / $fileCount)  * 100)

    $disks = Get-WmiObject -class Win32_LogicalDisk -ComputerName $server -Filter {DriveType=3}




    $disklist = " "

    foreach ($disk in $disks) {

        IF ($disklist -eq " "){$putAcomma = ""} ELSE {$putAcomma = ", "}  

        $size = [math]::Round(([int64]$disk.Size / 1073741824))

        $disklist = $disklist + $putAcomma + $disk.DeviceID + " " + [string]$size + " GB"


    Write-Progress -activity "Scanning server $server" -status "Scanning: $i of $($fileCount)" -percentComplete (($i / $fileCount)  * 100)



    #Set the array for the output.


    $List += [pscustomobject]@{

    'ComputerName'         = $server

    'Manufacturer'        = $bios.Manufacturer

    'Model'               = $system.Model

    'BIOS Version'        = $bios.Version

    'Serial Number'       = $bios.SerialNumber

    'Processor Number'    = $system.NumberOfProcessors

    'Processor Name'      = $proc.name

    'CPUs'                 = $system.NumberOfLogicalProcessors

    'Speed (MHZ)'          = $proc.CurrentClockSpeed

    'RAM (GB)'             = $system.TotalPhysicalMemory / 1GB -as [int]

    'Used RAM slot'       = $memory.count

    'DiskList'             = $disklist



    } catch {

    $List += [pscustomobject]@{

    'ComputerName'         = $server

    'CPUs'                 = " ERROR "

    'Speed (MHZ)'          = " ERROR "

    'RAM (GB)'             = " ERROR "

    'DiskList'             = $error[0].exception







write-progress -activity "Completed."


$randomString = Get-Date -format M.d.yyyy.HH.mm.ss



md "c:\scripts\export\"





$csvFileName = 'c:\scripts\export\' + $randomString + '_GetSpecsExport.csv'




$List | Export-Csv $csvFileName -noType



Invoke-Item $csvFileName


No comments:

Post a Comment