NOTE: while the below concepts are still valid, please be aware a more general-purpose solution for importing Excel data is provided by the EDM-Pack described here:
=====
A common part of many WFA workflow automation efforts is integrating customer specific data into the WFA Cache database. Examples include department or client codes, data center locations or any kind of data that is necessary to make decisions in the storage automation process. WFA supports this kind of external data and via custom dictionaries and WFA Cache tables where that data can be regularly imported and available for use in WFA User Input forms and command resource-selection. WFA supports two types of data-sources:
- SQL - connections to external databases along with SQL code (Cache Queries) that select and pull data into WFA tables and
- SCRIPT - based data-sources, written in PowerShell, that collect data from an external source and convert it into a specially named and formatted CSV file which WFA then consumes into a WFA dictionary table after the data-source script runs.
Commonly available examples of PowerShell SCRIPT based data-sources read in simple text files (.txt, .csv) created from Excel spreadsheets using the Excel "Save As..." command to create a copy of a native Excel worksheet into a simple text file. This article documents a method to create a Data sources that instead imports directly from native Excel worksheets (.xlsx files). The method utilizes the free Microsoft Access Control Entry (ACE) driver/provider and a corresponding PowerShell cmdlet called Get-ACEData.
To obtain and install the Microsoft ACE driver/provider on your WFA server following these steps:
- Search the Microsoft site for the "Microsoft Access Data Engine 2010 Redistributable"
- On that page download the AccessDatabaseEngine_x64.exe to your WFA server
- Run the .exe on the WFA server which installs the ODBC provider onto the WFA Server
To obtain and install the Get-ACEData cmdlet follow these steps:
- Goto: http://blogs.technet.com/b/heyscriptingguy/archive/2011/05/04/use-ace-drivers-and-powershell-to-talk-to-access-and-excel.aspx
- Follow the instructions in the section titled "Installing the ACE Module"
- This process describes downloading and saving the ACE.psm1 PowerShell Module. You can:
- follow those instructions precisely to save it on your WFA server -or-
- insert the ACE.psm1 code at the start or your WFA Data Source
- Including the psm1 code into your data source simplifies sharing the WFA data source between WFA servers
- This process describes downloading and saving the ACE.psm1 PowerShell Module. You can:
Below is an data source that reads data directly from a Excel worksheet file named "depts.xlsx". The worksheet has a tab sheet named "tech" that contains 3 columns. The first row (row 1) of the tech table contains the column headings: Dept Code, Description, and Business Unit. These column headings become PowerShell object properties when importing with Get-ACEData cmdlet. Note that when column headings have spaces the PowerShell property names must be enclosed in quotes as seen below.
===== EXAMPLE WFA DATA SOURCE USING ACE AND GET-ACEDATA =====
# ==================== MAINLINE FOR DATA SOURCE ========================
# Import-Module ace # The Import-Module is not needed if ace.psm1 code is included above the mainline data source code
$inFile = "C:\ProgramData\OnCommand\WFA\UserInput\depts.xlsx"
try {
$inTable = Get-ACEData -FilePath $inFile
} catch [System.Exception] {
$msg = "Data Source: Could not read input table from file path: $($_.Exception)"
Get-WFALogger -message $msg -Error ; Throw $msg
}
$outFile = ".\depts.csv" # WFA dictionary "depts" must exist which defines each field
try {
New-Item -Path $outFile -type file -force | Out-Null
} catch [System.Exception] {
$msg = "Data Source: Could not create output file path: $($_.Exception)"
Get-WFALogger -message $msg -Error ; Throw $msg
}
foreach ( $row in $inTable.tables["tech$"] )
{
[string] $deptCode = $row."Dept Code"
[string] $decription = $row.description
[string] $businessUnit = $row."Business Unit"
write-host $("Dept Code: " + $deptCode + "`t Description: " + $description ) # for debug-only
$outLine = $($deptCode + "`t" + $description + "`t" + $businessUnit + "`n" )
Add-Content $outFile ( [byte[]][char[]] "\N`t$outLine") -Encoding Byte
}
===== COORESPONDING DICTIONARY DEFINTION =====
Public