Transpose.ps1
|
#Requires -Version 4 <#PSScriptInfo .VERSION 0.2 .GUID 5d99dc80-0976-499d-824e-4143a64e7724 .AUTHOR Sam Boutros .COMPANYNAME Public Domain .COPYRIGHT Public Domain .TAGS Transpose .LICENSEURI https://opensource.org/licenses/MIT .PROJECTURI https://superwidgets.wordpress.com/2014/11/30/powershell-script-to-transpose-rows-into-column .ICONURI .EXTERNALMODULEDEPENDENCIES .REQUIREDSCRIPTS .EXTERNALSCRIPTDEPENDENCIES .RELEASENOTES https://gallery.technet.microsoft.com/scriptcenter/Powershell-Script-to-7c8368be .PRIVATEDATA #> <# .DESCRIPTION Script to transpose input data converting Server/Property/Value columns into rows where each server is a column and each property is a row. Script by Sam Boutros v0.1 - 11/30/2014 - Original release on the Microsoft Script Center Repository https://gallery.technet.microsoft.com/scriptcenter/Powershell-Script-to-7c8368be v0.2 - 11 October 2021 - rewrite, publish in the PowerShell Gallery For more information see https://superwidgets.wordpress.com/2014/11/30/powershell-script-to-transpose-rows-into-column #> #region Input [CmdletBinding(ConfirmImpact='Low')] Param( [Parameter(Mandatory=$false)]$SourceCSVFile = '.\table1.csv', [Parameter(Mandatory=$false)]$TransposeBy = 'Property', [Parameter(Mandatory=$false)]$GroupBy = 'Server' ) #endregion function Write-Log { <# .SYNOPSIS Function to log input string to file and display it to screen .DESCRIPTION Function to log input string to file and display it to screen. Log entries in the log file are time stamped. Function allows for displaying text to screen in different colors. .PARAMETER String The string to be displayed to the screen and saved to the log file .PARAMETER Color The color in which to display the input string on the screen Default is White 16 valid options for [System.ConsoleColor] type are Black Blue Cyan DarkBlue DarkCyan DarkGray DarkGreen DarkMagenta DarkRed DarkYellow Gray Green Magenta Red White Yellow .PARAMETER LogFile Path to the file where the input string should be saved. Example: c:\log.txt If absent, the input string will be displayed to the screen only and not saved to log file .EXAMPLE Write-Log -String "Hello World" -Color Yellow -LogFile c:\log.txt This example displays the "Hello World" string to the console in yellow, and adds it as a new line to the file c:\log.txt If c:\log.txt does not exist it will be created. Log entries in the log file are time stamped. Sample output: 2014.08.06 06:52:17 AM: Hello World .EXAMPLE Write-Log "$((Get-Location).Path)" Cyan This example displays current path in Cyan, and does not log the displayed text to log file. .EXAMPLE "$((Get-Process | select -First 1).name) process ID is $((Get-Process | select -First 1).id)" | Write-Log -color DarkYellow Sample output of this example: "MDM process ID is 4492" in dark yellow .EXAMPLE Write-Log 'Found',(Get-ChildItem -Path .\ -File).Count,'files in folder',(Get-Item .\).FullName Green,Yellow,Green,Cyan .\mylog.txt Sample output will look like: Found 520 files in folder D:\Sandbox - and will have the listed foreground colors .EXAMPLE Write-Log (Get-Volume | sort DriveLetter | Out-String).Trim() Cyan .\mylog.txt Sample output will look like (in Cyan, and will also be written to .\mylog.txt): DriveLetter FriendlyName FileSystemType DriveType HealthStatus OperationalStatus SizeRemaining Size ----------- ------------ -------------- --------- ------------ ----------------- ------------- ---- Recovery NTFS Fixed Healthy OK 101.98 MB 450 MB C NTFS Fixed Healthy OK 7.23 GB 39.45 GB D Unknown CD-ROM Healthy Unknown 0 B 0 B E Data NTFS Fixed Healthy OK 26.13 GB 49.87 GB .LINK https://superwidgets.wordpress.com/2014/12/01/powershell-script-function-to-display-text-to-the-console-in-several-colors-and-save-it-to-log-with-timedate-stamp/ .NOTES Function by Sam Boutros v1.0 - 6 August 2014 v1.1 - 1 December 2014 - added multi-color display in the same line v1.2 - 8 August 2016 - updated date time stamp format, protect against bad LogFile name v1.3 - 22 September 2017 - Re-write: Error handling for no -String parameter, bad color(s), and bad -LogFile without errors Add Verbose messages v1.4 - 27 March 2020 - Update to skip writing to file if LogFile parameter is not provided v1.5 - 15 May 2020 - Update to fix bug related to colors (thanks Stephen) #> [CmdletBinding(ConfirmImpact='Low')] Param( [Parameter(Mandatory=$false, ValueFromPipeLine=$true, ValueFromPipeLineByPropertyName=$true, Position=0)] [String[]]$String, [Parameter(Mandatory=$false,Position=1)][String[]]$Color, [Parameter(Mandatory=$false,Position=2)][String]$LogFile, [Parameter(Mandatory=$false,Position=3)][Switch]$NoNewLine ) if ($String) { #region Write to Console $i=0 foreach ($item in $String) { try { Write-Host "$item " -ForegroundColor $Color[$i] -NoNewline -EA 1 } catch { Write-Host "$item " -NoNewline } $i++ } if (-not $NoNewLine) { Write-Host ' ' } #endregion #region Write to file if ($LogFile) { try { "$(Get-Date -format 'dd MMMM yyyy hh:mm:ss tt'): $($String -join ' ')" | Out-File -Filepath $Logfile -Append -ErrorAction Stop } catch { Write-Warning "Write-Log: Bad LogFile name ($LogFile). Will not save input string(s) to log file.." } } else { Write-Verbose 'Write-Log: Missing -LogFile parameter. Will not save input string(s) to log file..' } #endregion } else { Write-Verbose 'Write-Log: Missing -String parameter - nothing to write or log..' } } #region Read input if (Test-Path $SourceCSVFile) { try { $InputTable = Import-Csv $SourceCSVFile -EA 1 $ColumnList = ($InputTable | Get-Member -MemberType NoteProperty).Name if ($TransposeBy -notin $ColumnList) { Write-Log 'Transpose Error: TransposeBy column',$TransposeBy,'not found in input file',$SourceCSVFile Magenta,Yellow,Magenta,Yellow; break } if ($GroupBy -notin $ColumnList) { Write-Log 'Transpose Error: GroupBy column',$GroupBy,'not found in input file',$SourceCSVFile Magenta,Yellow,Magenta,Yellow; break } } catch { Write-Log 'Transpose Error: unable to read input file',$SourceCSVFile Magenta,Yellow Write-Log $_.Exception.Message break } } else { Write-Log 'Input file',$SourceCSVFile,'not found, building sample file' Magenta,Yellow,Green $SourceCSVFile = '.\table1.csv' $TransposeBy = 'Property' $GroupBy = 'Server' @' Server,Property,Value Srv1,Memory,4GB Srv1,Processes,135 Srv1,Storage,120GB Srv2,Memory,8GB Srv2,Processes,234 Srv2,Storage,120GB Srv3,Memory,16GB Srv3,Processes,239 Srv3,Storage,160GB Srv4,Memory,10GB Srv4,Processes,219 Srv4,Storage,100GB '@ | Out-File $SourceCSVFile -Force } $InputTable = Import-Csv $SourceCSVFile $ColumnList = ($InputTable | Get-Member -MemberType NoteProperty).Name Write-Log 'Read',$InputTable.Count,'rows from input table from',$SourceCSVFile,'- columns:',($ColumnList -join ', ') Green,Cyan,Green,Cyan,Green,Cyan #endregion #region Transpose # Value = intersection of $TransPoseBy X $GroupBy $OutputTable = foreach ($TransposeByItem in $InputTable.$TransposeBy | Select -Unique) { $Props = [ordered]@{ $TransposeBy = $TransposeByItem } foreach ($GroupByItem in $InputTable.$GroupBy | Select -Unique){ $Value = ($InputTable.where({ $_.$GroupBy -eq $GroupByItem -and $_.$TransposeBy -eq $TransposeByItem })).Value $Props += @{ $GroupByItem = $Value } } New-Object -TypeName PSObject -Property $Props } Write-Log 'Transposed',"$($ColumnList.Count)/$($InputTable.Count)",'columns/rows into',"$(($OutputTable | Get-Member -MemberType NoteProperty).Count)/$($OutputTable.Count)",'columns/rows' Green,Cyan,Green,Cyan,Green $OutputTable #endregion |