Extracting data from the FTP server

In the last recipe, you learned how to fetch data from a website using the web file connection. With the same connection type, we can extract data from files available in an FTP server.

To demonstrate this recipe, I'm using a local FTP server with user authentication. My server is localhost, my userid is qlik, and my password is pwd.

As a general rule, you must use the following parameters in the URL if the server requires authentication to retrieve data from a file ftp://<userid>:<password>@<servername>/path/filename.qvd.

Getting ready

When extracting the data from the FTP server:

1. Open your web browser to check whether the URL is correct and whether you have access to the server (I prefer Firefox for this task).
2. Insert the following URL from which you can see a list of folders and files from a local FTP server: ftp://qlik:pwd@localhost (change the URL with your credentials and server name):

3. Navigate to the folder with the files you need:

4. Right-click on the file and select Copy Link Location:

In my example, I chose ROUTE_BUS.csv:

1. Open an existing Qlik Sense application or create a new one.
2. Open the Data load editor.
3. Click on Create new connection and select Web file.
4. The Select web file window will open.
5. Insert the URL copied from the web page to set a connection with the file to extract data: ftp://qlik:pwd@localhost/chapter%201/QlikSenseData/ROUTE_BUS.csv.
6. Name the connection as QlikSense Cookbook FTP ROUTE_BUS, as shown in the following screenshot:

How to do it...

1. In the list under Data connections, select QlikSense Cookbook FTP ROUTE_BUS and click on Select data. This will open up a preview window listing the contents of the file.
2. If the file is large, more than one megabyte, the preview can take a long time to open because of low internet speed and bandwidth when connecting to the remote FTP server.
3. Change the value under Field names to Embedded field names.

4. The preview of the table will look like the following screenshot:

5. Select all the fields from the table in the preview window. Click on Insert script to load the web data in the application.
6. Name the table as ROUTE_BUS and the script will read as follows:

ROUTE_BUS: 
LOAD 
    Route_id, 
    CHANGE 
FROM [lib://QlikSense Cookbook FTP ROUTE_BUS] 
(txt, codepage is 28592, embedded labels, delimiter is ',', msq); 

7. Save and load the data. Once the script is successfully loaded, the Data model viewer will show the loaded table.

How it works...

Qlik Sense connects to the FTP source file using the stored data connection. Once connected, it identifies the content of the source folders and lists them in the preview window.

If the file is large, the preview will take a long time to open.

For each file, you must create an FTP connection, so this recipe is used in very specific use cases.

If you need to read several files, files that are large in size or both, I recommend using an FTP client to download the files beforehand to a local folder.

You can perform the FTP download using the Windows FTP command. The LOAD script can trigger the FTP command using EXECUTE if legacy mode is enabled.

There's more...

Qlik Sense can also extract data from other data formats, such as XML, XLSX, XLS, or QVDS. The underlying principles remain the same as explained in the preceding recipes.

See also...

• The Creating a master library from the Data model viewer recipe
• The Activating the legacy mode in Qlik Sense® desktop recipe