Using Microsoft ACE for direct Excel .xlsx WFA Data-Sources

[ Edited ]
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:
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.
# ====================  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


Re: Using Microsoft ACE for direct Excel .xlxs WFA Data-Sources

Good post.

If this post resolved your issue, help others by selecting ACCEPT AS SOLUTION or adding a KUDO.