Virtual Geek

  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