Loading data from files

Qlik Sense can read data from files in a variety of formats:

  • Text files, where data in fields is separated by delimiters such as commas, tabs or semicolons (comma-separated variable (CSV) files).
  • HTML tables.
  • Excel files (except password protected Excel files).

    For more information, see Loading data from Microsoft Excel spreadsheets.

  • XML files.
  • Qlik native QVD and QVX files.
  • Fixed record length files.
  • DIF files (Data Interchange Format). DIF files can only be loaded with the data load editor).

You can load files from different data connection types:

How can I load data from files?

There are several ways to load data from files.

  • Adding data with Add data, the quickest way to load data from a file. You can load from an existing data connection, or connect to a new data source on the fly.

    For more information, see Adding data to the app.

  • Selecting data from a data connection in the data load editor.

    Instead of typing the statements manually in the data load editor, you can 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. You need to make the following settings to the data connection.

Settings for the data 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\MyData\

Example of a UNC path: \\myserver\filedir\

Note: It is not possible to 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. You need to make the following settings to the data connection:

Settings for a web file data connection
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,.

For i = 0 to 180 step 20 LOAD Title1, "Author", F6 As Replies, Views, "Latest activity" FROM [lib://x2] (URL IS [https://community.qlik.com/community/qlik-sense/new-to-qlik-sense/content?filterID=contentstatus%5Bpublished%5D~objecttype~objecttype%5Bthread%5D&itemView=detail&start=$(i)], html, utf8, embedded labels, table is @1); Next i;

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..

Did this information help you?

Thanks for letting us know. Is there anything you'd like to tell us about this topic?

Can you tell us why it did not help you and how we can improve it?