Selecting data in load scripts
You can select which fields to load from files or database tables and which views of the data source you want by using Select data in Data load editor or Script.
As well as selecting fields, you can also rename fields in the dialog. When you have finished selecting fields, you can insert the generated script code into your script.
Some data sources, such as a CSV file, contain a single table, while other data sources, such as Microsoft Excel spreadsheets or databases can contain several tables.
Do not add a table in Data load editor that has already been added as a scripted table with the same name and same columns in Data manager.
You open Select data by clicking on a data connection in Data load editor or Script.
You can also select data to load from the data catalog. For more information, see Loading data from the data catalog.
Selecting data from a database
When selecting data from a database, the data source can contain several tables.
Do the following:
-
In a script editor, click Data sources.
-
Under Data connections on the left, click on a database connection.
The select data dialog is displayed.
-
This step is relevant to Databricks only. If your Databricks host supports Unity catalog, the catalog you specified in the Create new connection dialog will appear in the Catalog field. You can either leave it unchanged or selected a different catalog from the drop-down list.
Information noteWhile you can select data from multiple databases, all of the databases must belong to the same catalog. -
Select a Database from the drop-down list.
Some selection dialogs do not have a Database drop-down list because the database name is entered when the connection is configured.
-
Select Owner of the database.
The list of Tables is populated with views and tables available in the selected database.
Some databases do not require that owners be specified in the data selection process.
- Select a table.
-
Select the fields you want to load by checking the box next to each field you want to load.
You can select all fields in the table by checking the box next to the table name.
Tip noteYou can edit the field name by clicking on the existing field name and typing a new name. This may affect how the table is linked to other tables, as they are joined on common fields by default. -
Select additional tables if you want to add data from them.
Information noteYou cannot rename fields in the data selection wizard at the same time as you filter for fields by searching. You have to erase the search string in the text box first.Information noteIt is not possible to rename two fields in the same table so that they have identical names. -
When you have finished your data selection, do the following:
-
Click Insert script.
The data selection window is closed, and the LOAD /SELECT statements are inserted in the script in accordance with your selections.
-
Selecting data from a Microsoft Excel spreadsheet
When you select data from a Microsoft Excel spreadsheet, the file can contain several sheets. Each sheet is loaded as a separate table. An exception is if the sheet has the same field/column structure as another sheet or loaded table, in which case, the tables are concatenated.
For more information, see Loading data from Microsoft Excel spreadsheets.
Do the following:
-
Click Data sources.
-
Click on the appropriate folder connection in the data load editor.
The select file dialog is displayed.
-
Select a file from the list of files accessible to this folder connection.
-
Select the first sheet to select data from. You can select all fields in a sheet by checking the box next to the sheet name.
-
Make sure you have the appropriate settings for the sheet:
Settings to assist you with interpreting the table data correctly UI item Description Field names Set to specify if the table contains Embedded field names or No field names. Typically in an Excel spreadsheet, the first row contains the embedded field names. If you select No field names, fields will be named A,B,C... Header size
Set to the number of rows to omit as table header, typically rows that contain general information that is not in a columnar format.
-
Select the fields you want to load by checking the box next to each field you want to load.
Tip noteYou can edit the field name by clicking on the existing field name and typing a new name. This may affect how the table is linked to other tables, as they are joined by common fields by default. -
When you are done with your data selection, do the following:
-
Click Insert script.
The data selection window is closed, and the LOAD /SELECT statements are inserted in the script in accordance with your selections.
-
Selecting data from a table file
You can select data from a large number of data files:
- Text files, where data in fields is separated by delimiters such as commas, tabs or semicolons (comma-separated variable (CSV) files).
- HTML tables.
- XML files.
- KML files.
- Qlik native QVD and QVX files.
- Fixed record length files.
- DIF files (Data Interchange Format).
Do the following:
-
Click on the appropriate folder connection in the script editor.
The select file dialog is displayed.
-
Select a file from the list of files accessible to this folder connection.
- Make sure that the appropriate file type is selected in File format.
-
Make sure you have the appropriate settings for the file. File settings are different for different file types.
For more information, see Choosing settings for file types.
-
Select the fields you want to load by checking the box next to each field you want to load. You can also select all fields in a file by checking the box next to the sheet name.
Tip noteYou can edit the field name by clicking on the existing field name and typing a new name. This may affect how the table is linked to other tables, as they are joined by common fields by default. -
When you are done with your data selection, click Insert script.
-
The data selection window is closed, and the LOAD /SELECT statements are inserted in the script in accordance with your selections.
Information noteUsers with edit permissions in a space can read, write, and load DataFiles in that space. Other users will not see the DataFiles.
Choosing settings for file types
Delimited table files
These settings are validated for delimited table files, containing a single table where each record is separated by a line feed, and each field is separated with a delimited character, for example a CSV file.
UI item | Description |
---|---|
File format for delimited table files |
Set to Delimited or Fixed record. When you make a selection, the select data dialog will adapt to the file format you selected. |
Field names | Set to specify if the table contains Embedded field names or No field names. |
Delimiter |
Set the Delimiter character used in your table file. |
Quoting |
Set to specify how to handle quotes: None = quote characters are not accepted Standard = standard quoting (quotes can be used as first and last characters of a field value) MSQ = modern-style quoting (allowing multi-line content in fields) |
Header size |
Set the number of lines to omit as table header. |
Character set |
Set character set used in the table file. |
Comment |
Data files can contain comments between records, denoted by starting a line with one or more special characters, for example //. Specify one or more characters to denote a comment line. Qlik Sense does not load lines starting with the character(s) specified here. |
Ignore EOF | Select Ignore EOF if your data contains end-of-file characters as part of the field value. |
Fixed record data files
Fixed record data files contain a single table in which each record (row of data) contains a number of columns with a fixed field size, usually padded with spaces or tab characters.
You can set the field break positions in two different ways:
-
Manually, enter the field break positions separated by commas in Field break positions. Each position marks the start of a field.
Example: 1,12,24
- Enable Field breaks to edit field break positions interactively in the field data preview. Field break positions is updated with the selected positions. You can:
Click in the field data preview to insert a field break.
- Click on a field break to delete it.
- Drag a field break to move it.
UI item | Description |
---|---|
Field names | Set to specify if the table contains Embedded field names or No field names. |
Header size |
Set Header size to the number of lines to omit as table header. |
Character set |
Set to the character set used in the table file. |
Tab size | Set to the number of spaces that one tab character represents in the table file. |
Record line size |
Set to the number of lines that one record spans in the table file. Default is 1. |
Ignore EOF | Select Ignore EOF if your data contains end-of-file characters as part of the field value. |
HTML files
HTML files can contain several tables. Qlik Sense interprets all elements with a <TABLE> tag as a table.
UI item | Description |
---|---|
Field names | Set to specify if the table contains Embedded field names or No field names. |
Character set |
Set the character set used in the table file. |
XML files
You can load data that is stored in XML format.
There are no specific file format settings for XML files.
QVD files
You can load data that is stored in QVD format. QVD is a native Qlik format and can only be written to and read by Qlik Sense or QlikView. The file format is optimized for speed when reading data from a Qlik Sense script but it is still very compact.
There are no specific file format settings for QVD files.
For more information, see Working with QVD files.
QVX files
You can load data that is stored in Qlik data eXchange (QVX) format. QVX files are created by custom connectors developed with the Qlik QVX SDK.
There are no specific file format settings for QVX files.
For more information, see Building custom connectors with the Qlik QVX SDK.
KML files
You can load map files that are stored in KML format, to use in map visualizations.
There are no specific file format settings for KML files.
For more information, see Loading your own map data.
Previewing scripts
The statements that will be inserted are displayed in the script preview, which you can choose to hide by clicking Preview script.
Including LOAD statements
If Include LOAD statement is selected, SELECT statements are generated with preceding LOAD statements using the SELECT statements as input.
Inline loads
With inline loads, you can type data manually as text. A basic inline load creates a table, and inserts the data fields and records.
You can create and load inline tables in the Data load editor.
The following script contains a simple inline load.
MyTable:
Load * Inline [
Country, Year, Sales
Argentina, 2014, 66295.03
Argentina, 2015, 140037.89
Austria, 2014, 54166.09
Austria, 2015, 182739.87
];
The following syntax is used for the above inline load:
-
Load * Inline instructs Qlik Sense to load all fields from the inline table that follows.
-
MyTable: defines the name of the table. The list of tables in the app can be accessed from Data model viewer and other areas.
-
Square brackets enclose the data.
-
The first line of the load statement defines the data fields.
-
Commas separate data fields and records.
-
A semi-colon closes the load statement.
For information about inline loads, see Using inline loads to load data.