Menu

Virtual Geek

Tales from real IT system administrators world and non-production environment

POWERSHELL CREATE HASHTABLE FROM MICROSOFT EXCEL

July 1, 2017 08:09PM

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.

Powershell excel to hashtable, service restart from hashtable

Go Back

Comment