After writing my last blog on using splatting from INI file, where it has format of Key and Value concatenated with equal = sign. It can be executed easily but sometimes writing INI file can be little messy if there is slight mistake, Still I find INI is best for writing parameter and syntax due to its simplicity, If you want run same configuration against multiple entity like users or computers, Need to write extra coding or add another blocks to INI. For Normal basic use cases INI is always best.
POWERSHELL: USE PARAMETERS AND CONFIGURATION FROM INI FILE, USE AS SPLATTING
This is why I wrote my another script for parsing Excel file, Same Key and Value can be added to Excel file, and it can have multiple rows. Only requirement is Excel should be installed on the machine where this script will run. (There are modules and scripts available where Excel can be manipulated or extract information without installing it using PowerShell).
Below is the my Excel format looks like, in the bottom I have Tab with configuration names.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 |
<# .Synopsis Excel to Powershell Hashtable. .Description This script converts Selected Excel data to Powershell Hashtable. It requires Microsoft Excel to be installed from where you are running this script. .Example Get-ExcelHashtable -ExcelFile c:\temp\Test.xlsx -Tab Service It parses Excel file and for selected tab create hashtable with same name. .Example Excel file format and example -------------------------------- | Name | ComputerName | | ------------------------------ | LanManServer | Localhost | | WorkStation | 127.0.0.1 | -------------------------------- |Service| <---------------------------Excel Tabs --------- .OutPuts Get-ExcelHashtable -ExcelFile c:\temp\Test.xlsx -Tab Service $service | Foreach-Object {$syntex = $_; Get-Service @syntex} Status Name DisplayName ------ ---- ----------- Stopped LanManServer Server Running LanmanWorkstation Workstation .Notes NAME: Get-ExcelHashtable AUTHOR: Kunal Udapi CREATIONDATE: 01 July 2017 LASTEDIT: 1 July 2017 KEYWORDS: Export excel tab to hashtable .Link #Check Online version: http://kunaludapi.blogspot.com #Check Online version: http://vcloud-lab.com #Requires -Version 3.0 #> [CmdletBinding(SupportsShouldProcess=$True,ConfirmImpact='Medium')] param( [Parameter( Position=0, Mandatory=$true, ValueFromPipeline=$true, HelpMessage='Type the full path of Excel file' )] [alias('ExcelFile')] [ValidateScript({ If (Test-Path $_) { $true } else{ "Invalid path given: $_" } })] [System.String]$File, [Parameter(Position=1, Mandatory=$true)] [alias('Configuration')] [System.String]$Conf ) #$Path = 'C:\temp\test.xlsx' #$Conf = 'Service' $TabName = [System.IO.Path]::GetTempFileName() $excelObj = New-Object -ComObject Excel.Application $excelObj.Visible = $false $excelObj.DisplayAlerts = $false $WorkBook = $excelObj.Workbooks.Open($File) $WorkSheet = $WorkBook.Worksheets | Where-Object {$_.Name -eq $Conf} $WorkSheet.SaveAs($TabName, 6) $excelObj.Quit() $TabCSV = Import-Csv $TabName $Header = $TabCSV | Get-Member -MemberType NoteProperty | Select-Object -ExpandProperty Name $CompleteHash =@() $Information = @() foreach ($row in $TabCSV) { $RowHash = @() foreach ($head in $header) { $RowHash += "`"$Head`"=`"$($row.$Head)`"" } $CompleteHash += "@{$($RowHash -Join '; ')}" } $Information = $CompleteHash -join ', ' New-Variable -Scope Global -Name $Conf -Value (Invoke-Expression $Information) -Force |
Copy Paste above script in PS1, Make sure you can execute PS1 script, it can be modified using Set-Executionpolicy remotesigned. Next once below command run it will be asking for excel file path and Configuration tab name. When running information it doesn't require much coding. It creates same configuration or Tab named variable, In my case $service, I am using foreach block with $psitem as splatting.
