How to export powershell output to CSV - Using hash table ?
Technet - Using export-csv
We have many ways to export a PowerShell output to CSV format, and the simple one is just using Expoer-CSV which is familiar to all... Below is the method exporting a powershell output to CSV format by using hash table. Here we are updating (Appending) hash table from a For-each loop and exporting the result to CSV format. At the end using Expoer-CSV to export the hash table values.Benefit is to make out result as per our customized way.
That's all I am trying here...
Scenario : Just pulling few details of a grop of machines
That's all I am trying here...
Scenario : Just pulling few details of a grop of machines
Technet - Using export-csv
001
|
Get-Process | export-csv C:\vivek\process.csv
|
Passing the result to an hash table and exporting to CSV
If we want to export a customized powershell output to CSV file, we can use a hash table to do that. Here I am giving a scenario based example for creating a csv output using hash table.
Scenario : Pinging a group of system and exporting the result to csv file.And also checking the Operating system name and RAM size of the system,
1. Creating an empty hash table to store the result and Getting Machine Name from a .txt file located on our desktop.
You can use your own way to input the system details.
You can use your own way to input the system details.
001
002 003 004 |
$details = @()
$pc = Get-Content $env:USERPROFILE\desktop\pc.txt foreach ($sys in $pc) { |
2. Ping checking (Offline) - This script portion will start based on ping result.If the ping result is false, it just create an hash table with predefined content.And if the result is true, there are some wmi query to check the system details.
Forming hash table and adding the values to PSObject based on ping result
Using [ordered]@ table to keep the order as we given
001
002 003 004 005 006 007 008 009 010 |
if(!(Test-Connection -Cn $sys -BufferSize 16 -Count 1 -ea 0 -quiet))
{ $Result = [ordered]@{ MACHINE_NAME = "$sys" PING_STATUS = "MACHINE OFFLINE" OS_NAME = "N/A" TOTAL_PHYSICALMEMORY = "N/A" } $Details += New-Object PSObject -Property $Result } |
We have to create a PSObject to store and append the hash table values. After the for-each loop, we will be exporting this psobject to comma separate value (csv)
001
|
$Details += New-Object PSObject -Property $Result
|
3. Ping Checking (Online) - As I mentioned previously, this is the true portion of ping result. If the ping result is true, we are checking the OS_NAME and TOTAL PHYSICALMEMORY of the system.
001
002 003 004 005 006 |
$osname = "N/A"
$physicalmemory = "N/A" $sysdetails = "N/A" $sysdetails = Get-WmiObject -Class Win32_computersystem -ComputerName $sys | select -ExpandProperty TotalPhysicalMemory $osname = Get-WmiObject -Class Win32_OperatingSystem -ComputerName $sys | select -ExpandProperty caption $physicalmemory = ($sysdetails)/1mb -as [int] |
Forming hash table and adding the values to PSObject based on ping result
001
002 003 004 005 006 007 |
$Result = [ordered]@{
MACHINE_NAME = "$sys" PING_STATUS = "MACHINE ONLINE" OS_NAME = "$OSNAME" TOTAL_PHYSICALMEMORY = "$physicalmemory" } $Details += New-Object PSObject -Property $Result |
At the end exporting all the result to .CSV file. I just created a location path with a customized name. You can give anything which you like
001
002 003 |
$date = Get-Date -UFormat "%m-%d-%y"
$pathofcsv = "$env:userprofile\desktop\" + "Ping_Result_" + "$date" + ".csv" $Details | export-csv -Path $pathofcsv -NoTypeInformation |
Output will looks like below :)
Now putting all the script together :
001
002 003 004 005 006 007 008 009 010 011 012 013 014 015 016 017 018 019 020 021 022 023 024 025 026 027 028 029 030 031 032 033 034 035 036 037 038 |
#Machine availability check
#Handling Output using CSV $details = @() $pc = Get-Content $env:USERPROFILE\desktop\pc.txt foreach ($sys in $pc) { if(!(Test-Connection -Cn $sys -BufferSize 16 -Count 1 -ea 0 -quiet)) { $Result = [ordered]@{ MACHINE_NAME = "$sys" PING_STATUS = "MACHINE OFFLINE" OS_NAME = "N/A" TOTAL_PHYSICALMEMORY = "N/A" } $Details += New-Object PSObject -Property $Result } else { $osname = "N/A" $physicalmemory = "N/A" $sysdetails = "" $sysdetails = Get-WmiObject -Class Win32_computersystem -ComputerName $sys | select -ExpandProperty TotalPhysicalMemory $osname = Get-WmiObject -Class Win32_OperatingSystem -ComputerName $sys | select -ExpandProperty caption $physicalmemory = ($sysdetails)/1mb -as [int] $Result = [ordered]@{ MACHINE_NAME = "$sys" PING_STATUS = "MACHINE ONLINE" OS_NAME = "$OSNAME" TOTAL_PHYSICALMEMORY = "$physicalmemory" } $Details += New-Object PSObject -Property $Result } } $date = Get-Date -UFormat "%m-%d-%y" $pathofcsv = "$env:userprofile\desktop\" + "Ping_Result_" + "$date" + ".csv" $Details | export-csv -Path $pathofcsv -NoTypeInformation |
Download Script