SFTP
The Qlik SFTP connector provides secure file system access using the SFTP protocol (SSH File Transfer Protocol), allowing you to stream data directly into your Qlik Sense app
Connecting to an SFTP account
To access your SFTP data, you will need to select the connector in Qlik Sense, and authenticate the connector with your SFTP credentials.
For example, a Qlik Sense deployment in the North American region may have significantly slower download/upload speeds when connecting to a remote file system in the Asia-Pacific region.
You can access the connector through Add data and the Data load editor.
- From Add data, select SFTP from the list of data connectors.
- From the Data load editor, click Create new connection and select SFTP.
Authentication
The first step to create an SFTP connection is to authenticate the connector with your SFTP credentials:
-
Host
You can use a host name or an IP address.
-
Port
Ports 22 and 2222 are supported.
-
Host Public Key algorithm
Set which algorithm is used for the host public key. Default value is RSA.
-
Host Public Key or Host Public Key fingerprint
-
Host Public Key must be a base64 encoded key. If the key is padded, that is, ending with "==", the padding must be included.
-
Host Public Key fingerprint must be a base64 encoded SHA256 fingerprint.
-
-
Username
-
Password
-
Private Key File
If a passphrase is configured for a private key, you also need to set it in Private Key Passphrase.
You must specify at least one of Password or Private Key File.
When you are finished setting up your data connection, click Create to save the connection. The connection credentials are verified automatically. From the Data load editor, you can also verify the connection before you create it by clicking Test Connection.
Add data
A default name is applied to the data connection.
Data load editor
You can specify a connection name or leave the default name for the connection.
Connector location
The location of the connection depends on how you select data.
-
From Add data, it will be saved under File Locations.
-
From the Data load editor, it will be saved under Data connections with the name that you provided.
Selecting data with an SFTP connection
When you create an SFTP connection with Add data, the data selection step follows immediately. When you create a data connection with the Data load editor, you need to open the data connection before you can select data.
To select data, first, navigate to the file path location on the SFTP host. Next, locate your files by clicking on the folder names. You can navigate backwards by clicking on the previous folder in the path field or by clicking . You can filter the file types that are shown by using the File type drop-down menu.
First, select a file. Then, select the data tables and the data fields that you want to load. You can rename data fields before loading them into Qlik Sense by clicking on the field name and entering a new name. The new name is mapped by Qlik Sense as an alias to that field name in the database.
Data file properties
If you are loading data from an excel file or from a delimited file, you may need to adjust the data file properties. The following table describes the data file properties for these file types:
Parameter | Description | For file type |
---|---|---|
Field names (Embedded) | 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. |
Delimited files .xls/.xlsx |
Field names (None) | Select if the fields are not labeled. If you select this option, fields will be named A,B,C... |
Delimited files .xls/.xlsx |
Header size | Set to the number of header rows to ignore. Typically, you want to exclude rows that contain general information that is not in a columnar format. |
Delimited files .xls/.xlsx |
Delimiter | Set the field delimiter. Select a standard delimiter (comma, semicolon, space, tab) or a custom delimiter. | Delimited files |
Quoting (MSQ) | Used to specify modern style quoting which allows multi-line content. End-of-line characters must be enclosed with double quotation marks. Double quotation marks (“) appearing as the first or last character in field content will be interpreted as the start or end of multi-line content. | Delimited files |
Quoting (Standard) | Used to specify standard quoting. Double and single quotation marks accepted only when they are the first and last blank character of a field value. | Delimited files |
Quoting (No Quote) | Used if quotation marks are not to be accepted in the file. | Delimited files |
Character set | Select the character set for the data you are loading. Select a standard character set or a custom character set. | Delimited files |
Ignore end-of-file-character | Select to ignore characters with code point 26, which otherwise denotes an end-of-file character, are disregarded and can be part of a field value. Only relevant for .txt files | Delimited files |
Comment | Data files can contain comments between records. Comments are 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. | Delimited files |
Load data into your Qlik Sense app
Once you have finished selecting data, you can load your data into your Qlik Sense app. How you proceed will depend on whether you load data with Add data or the Data load editor.
Add data
To load data using Add data, click Add data. This will open the data in the Associations view of the data manager. In the associations view, you can continue to add data sources, transform the data, and associate the tables in Data manager.
Data profiling is enabled by default when you click Add data. Data profiling does the following:
- Recommends data associations.
- Auto-qualifies common fields between tables. This adds a unique prefix based on table name.
- Maps date and time fields to autoCalendar.
Tables are not associated on common field names automatically. You can associate tables in the Associations view.
If you want to load the data directly into your app, click and then disable data profiling.
When you add data with data profiling disabled, all existing data from data sources will be reloaded when you add the data. Tables will be associated on common field names automatically. Date and time fields will not be created.
Data load editor
To load data with the Data load editor, click Insert script once you are finished selecting the data. A load script is inserted into the script editor of the Data load editor. You can continue to edit the script in the script editor or you can click Load data to run the data load script.