Loading data from files
Qlik Sense can read data from a variety of file formats.
File formats
There are several supported data file formats:
-
Text files: Data in fields must be separated by delimiters such as commas, tabs, or semicolons. For example: comma-separated variable (CSV) files.
- HTML tables
-
Excel files:
Information noteYou cannot load data from password-protected Excel files, or Excel Binary Workbook files (.xlsb).For more information, see Loading data from Microsoft Excel spreadsheets.
-
XML files
-
Qlik native QVD and QVX files
For more information, see Working with QVD files.
- Fixed record length files
-
Data Interchange Format (DIF) files: DIF files can only be loaded with the data load editor.
Connection types
You can load files from different data connection types:
-
Local and network file folders: For more information, see Loading files from local and network file folders.
-
The Attached files folder: You cannot delete or edit this folder. It contains files that are uploaded and attached to the app. (Not available in Qlik Sense Desktop). For more information, see Adding data to the app.
-
Files on the web: For more information, see Loading files from web resources.
How do I load data from files?
There are several ways to load data from files.
Selecting data from a data connection in the data load editor
You can go to Data Connections, and use the Select data dialog to select data to load.
For more information, see Select data in the data load editor.
Loading data from a file by writing script code
Files are loaded using a LOAD statement in the script. LOAD statements can include the full set of script expressions. To read in data from another Qlik Sense app, you can use a Binary statement.
For more information, see Load.
Loading files from local and network file folders
You can load files from local and network file folders with a folder connection:
UI item | Description |
---|---|
Path |
Path to the folder containing the data files. You can either: Select the folder, type a valid local path, or type a UNC path. Example of valid local path: C:\data\DataFiles\ Example of a UNC path: \\myserver\filedir\ Information noteYou cannot use a mapped network drive in the path.
|
Name | Name of the data connection. |
Loading files from web resources
You can load files from web resources, such as FTP, HTTP, or HTTPS, with a web file data connection. The file can be of any type supported by Qlik Sense:
UI item | Description |
---|---|
URL |
Full URL to the web file you want to connect to, including the protocol identifier. Example: http://unstats.un.org/unsd/demographic/products/socind/Dec.%202012/1a.xls 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. |
The URL set in the web file data connection is static by default, but you can override the URL with the format specification setting URL is. This is useful if you need to load data from dynamically created URLs.
For more information, see URL is.
Loading data from a dynamically created URL
In this example we want to load forum posts from the first 10 pages of the New to Qlik Sense forum of Qlik Community. The forum page contains 20 posts on each page, and the final parameter of the URL ,start, sets which post to show as the first post of the page. In the example URL here, the page will show posts starting with post number 20, and the following 20 posts.
https://community.qlik.com/community/qlik-sense/new-to-qlik-sense/content?filterID=contentstatus%5Bpublished%5D~objecttype~objecttype%5Bthread%5D&itemView=detail&start=20
With the counter i we step through the pages with a step of 20 until 180, which means the For loop executes 10 times.
To load the page, we substitute the start page with $(i) at the end of the URL in the URL is setting,.
This will load the 200 most recent posts of the forum in a table, with title, author, number of replies and views, and time of latest activity.