Run SQL against Excel and Access through Access Database Engine with PowerShell – Part 1

tree
This is part 1 of a series of blog posts around using SQL against Excel and Access through ACE drivers (Access database engine) with PowerShell.
Part 1 is about introduction and getting up and running.

PowerShell has already some built-in support to slice and dice data via the select, where, and group cmdlets. If it comes to bigger data-sets and join operations though, it lacks performance and features.
Many of us (including me) have data sitting in either Excel or Access files rather than SQL server or similar more scalable DBMSs.
In case of Excel converting the file to .csv and subsequently pulling the data in using Import-CSV is of course a valid option for several use cases. On the other hand there is another (better) way that doesn’t require any conversion and enables reading and writing the data directly into Excel.
First we’ll need to download and install the Microsoft Access Database Engine 2010 Redistributable. The name is a bit misleading as this is a replacement of the older Jet and OLEDB drivers which can be used for older and newer Excel and Access files. The download offers a 32 and a 64 bit version where the bit size corresponds to the respective bit size of the used office version. In order to avoid the nitty gritty details and focus on the task I have created a module that is a modified and extended version of the ACE module Chad Miller created. Let’s download the module to into the user’s module path.

$folderPath = Join-Path -Path ([Environment]::GetFolderPath('MyDocuments')) -ChildPath WindowsPowerShell\Modules\ACE
mkdir $folderPath
$url = 'https://raw.githubusercontent.com/DBremen/PowerShellScripts/master/functions/ACE.psm1'
Invoke-WebRequest $url -OutFile "$folderPath\ACE.psm1"

This is everything needed to get started. See you hopefully in the next part to dive into some example queries.

shareThoughts


Photo Credit: Fountain_Head via Compfight cc

Advertisements

I'd love to hear what you think

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s