m8ITAdminBox

2.0.0

AdminBoxData.psm1

function Get-OleDbConnection {   
    [CmdletBinding()]
    param (
        [Parameter(Position=0, Mandatory=$true, ValueFromPipeline=$true)]
        [ValidateNotNullOrEmpty()]
        [string]$ConnectionString
    )

    $connection = New-Object System.Data.OleDb.OleDbConnection($ConnectionString)
    $connection.Open()
    $connection

}

function Get-AceConnectionString {

    [CmdletBinding()]
    param (
        [Parameter(Position=0, Mandatory=$true, ValueFromPipeline=$true)]
        [ValidateNotNullOrEmpty()]
        [string]$Path,

        [Parameter(Mandatory=$false)]
        [switch]$NoHeader=$false
    )

    $hdr = if($NoHeader) {'NO'} else {'YES'}

    switch -regex ($Path)
    {
        #'\.xls$|\.xlsx$|\.xlsb$' {"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=`"$Path`";Extended Properties=`"Excel 12.0 Xml;HDR=YES`";"}
        '\.xls$|\.xlsx$|\.xlsb$' {"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=`"{0}`";Extended Properties=`"Excel 12.0 Xml;HDR={1}`";" -f $Path, $hdr}
        '\.mdb$|\.accdb$'        {"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=`"$Path`";Persist Security Info=False;"}
    }

}

function Get-AceDataTable {
    
    [CmdletBinding()]
    param (
        [Parameter(Position=0, Mandatory=$true, ValueFromPipeline=$true)]
        [ValidateScript({$_ -match  '\.xls$|\.xlsx$|\.xlsb$|\.mdb$|\.accdb$'})]
        [string]$Path,
        [Parameter(Position=1, Mandatory=$true)]
        [string[]]$Table,
        [Parameter(Position=2, Mandatory=$false)]
        [switch]$NoHeader=$false
    )

    $connection = Get-OleDbConnection -ConnectionString $(Get-AceConnectionString -Path $Path -NoHeader:$NoHeader)
    
    $dataAdapter = new-object System.Data.OleDb.OleDbDataAdapter
    $dataSet = New-Object system.Data.DataSet
    $command = new-object System.Data.OleDb.OleDbCommand
    $command.Connection = $connection

    $Table | foreach { 
            $commandText = "SELECT * FROM [{0}]" -f $_;
            $command.CommandText = $commandText;
            $dataAdapter.SelectCommand = $command;
            $dataTable = New-Object System.Data.DataTable("$_");
            $null = $dataAdapter.Fill($dataTable);
            $dataSet.Tables.Add($dataTable)
    }

    $connection.Close();
    Write-Output $dataSet
}