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.ftp://%3Cuserid%3E:%3Cpassword%3E@%3Cservername%3E/path/filename.qvd
When extracting the data from the FTP server:
- 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).
- 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):
- Navigate to the folder with the files you need:
- Right-click on the file and select
Copy Link Location:
In my example, I chose ROUTE_BUS.csv:
- Open an existing Qlik Sense application or create a new one.
- Open the
Data load editor. - Click on
Create new connectionand selectWebfile. - The
Select web filewindow will open. - 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. - Name the connection as
QlikSense Cookbook FTP ROUTE_BUS, as shown in the following screenshot:
- In the list under
Data connections, selectQlikSense CookbookFTP ROUTE_BUSand click on Select data. This will open up a preview window listing the contents of the file. - 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.
- Change the value under
Field namestoEmbedded field names.
- The preview of the table will look like the following screenshot:
- Select all the fields from the table in the preview window. Click on
Insert scriptto load the web data in the application. - Name the table as
ROUTE_BUSand 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); - Save and load the data. Once the script is successfully loaded, the
Data model viewerwill show the loaded table.
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.