Excel Import/Export

The Excel task (excel) exchanges data between a database and excel. There are two operating modes:

• export: data extracted by a SQL query (SELECT) is exported to an Excel datasheet;
• import: data read from an Excel datasheet is imported to a database acording to a SQL DML statement (INSERT, UPDATE, DELETE).

The following elements configure the task:

Element	Purpose
sheet	Defines the source or target sheet. Refer to Sheet Element for detailed description.

The following attributes configure the task:

Attribute	Type	Purpose
db	string	Defines the database to import/export data to/from
file	string	Full name of the Excel file to import/export data to/from
mode	string	Sets the operating mode. Refer to Operating Modes for detailed description.

Operating Modes

The following operating modes are supported:

Value	Description
import	Data is read from Excel and stored in the table.
export	Data is obtained from a SQL query and stored in Excel

Sheet Element

Each import/export operation is related to a sheet element. This element defines the source/target sheet (as per attribute name) and the corresponding source/target SQL command, specified in the element value:

• in export mode, the SQL command must be a valid query
• in import mode, the SQL command must be a valid DML command where data from excel gets interpolated by speifying the Excel column identifier (A, B, C, etc.) as ${column_id}.

The following attributes configure the task:

Attribute	Type	Purpose
name	string	Sheet name

Example

<job name="ZZTEST">
  <excel name="XLS01" db="QS" file="C:\tmp\export.xlsx" mode="export">
    <sheet name="XLS01">select * from xls01</sheet>
    <sheet name="TEST01">select * from test01</sheet>
  </excel>
  
  <excel name="XLS02" db="QS" file="C:\tmp\export.xlsx" mode="import">
    <sheet name="XLS01">insert into xls02 values (${A}, ${B}, ${B})</sheet>
  </excel>
</job>

Status codes

Status	Status code	Description
Ready	-1	Task is initialized, but not yet started
Running	-2	Task is running
Success	0	Task completed successfully
Warnings	1	Task completed with warnings
Errors	2	Task completed with errors
Aborted	9	Task cannot be executed