CSV Extract

Settings

Connection Select a CsvFile connection.
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 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.
Use caching If caching is activated, the complete output of the extract is temporarily stored during the first call of the extract, using an internal H2 database. Subsequent calls of the extract read directly from the cache without connecting to the underlying source system of the extract. If the extract or the underlying connection contains variables, a separate cache is build for different values of these variables.See Caching in Extracts and Transforms for more information.

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 May 12, 2025