File Extract

Settings

Connection Select a connection of the above mentioned type
SQL query (optional)

By entering an optional SQL statement in the extract, the data can be sorted in advance. Use the name of the extract's connection as the table name in the SQL statement. If no SQL query is entered, performance is improved and all columns and all lines are extracted from the file.

Table names and field names must be written in double quotation marks in the query statement. The columns are identified with the standard aliases of the extract. Two cases are distinguished for these aliases:

Flat File with Header: The columns are marked with the column name in the header. Example: Select “Customers”, “Region”, “Products” from “Sales_file” where “Region” = ‘US’
Flat File without Header: The columns are marked with column numbers.Example: Select "column1", "column2", "column3" from "Sales_file" where “column2” = ‘US’

The SQL syntax of the H2 database engine is used. For more information see http://www.h2database.com/html/grammar.html.
Note: comments in the SQL statement are allowed using -- or // or /* … */
Field structure

The resulting field structure of the File extract can be generated with the Refresh button. The number and the (original) name of all columns are displayed. For each column you can optionally define the following settings with double-click:

Field name An alternative name to the original name of the column. The field will be referenced with this name for the rest of the Jedox Integrator process.
Default This setting defines a first basic mapping. A blank, several blanks, or a null value is mapped to this default value.
Skip lines Ignores a specific number of rows at the beginning of the file. If the header row is set, only the subsequent rows after the header are ignored. The default value of the Skip lines is 0.
Start Defines the first line number to be read from the file. If the header row is set, only the subsequent rows after the header are ignored. The default value of the Start is 1.
End

Defines the last line number to be read from the file. The default value of the End is 1, meaning all rows are read.
Columns

Limits the maximum number of columns to be read. If not set, all columns that are presented in the first data row are used.

Example:

Copy 
** Some comment
**
**
Index;City;Country
1;Freiburg;Germany
2;Paris;France
3;Vienna;Austria
4;New York;USA

skip: 3 / start: 2 / end: 3 / columns: 2

Result:
Index;City
2;Paris
3;Vienna

Updated April 8, 2025