KB95782511: AE Connect - How to use an Excel document as database

Description

For testing, training or light implementations with external databases, it is tempting to use a format everybody knows: Excel spreadsheets. Although it is not designed as a database system, it can be used as one.

For advanced implementations, Esko advices to use a real database setup like Oracle or SQL.

This article explains how to configure and use XLSX spreadsheet as database using Automation Engine 14.0.  It does not explain how to create queries.

Why not to use Excel as database

Esko does not promote the use of Excel as database format because of these limitations:

• ODBC driver does not support deleting records.
• XLSX file can not be opened or accessed by anyone else, or another process.
• XLSX file has to be installed locally on Automation Engine master server.
• ODBC connection is not released together with Interact with Database Task. There is a settling time during which the XLSX is locked for editing by Automation Engine and the values are not yet changed, so opening the file as read-only is possible but will not show any changes.

Procedure

Create XLSX file

1. Create a database in MS Excel. There are no records yet but for the sake of testing, please do add some dummy values to each field.
   The first row will be used as header, so here you can enter the name of the field you have chosen. The name of the tabsheet is used as the name of the table. So make it meaningful, not sheet1. You can define multiple tabsheets, but you can not use multiple XLSX files. 
2. Save this file on the Automation Engine Server. It does not need to be in a container. The user running Automation Engine services should have full access.

ODBC configuration

1. Download and install the Microsoft Access Driver from Microsoft Access Database Engine 2010 Redistributable. Make sure to download the version matching your operating system: 32 or 64 bit. It is called Access Driver but contains the drivers for all Office connection.

   ODBC driver 32 or 64 bit

   On a 64 bit machine, the Interact with Database Task and Upload Points can only work with 64 bit ODBC Data Sources. This is different compared to the 32-bit ODBC Query SmartName that required the 32 bit driver also on 64 bit machines.

   64 bit ODBC and 32 bit Office

   The installation of 64 bit version requires that the 32-bit version of the Microsoft Access Driver must be uninstalled, as well as any 32 bit MS Office programs like Excel or Word. Uninstall it first using the Windows Add/Remove Programs in the Control Panel.

    

   1. Open the Windows ODBC Data Source Administrator tool.

   2. Select the System DSN tab and click Add.
      

2. If you do not have the Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb) in the list, you did something wrong in step 1.

3. Select the Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb) and click Next.
    

4. Specify the name of the Data Source and select the workbook (.xlsx file).
   

   ODBC Data Source Administrator crashing

   If the ODBC Data Source Administrator tool is crashing when you try to select a file and you're running on a virtual machine: "Turn Shared Folders Off!" This problem occurs with VMWare Fusion on Mac.
   You can turn it back on after the ODBC is configured.

5. In Options, deactivate the Read only option.
   

Configuration in Automation Engine

1. The databases are setup in the Configure panel (Pilot menu > Tools > Configure > External Databases): In the Configure panel, click External Database > click New in the File menu (or click Insert) to add a database.
   
2. In the DBMS Type drop-down menu, choose ODBC Source and fill in the name of the data source you've chosen in ODBC configuration step 4.
3. Save.

Database SmartNames

You can create SmartNames to perform SELECT queries. The result will only be one string value. If your command results in multiple values, the SmartName will only return the first value.

1. Open Automation Engine Pilot, SmartNames view.
2. Create a new SmartName type Database Query.
3. Select your configured database from the drop down list. 
4. Enter your SQL query. You should see the result at the bottom.

Interact with Database Task

Using the Interact with Database Task you can perform SELECT queries. These will output a XML file with the returned values. If multiple records are returned, they will be different nodes in the XML file. To continue your workflow, use the XML compatible tasks or XPath SmartNames.

With this task you can also perform UPDATE, INSERT INTO, etc.. queries. So, you can actually alter the information in the database. The output from the task are the input files. The options in this task are not explained here. If you do not find them self-explaining enough, please refer to the manual.