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: You cannot load data from 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
  • 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:

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 Data Selection 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:

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: You 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:

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?