Menu

Virtual Geek

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

POWERSHELL CONVERT EXCEL TO DSC (DESIRED STATE CONFIGURATION) CONFIGURATION HASHTABLE FORMAT

July 2, 2017 10:09PM

I use Powershell DSC (Desired State Configuration) a lot for windows servers configuration, And I make use of DSC hashtable (configurationdata) frequently. Here is Microsoft fantastic article guide for writing DSC hashtable. But while adding multiple node I find sometimes it takes time to adding extra configuration, specifically comma "s, parentheses, braces, equal sign,  (Tabs for in better readable format) need to have sometime special care and one has to spend more time typing them. I wanted a best automated way to write the hashtable format, so it can be easily exported in below dsc data schema, and it will have few or less errors in it.

$MyData = 
@{
    AllNodes = @()
    NonNodeData = ""   
}

Useful Blog related to DSC
POWERSHELL PS REMOTING BETWEEN STANDALONE WORKGROUP COMPUTERS
DSC (DESIRED STATE CONFIGURATION) : DEPLOYING ACTIVE DIRECTORY
PART 1 : POWERSHELL - CREATE LOCAL IIS WEB REPOSITORY FOR DSC MODULE
POWERSHELL: USE PARAMETERS AND CONFIGURATION FROM INI FILE, USE AS SPLATTING
POWERSHELL CREATE HASHTABLE FROM MICROSOFT EXCEL

Most of the time I gather information in Microsoft Excel or my colleagues provide information in same format to me, so I decided to utilize Excel for this task, writing configuration to multiple tabs is easy, I can easily use them and convert to AllNodes automatic hashtable DSC variable, also NonNodedata can also can be mentioned as tab. Below is the strict Excel file format need to follow, Create AllNodes (Name should be same) tab names must contain AllNodes suffixed with . (dot) and numberings, NonNodeData must be as a tab with same name even though it is blank.

Powershell Convert Microsoft Excel to Dsc desired State Configuration Excel file format with tabs

Below script I am running as .ps1 file, make sure you can execute ps1 scripts on your computer, Set the execution policy with Set-ExecutionPolicy RemoteSigned.

  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
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
<#  
  .Synopsis  
   Converts Excel to DSC Configuration Data HashTable format text file.
  .Description  
   This script parses Microsoft Excel file data and coverts to ready to use Powershell DSC (Desired State Configuration) configuration Hashtable format text file. It requires Microsoft Excel to be installed from where you are running this script. Once Excel file is proecess it opens notepad with result, Notpad data can be copied and modified easily.
  .Example  
   ConvertTo-DSCHashTable -ExcelFile C:\temp\Test.xlsx
     
   It parses Microsoft Excel file and create DSC configuration text file, Notpad will automatically open.
  .Example
   Microsoft Excel file format and example, AllNodes data must 
   ----------------------                -----------------------------------   ---------------------------------
   | NodeName | LogPath |                | NodeName | Role      | SiteName |   | ConfigFileContents            | 
   |--------------------|                |----------------------------------   |-------------------------------|
   | *        | C:\Logs |                | VM1      | WebServer | Website1 |   |(Get-Content C:\Temp\Conf.xml) |

   ------------------------------------  ------------------------------------  ------------------------------------
   |*AllNodes.0|AllNodes.1|NonNodeData|  |AllNodes.0|*AllNodes.1|NonNodeData|  |AllNodes.0|AllNodes.1|*NonNodeData| <------ Tabs
   ------------------------------------  ------------------------------------  ------------------------------------
  .OutPuts  
    ConvertTo-DSCHashTable -ExcelFile C:\temp\Test.xlsx
    
    $ConfigurationData = @{
        AllNodes = @(
            @{
                LogPath='C:\Logs'; 
                NodeName='*'
            },

            @{
                Nodename='VM-1'; 
                Role='WebServer'; 
                SiteName='Website1'
            },
        );
        
        NonNodeData = @{		
		    ConfigFileContents=(Get-Content C:\Temp\conf.xml)
	    }
    }

  .Notes  
   NAME: ConvertTo-DSCHashTable
   AUTHOR: Kunal Udapi
   CREATIONDATE: 12 February 2017
   LASTEDIT: 2 July 2017  
   KEYWORDS: Convert excel tab to DSC configuration hashtable  
  .Link  
   #Check Online version: http://kunaludapi.blogspot.com
   #Check Online version: http://vcloud-lab.com
   #Referances: https://msdn.microsoft.com/en-us/powershell/dsc/configdata
   #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
)
$ConfigurationData = @()
#Region  (Parse Excel)
#$File = 'C:\temp\test.xlsx'
$TempFolder = [System.IO.Path]::GetTempPath()
$Guid = [System.Guid]::NewGuid()
$TempDir = Join-Path -Path $TempFolder -ChildPath $Guid.Guid
New-Item -Path $TempDir -ItemType Directory | Out-Null
$excelObj =  New-Object -ComObject Excel.Application
$excelObj.Visible = $false
$excelObj.DisplayAlerts = $false
$WorkBook = $excelObj.Workbooks.Open($File)
$WorkSheets = $WorkBook.Worksheets
Foreach ($WorkSheet in $WorkSheets) {
    $CSVFileName = $WorkSheet.Name
    $CSVFile = Join-Path -Path $TempDir -ChildPath $CSVFileName
    $WorkSheet.SaveAs($CSVFile, 6)
}
$excelObj.Quit()
#Endregion

