Write to Flat File

The "Write to Flat File" 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 a flat file.

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

Description

The following example shows how to build an export to flat file using the module. First, the SQL query used:

SELECT c_custkey AS key
     , c_name
  FROM staging.customer

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

Data Sources

The sources of the module are tables that are read via the query defined by the datasqill developer. The datasqill runtime user must have read rights for these tables. 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 a single flat file. The datasqill runtime user must have write rights for the target folder of this file. The target flat file must be connected to the module 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'.
Delimiter	Char	Delimiter is a separator for attribute values
Double Quote	Boolean	Enclose attribute values in quotes
Header	Boolean	When enabled, the header is written
GZIP File	Boolean	When enabled, the generated file is added to a ZIP archive
Suppress Empty File	Boolean	When enabled and SQL query returns no results, no file is generated
With BOM	Boolean	When enabled, a generated file will have "UTF-8 with BOM" encoding, otherwise (default) "UTF-8 without BOM". BOM is Byte Order Mark.
Loop Query	SQL	Loop query to use results in the main query or in the filename.
Line Separator	Char	Possible values: 'windows' or 'unix'. If not specified, the default line separator from the operating system of the datasqill server is used

First Examples

The example shows how to generate a flat 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') dt

Main Query:

SELECT c_custkey AS key
     , c_name
  FROM stage.customer

The column "dt" from the Loop Query as a variable ${dt} can be used both in the main query and in the filename.

The output file will then be named, for example, exp_customer_2015-05-01.csv for the date 01.05.2015