Write to Excel

The "Write to Excel" module is controlled by an SQL query that the datasqill developer defines. The module reads data using this query from the source tables and writes it to an Excel file.

Name Meaning
Module Write to Excel
Module Class DsModWriteExcel
Type Java
Purpose Based on the specified SQL query, data is to be written to an Excel file
Transformation Code SQL Query
Sources Source tables in a database, Excel template file
Targets Excel File

Description

The following example shows how to build an export to flat file using the module.

First, define the SQL query:

SELECT c_custkey AS key
     , c_name
     , c_firstname
  FROM staging.customer

(We set the load_date variable in the header rows in a LoopQuery.)

Second, you must prepare the Excel template: write_excel_file_template

The highlighted variables have the following meaning:

Name Source Meaning Optional
${load_date} Loop Query The columns from the Loop Query can be used in Excel Y
${TemplateRow} - This variable specifies from which row the data from the query is inserted into the Excel N
${key}, ${c_name}, ${c_firstname} Main Query These variables correspond to the column names from the SQL query N

As can be seen in the example, the Excel column names can be entered in an SQL query with or without aliases.

The column labels can be freely defined.

Data Sources

The sources of the module are:

  • Excel template file
  • Tables that are read via the query defined by the datasqill developer and the Loop query. The datasqill runtime user must have read rights for these tables.

The Excel template file as well as all source tables used in the SQL query must be connected to the module input in the datasqill GUI in the graphical data model.

Data Target

The target of the module is to create a single Excel file. The datasqill runtime user must have write rights for the target folder of this file. The target Excel file must be connected to the transformation output in the datasqill GUI in the graphical data model.

Attributes

In its delivered state, the module offers the following attributes in the GUI for the datasqill developer:

Name Type Meaning
Empty Directory Boolean The module deletes all files in the target folder before writing when this attribute is set to 'Y'
Excel Sheet Char The name of the Excel sheet that is written
Loop Query SQL Loop query to use results in the main query or in the filename
Suppress Empty Sheet Boolean When enabled, the Excel sheet without data is not generated

First Example

The following Excel template is used for the example: Example Excel Template

The example shows how to generate an Excel file and set a timestamp for the filename using a Loop Query (the queries are written for Postgres DB)

Loop Query:

SELECT TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD') load_date

Main Query:

SELECT c_custkey AS key
     , c_name
     , c_firstname
  FROM stage.customer

write_excel_file_action

The column "load_date" from the Loop Query as a variable ${load_date} can be used both in the target Excel file and in the filename.

write_excel_file_transformation

The output file will then be named, for example, Excel_with_data_2021-11-09.xlsx for the date 09.11.2021

Data Types & Formats

The "Write to Excel" module supports the following data types

  • Numbers (integers or decimal numbers)
  • Dates (Date, Time, or Timestamp)
  • Text

If standard Excel formatting is set for the target cells in Excel, the data is formatted as text. This leads to whole numbers being displayed for dates (Excel counts days since 1.1.1900) and for numbers, hints may appear in the form of a yellow exclamation mark indicating a cell formatted as text.

To format numbers or dates and times in the desired format, it is advisable to apply these formats already in the template.

This template was formatted accordingly:

format_template

The cells have the following formats:

  • A3: Standard
  • B3: Number (no decimal places with thousand separator)
  • C3: Date (*Wednesday, March 14, 2012)
  • D3: Time (*13:30:55)
  • E3: Custom (DD.MM.YY hh:mm:ss)
  • F3: Date (14.3.2012 German Gregorian)
  • G3: Custom (hh:mm:ss,00)
  • H3: Custom (h:mm:ss AM/PM)

The result is the following Excel file:

format_result