#Region  (AllNodes)
$AllNodesCSV = Get-ChildItem -Path $TempDir -Filter AllNodes.* | Select-Object -ExpandProperty FullName
$CompleteAllNode =@()
Foreach ($AllNodeRow in $AllNodesCSV) {
    $RowHash = @()
    $ALLNodeCSVRow = Import-Csv $AllNodeRow | Where-Object { ($_.PSObject.Properties | ForEach-Object {$_.Value}) -ne $null}
    $Header = $ALLNodeCSVRow | Get-Member -MemberType NoteProperty | Select-Object -ExpandProperty Name
    foreach ($head in $header) {
        $HashValue = ($ALLNodeCSVRow.$Head | Out-String).Trim()
        $RowHash += "`t`t`t{0}=`"{1}`"" -f $Head, $HashValue
        #"`"$Head`"=`"$($ALLNodeCSVRow.$Head)`""
    }
    $CompleteAllNode += "`t`t@{`n$($RowHash -Join "; `n")`n`t`t}"
}
$AllNodeInfo = $CompleteAllNode -join ",`n`n"
#Endregion

#Region  (NonNodeData)
$NonNodeDataCSV = Get-ChildItem -Path $TempDir -Filter NonNodeData.csv | Select-Object -ExpandProperty FullName
$NonNodeDataCSVRow = Import-Csv $NonNodeDataCSV | Where-Object { ($_.PSObject.Properties | ForEach-Object {$_.Value}) -ne $null}
$CompleteNonNode = @()
if ($NonNodeDataCSVRow -eq $null) {
    $CompleteNonNode = "`t`t`tNonNodeData = `"`""
}
else {
    $NonHeader = $NonNodeDataCSVRow | Get-Member -MemberType NoteProperty | Select-Object -ExpandProperty Name
    $NonNodeRowHeadHash = @()
    foreach ($Nonhead in $NonHeader) {
        $NonNodeHashValue = ($NonNodeDataCSVRow.$Nonhead | Out-String).Trim()
        $NonNodeRowHeadHash += "`t`t{0}=`"{1}`"" -f $NonHead, $NonNodeHashValue
    }
    $CompleteNonNode += "`t`t`n$($NonNodeRowHeadHash -Join "; `n")`n`t"
}
#Endregion
$TmpFileName = [System.IO.Path]::GetTempFileName()
$ConfigurationData = "`$ConfigurationData = @{`n`tAllNodes = @(`n$AllNodeInfo`n`t); `n`n`tNonNodeData = @{$CompleteNonNode}`n}"
Set-Content -Path $TmpFileName -Value $($ConfigurationData -replace "`n", "`r`n")
Notepad $TmpFileName
#$ConfigurationData | clip.exe
$ConfigurationData

There is only one parameter named File or ExcelFile, is the path to Excel and it is mandetory. Once it is executed it opens notepad with the required formatted data, here on notepad  (Powershell ISE, Visual Studio Code Copy past data) I can again verify the data if there is any mistake, and for minor changes information can be modified straight in the readymade text formatted notepad or choice of editor, instead running script again, Also same information is shown on the console as well, for future use save notepad as it is opened as temporary file.

Powershell Convert Microsoft Excel to Dsc desired State Configuration Excel file format with tabs auto format hashtable format nodename, Role, NonNodeData, AllNodes, Automatic Variable, confdata, configurationdata

Incase you require this hashtable in the variable first load the data in variable, and run Invoke-Expression. 

$ConfigurationData = C:\Temp\ConvertTo-DSCHashTable.ps1 -ExcelFile C:\Temp\Test.xlsx
Invoke-Expression $ConfigurationData

All the name and value information within hashtable is stored into variable $ConfigurationData and ready to use in Desired State Configuration

Powershell Convert Microsoft Excel to Dsc desired State Configuration ConvertTo-DSCHashTable, Invoke-Expression, hashtable format nodename, Role, NonNodeData, AllNodes, Automatic Variable, confdata, configurationdata

Go Back

Comment