Data connections in the data load editor provide a way to save shortcuts to the data sources you commonly use: databases, local files or remote files. Data connections lists the connections you have saved in alphabetical order. You can use the search/filter box to narrow the list down to connections with a certain name or type.
Data connection types
The following types of connections exist:
- Standard connectors:
- ODBC database connections.
- OLE DB database connections.
Folder connections that define a path for local or network file folders.
The Attached files folder, which you cannot delete or edit, contains files that are uploaded and attached to the app.
- Web file connections used to select data from files located on a web URL.
-
Custom connectors:
With custom developed connectors you can connect to data sources that are not directly supported by Qlik Sense. Custom connectors are developed using the QVX SDK or supplied by Qlik or third-party developers. In a standard Qlik Sense installation you will not have any custom connectors available.
Creating a new data connection
Do the following:
- Click Create new connection.
-
Select the type of data source you want to create from the drop-down list.
The settings dialog, specific for the type of data source you selected, opens.
-
Enter the data source settings and click Create to create the data connection.
The connection name will be appended with your user name and domain to ensure that it is unique.
The data connection is now created with you as the default owner. If you want other users to be able to use the connection in a server installation, you need to edit the access rights of the connection in theQlik Management Console.
Data connection settings
Each type of data connection has specific settings that you need to configure. The settings for the data connection types are described in this section. Custom connector settings are described in the documentation for the custom connector.
ODBC data connection settings
| Setting | Description |
|---|---|
| User DSN
System DSN |
Select which type of DSN to connect to. For User DSN sources you need to specify if a 32-bit driver is used with Use 32-bit connection. System DSN connections can be filtered according to 32-bit or 64-bit. |
| Username | User name to connect with, if required by the data source. |
| Password | Password to connect with, if required by the data source. |
| Name | Name of the data connection. |
See: ODBC
OLE DB data connection settings
| Setting | Description |
|---|---|
| Provider |
Select Provider from the list of available providers. Only available when you create a new connection. |
| Data source |
Type the name of the Data source to connect to. This can be a server name, or in some cases, the path to a database file. This depends on which OLE DB provider you are using. Only available when you create a new connection. Example: If you selected Microsoft Office 12.0 Access Database Engine OLE DB Provider, enter the file name of the Access database file, including the full file path: C:\Users\{user}\Documents\Qlik\Sense\Apps\Tutorial source files\Sales.accdb Warning noteIf a connection to the data source fails, a warning message is displayed.
|
| Connection string | The connection string to use when connecting to the data source. This string contains references to the Provider and the Data source. Only available when you edit a connection. |
| Windows integrated security | With this option you use the existing Windows credentials of the user running the Qlik Sense service. |
| Specific user name and password | With this option you need to enter User name and Password for the data source login credentials. |
| Username |
User name to connect with, if required by the data source. Leave this field empty if you use Windows integrated security or the data source does not require credentials. |
| Password |
Password to connect with, if required by the data source. Leave this field empty if you use Windows integrated security or the data source does not require credentials. |
| Load
Select database... |
If you want to test the connection, click Load and then Select database... to use for establishing the data connection. Information noteYou are still able to use all other available databases of the data source when selecting data from the data connection.
|
| Name | Name of the data connection. |
See: OLE DB
Folder data connection settings
| Setting | Description |
|---|---|
| Path |
Path to the folder containing the data files. You can either:
Information noteIt is not possible to use a mapped network drive in the path.
|
| Name | Name of the data connection. |
Web file data connection settings
| Setting | Description |
|---|---|
| URL |
Full URL to the web file you want to connect to. If you connect to an FTP file you may need to use special characters, for example : or @, in the user name and password part of the URL. In this case you need to replace special characters with a percent character and the ASCII hexadecimal code of the character. For example, you should replace : with '%3a', and @ with '%40'. |
| Name | Name of the data connection. |
Deleting a data connection
Do the following:
- Click E on the data connection you want to delete.
- Confirm that you want to delete the connection.
The data connection is now deleted.
Editing a data connection
Do the following:
- Click @ on the data connection you want to edit.
- Edit the data connection details. Connection details are specific to the type of connection.
- Click Save.
The data connection is now updated.
Inserting a connect string
Connect strings are required for ODBC, OLE DB and custom connections.
Do the following:
- Click Ø on the connection for which you want to insert a connect string.
A connect string for the selected data connection is inserted at the current position in the data load editor.
See: Connect
Selecting data from a data connection
If you want to select data from a data connection to load in your app, do the following:
- Create new connection linking to the data source (if the data connection does not already exist).
- ± Select data from the connection.
Referring to a data connection in the script
You can use the data connection to refer to data sources in statements and functions in the script, typically where you want to refer to a file name with a path.
The syntax for referring to a file is 'lib://(connection_name)/(file_name_including_path)'
Example 1: Loading a file from a folder data connection
This example loads the file orders.csv from the location defined in the MyData data connection.
Example 2: Loading a file from a sub-folder
This example loads the file Customers/cust.txt from the DataSource data connection folder. Customers is a sub-folder in the location defined in the MyData data connection.
Example 3: Loading from a web file
This example loads a table from the PublicData web file data connection, which contains the link to the actual URL.
Example 4: Loading from a database
This example loads the table Sales_data from the MyDataSource database connection.
Where is the data connection stored?
Connections are stored using the Qlik Sense Repository Service.You can manage data connections with the Qlik Management Console in a Qlik Sense server deployment. The Qlik Management Console allows you to delete data connections, set access rights and perform other system administration tasks.