Functions/Convert-UIMtoCSV.ps1
|
function Convert-UIMtoCSV { [CmdletBinding()] param ( [Parameter(Mandatory=$true)][string]$UIMFile ,[Parameter(Mandatory=$true)][string]$OutputFolder ,[Parameter(Mandatory=$true)][string]$Custcode ) $ExcelSheets = @( @{ "SheetName" = "Functionele Groepen" "CSVFile" = "$($Custcode)_UIM_FNC.csv" } @{ "SheetName" = "Accounts" "CSVFile" = "$($Custcode)_UIM_Accounts.csv" } @{ "SheetName" = "Shared Mailboxen" "CSVFile" = "$($Custcode)_UIM_SharedMailbox.csv" } @{ "SheetName" = "Groepsschijf" "CSVFile" = "$($Custcode)_UIM_NTFS.csv" } @{ "SheetName" = "Applicaties" "CSVFile" = "$($Custcode)_UIM_Apps.csv" } ) if(!(Test-Path $UIMFile)) { Write-Error "UIMFile bestaat niet" break } if(!(Test-Path $OutputFolder)) { mkdir $OutputFolder | Out-Null } foreach($ES in $ExcelSheets) { $ES.SheetName + " - " + $ES.CSVFile $OutputFile = Join-Path $OutputFolder $ES.CSVFile switch($ES.SheetName) { "Functionele Groepen" { Import-Excel $UIMFile -Sheet $ES.SheetName | Where-Object "Functionele Groepen" -NotLike "Functionele groep *" | Export-Csv -Path $OutputFile -Delimiter ";" -NoTypeInformation -Encoding UTF8 } "Accounts" { Import-Excel $UIMFile -Sheet $ES.SheetName | Where-Object Nieuwe_Inlognaam | Export-Csv -Path $OutputFile -Delimiter ";" -NoTypeInformation -Encoding UTF8 } "Groepsschijf" { $AantalKolommen = (Import-Excel $UIMFile -Sheet $ES.SheetName -EndColumn 2 | Where-Object ntfsg -NotLike "NTFS groep *").count $AantalKolommen = $AantalKolommen + 2 Import-Excel $UIMFile -Sheet $ES.SheetName -EndColumn $AantalKolommen | Where-Object fncg -NotLike "Functionele groep *" | Export-Csv -Path $OutputFile -Delimiter ";" -NoTypeInformation -Encoding UTF8 } "Applicaties" { $AantalKolommen = (Import-Excel $UIMFile -Sheet $ES.SheetName -EndColumn 2 | Where-Object appg -NotLike "Applicatie groep *").count $AantalKolommen = $AantalKolommen + 2 Import-Excel $UIMFile -Sheet $ES.SheetName -EndColumn $AantalKolommen | Where-Object fncg -NotLike "Functionele groep *" | Export-Csv -Path $OutputFile -Delimiter ";" -NoTypeInformation -Encoding UTF8 } } } } |