File Extract

image_pdfimage_print

The File extract reads data from a File or Directory connection type. 

Optional SQL Query

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 that up until Jedox Integrator 3.3, the SQL syntax of Apache Derby DB has been used.
Note that comments in the SQL statement are allowed using “\\”, “/* … *” or “–“, but not between the keyword FROM and the table name.

Field Structure 

The resulting field structure of the File extract can be generated with the option “Refresh”. The number and the (original) name of all columns are displayed.

For each column you can then optionally define:

FieldName: 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 defines a first basic mapping. A blank, several blanks, or a null value will be mapped to this default value.
Advanced Settings

Skip Lines:

Number of lines to be skipped at the top of the file before the header line (optional),

Start:

Number of the line to start the import (optional). The standard setting is 1.

End:

Number of the last line to import (optional).

Columns:

Numbers of columns in the text file (optional). This is only necessary if the number of columns differs from the number of values in the first line of the text file.

Use caching:

Memory, disk, or none (default). See Caching in Extracts for more information.

image_pdfimage_print