ACE-DS - A reusable Data Source for acquiring from Excel spreadsheets

[korns]

This ACE Data Source has been superceded by the EDM-Pack referenced here:

http://community.netapp.com/t5/OnCommand-Storage-Management-Software-Discussions/EDM-Pack-Excel-Data-Management-for-WFA/td-p/113416

=====

The attached package was a two person project. I came up with the simple idea in version 1.0 of the tool. Mirko Van Colen took the concept and made it 5-times as powerful (adding relationships) which resulted in this version 2.0 of the package.

Most WFA workflows that truely integrate with a customer's specific storage environment and business rules need to access external data (sites, cost centers, naming conventions, etc). Previously you had to write a WFA Data Source in SQL or PowerShell to import the data into WFA's database. With the ACE Data Source (i.e. ACE-DS) you simply need to create an external Excel spreadsheet and the cooresponding WFA dictionary entries. The ACE-DS does the rest reading directly from the Excel .xlsx file into a new WFA scheme and tables.

The attached files include:

  - The .dar file containing the ACE Data Source and sample 'ace' scheme and tables (exported from a WFA 3.0P1 system)

  - The coorsponding sample Excel spreadshee: ace.xlsx

  - The User Guide covering installation, Excel-to-WFA mapping rules, and some examples.

Enjoy

NOTE: Attached is update to the .dar file only - Package Version Pv_2_0_1 which includes a fix for a problem if you have only one row in the excel and you work with ID’s. Docs and examples stay the same.

[korns]

And if you prefer PDF format

[sinhaa]

Hi David,

Nice post and very innovative solution.

You had a older post about using MS ACE Powershell module to acquire MS Excel data into WFA. Using Microsoft ACE for direct Excel .xlsx WFA Data-Sources

So as I understand this solution unlike the older one I just need to take care of the mapping my WFA dictionary items with the EXCEL columns. The older solution the DataSourec type code was also be be adjusted  according to my Excel columns. Is this correct? Is there any other advantage?

A varient request. I see that MS Access can also maps CSV and XML into WFA DB.  Can you have another version to take care of that too? The thing is MS Excel is not present on WFA server 2008, 2012 by default. CSV and XML are editable by any text editor and hence always there.

And glad to see DataSource Logger being used in your code. Hope its helping you.  

sinhaa

[korns]

Hi Sinhaa, 

Thanks for the feedback and good questions.

Regarding the older MS ACE PowerShell data source ...

• That really was just about how to use the MS ACE driver/provider to 'write your own' data source that could use ACE PowerShell cmdlets to read from the Excel file. That is still an option but it seems to me 90% of cases can be addressed with this newer 'already written' data source. Also: in both cases, whether you are using the raw ACE cmdlets or this ACE Data Source, you are responsible to setup your scheme/dictionary(s) that match up with the columns in your data.
• This v2.0 solution also handles possible ordering problems of the columns. WFA dictionaries have a habit of re-ordering the columns when you come back and edit a dictionary later ... and this could be a problem when 'write your own'. Mirko's enhancements include handling that situation smoothly and transparently for you.

Regarding MS Access, CSV and XML into WFA DB ...

• The reason we called this the 'ACE Data Source' instead of the 'Excel Data Source' is because the ACE driver/provider from Microsoft is meant to be a portal to many types of data, not just Excel. It is an OLE DB provider. Excel is just what we tested with since it's so commonly known/used. The actual toolkit is called "Microsoft Access Database Engine 2010" so it may (should?, will?) already work directly reading MS Access .mbd files, csv, ... and XML "may" be one of the choices (haven't researched that one but ... remember, .xlsx IS XML encoded)

Regarding Excel not being present/supported on Windows servers ...

• Exactly ... thats why/how this started. Even if you could install Office or Excel on a MS Server OS it would require a license, etc ... not usually an option. Who wants to RDP into a server to manipulate their data anyway? 
• In practice what folks do is define a folder on the WFA server and share it. Then they run Excel on their desktop/laptop to actually enter/edit the data.
• I'm not sure that the current ACE Data Source can't read it's input file over the network on another server/share (where IT might house config DB data) and read it that way. Again, something we haven't tested yet but the idea would be to enter a UNC path to the file into the Execution Data Source's 'Host name:' field.
• If you want to maintain simple CSVs with an editor, I believe the ACE Data Source will read those also ... but not tested
• FWIW: I use Mac OS X Excel and Save As... directly onto the WFA Server shared folder. ACE Data Source then eats it up. 

DataSource Logger ...

• This was also brought into this version by Mirko! and the logging is very good (explaining what it detects and is doing along the way)
• He also points out that this can be viewed via logviewer
• I'm lazy and like my log files emaied to me so there is a hidden $debug flag in the code you can set with smtp-server/email-address to send the logfile AND the interim CSV files that were passed to WFA after each run (useful while seting up you tables/dictionaries)

[sinhaa]

Thanks for giving time to explain it all. 

sinhaa