You can select which tables and fields to use when you add data, or when you edit a table. 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.
If a table contains a header row, field names are usually automatically detected, but you may need to change the Field names setting in some cases. You may also need to change other table options, for example Header size or Character set, to interpret the data correctly. Table options are different for different types of data sources.
Selecting data from a database
When adding data from a database, the data source can contain several tables.
Do the following:
- Select a Database from the drop-down list.
- Select Owner of the database.
-
Select the first table to select data from. You can select all fields in the table by checking the box next to the table name.
-
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 joint on common fields by default. -
When you are done with your data selection you can continue in one of two ways:
-
Click Profile to continue with data profiling, and to see recommendations for table relationships.
-
Click Load and finish to load the selected data as it is, bypassing the data profiling step, and to start creating visualizations. Tables will be linked using natural associations, that is, by commonly-named fields.
-
Selecting data from a Microsoft Excel spreadsheet
When adding data from a Microsoft Excel spreadsheet, it 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.
See: How to prepare Excel files for loading with Qlik Sense
Do the following:
-
Make sure you have the appropriate settings for the sheet:
Field names Set to specify if the table contains Embedded field names or No field names. Header size
Set to the number of lines to omit as table header.
-
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.
-
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 you can continue in one of two ways:
-
Click Profile to continue with data profiling, and to see recommendations for table relationships.
-
Click Load and finish to load the selected data as it is, bypassing the data profiling step, and to start creating visualizations. Tables will be linked using natural associations, that is, by commonly-named fields.
-
Selecting data from a table file
You can add data from a large number of data files.
Do the following:
- 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.
-
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 you can continue in two different ways:
-
Click Profile to continue with data profiling, and to see recommendations for table relationships.
-
Click Load and finish to load the selected data as it is, bypassing the data profiling step, and to start creating visualizations. Tables will be linked using natural associations, that is, by commonly-named fields.
-
Returning to the previous step (Add data)
You can return to the previous step when adding data.
Do the following:
- Click to return to the previous step of Add data.
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.
File format settings
| File format |
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.
Setting field break positions
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.
File format settings
| 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. |
HTML files
HTML files can contain several tables. Qlik Sense interprets all elements with a <TABLE> tag as a table.
File format settings
| 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.
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.
See: Loading map data