# encoding: ascii
# api: powershell
# title: ACE.psm1
# description: ACE.psm1 is a module for getting data from Microsoft Office Access (*.mdb and .accdb) files and Microsoft Office Excel (.xls, *.xlsx, and *.xlsb) files.
# version: 1.0
# type: function
# author: Chad Miller
# license: CC0
# function: Get-ACEConnection
# x-poshcode-id: 2630
# x-archived: 2012-12-29T04:26:17
# x-published: 2012-04-23T18:30:00
#
#
#######################
function Get-ACEConnection
{
param($ConnectionString)
$conn = new-object System.Data.OleDb.OleDbConnection($ConnectionString)
$conn.open()
$conn
} #Get-ACEConnection
#######################
function Get-ACETable
{
param($Connection)
$Connection.GetOleDbSchemaTable([System.Data.OleDb.OleDbSchemaGuid]::tables,$null)
} #Get-ACETable
#######################
function Get-ACEConnectionString
{
param($FilePath)
switch -regex ($FilePath)
{
'\.xls$|\.xlsx$|\.xlsb$' {"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=`"$filepath`";Extended Properties=`"Excel 12.0 Xml;HDR=YES`";"}
'\.mdb$|\.accdb$' {"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=`"$filepath`";Persist Security Info=False;"}
}
} #Get-ACEConnectionString
#######################
<#
.SYNOPSIS
Queries Excel and Access files.
.DESCRIPTION
Get-ACEData gets data from Microsoft Office Access (*.mdb and *.accdb) files and Microsoft Office Excel (*.xls, *.xlsx, and *.xlsb) files
.INPUTS
None
You cannot pipe objects to Get-ACEData
.OUTPUTS
System.Data.DataSet
.EXAMPLE
Get-ACEData -FilePath ./budget.xlsx -WorkSheet 'FY2010$','FY2011$'
This example gets data for the worksheets FY2010 and FY2011 from the Excel file
.EXAMPLE
Get-ACEData - -FilePath ./budget.xlsx -WorksheetListOnly
This example list the Worksheets for the Excel file
.EXAMPLE
Get-ACEData -FilePath ./projects.xls -Query 'Select * FROM [Sheet1$]'
This example gets data using a query from the Excel file
.NOTES
Imporant!!!
Install ACE 12/26/2010 or higher version from LINK below
If using an x64 host install x64 version and use x64 PowerShell
Version History
v1.0 - Chad Miller - 4/21/2011 - Initial release
.LINK
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=c06b8369-60dd-4b64-a44b-84b371ede16d&displaylang=en
#>
function Get-ACEData
{
[CmdletBinding()]
param(
[Parameter(Position=0, Mandatory=$true)]
[ValidateScript({$_ -match '\.xls$|\.xlsx$|\.xlsb$|\.mdb$|\.accdb$'})] [string]$FilePath,
[Parameter(Position=1, Mandatory=$false)]
[alias("Worksheet")] [string[]]$Table,
[Parameter(Position=2, Mandatory=$false)] [string]$Query,
[Parameter(Mandatory=$false)]
[alias("WorksheetListOnly")] [switch]$TableListOnly
)
$FilePath = $(resolve-path $FilePath).path
$conn = Get-ACEConnection -ConnectionString $(Get-ACEConnectionString $FilePath)
#If TableListOnly switch specified list tables/worksheets then exit
if ($TableListOnly)
{
Get-ACETable -Connection $conn
$conn.Close()
}
#Else tablelistonly switch not specified
else
{
$ds = New-Object system.Data.DataSet
$cmd = new-object System.Data.OleDb.OleDbCommand
$cmd.Connection = $conn
$da = new-object System.Data.OleDb.OleDbDataAdapter
if ($Query)
{
$qry = $Query
$cmd.CommandText = $qry
$da.SelectCommand = $cmd
$dt = new-object System.Data.dataTable
$null = $da.fill($dt)
$ds.Tables.Add($dt)
}
#Return one or more specified tables/worksheets
elseif ($Table)
{
$Table |
foreach{ $qry = "select * from [{0}]" -f $_;
$cmd.CommandText = $qry;
$da.SelectCommand = $cmd;
$dt = new-object System.Data.dataTable("$_");
$null = $da.fill($dt);
$ds.Tables.Add($dt)}
}
#Return all tables/worksheets
else
{
Get-ACETable $conn |
where {$_.TABLE_TYPE -eq 'TABLE' } |
foreach{ $qry = "select * from [{0}]" -f $_.TABLE_NAME;
$cmd.CommandText = $qry;
$da.SelectCommand = $cmd;
$dt = new-object System.Data.dataTable("$($_.TABLE_NAME)");
$null = $da.fill($dt);
$ds.Tables.Add($dt)}
}
$conn.Close()
Write-Output ($ds)
}
} #Get-ACEData
Export-ModuleMember -function Get-ACEData