Native SFTP connector and FileList()

[steeefan]

Hi,

I have several applications that connect to SFTPs servers still via Qlik Web Connectors. I gradually want to change this so these apps use the native SFTP connector. I was able to create the connection and download single files.

However, some of the apps in production use the SFTPListFilesAndFolders operation to list files from a given folder on the SFTP server and then download each file present in the folder. I tried the usual way of using FileList() but to no avail:

LIB CONNECT TO 'LibName';

FOR EACH vFile IN FileList('lib://LibName/Folder/*')
  TRACE $(vFile);
NEXT vFile;

This script is retuning no results. There are however files in the specified folder.

Has anyone gotten a use case like this to work using the native SFTP connector? If so, how did you solve it?

[marcus_sommer]

AFAIK does FTP not support any file-functions as filetime() or wildcards like * or ?.

A workaround may be to execute an appropriate batch in beforehand which read or copy & paste the content - take a look for commands like mget.

Another approach might be to create a generic listing which is used to combine the path with the file-name-pattern and the variable to create a valid full-path. In many scenarios the possible categories/countries and/or periods are known so that's not to difficult to create such listings - whereby it might be needed to implement also an error-handling to fetch any exceptions.

[steeefan]

I was hoping the native SFTP connector would make such functions available. It's quite a setback that it is not as useful as the one supplied by Web Connectors.

I already had the idea of creating an external script that would connect to the SFTP server and write the folder contents to a file for QS to read. However when migrating to the cloud, I would still have to run that script somewhere, so I was hoping to avoid that.

The file names in this specific case are all distinct, such as Categories.csv, Classes.csv, Users.csv etc. The set of files is not changing often so maintaining a manual config list would be feasible but I was still hoping to not having to do that.

In any case, thanks for your suggestions, @marcus_sommer.

[marcus_sommer]

I don't know this connector and if it had such capabilities - but it's not only a matter of the connector else also from the FTP server if such feature is possible and enabled. The connector comes from the outside and could only call functions which the server provided.

You may ask the responsible IT team if they could implement an automatic job which creates in each folder a txt-file with all the file-names and maybe also the upper folder-structur (I think the security guys would regard it as a serious risk).

[p_verkooijen · Accepted solution]

@steeefan @marcus_sommer 

Any FTP(S)/SFTP server is able to accept the ls command.

It is a SFTP only connector that has been "delivered" but is lacking several functions that are available within the Qlik Webconnector. 

The issue here is the same as you have with other connectors, for instance using OneDrive or Google Drive you need the Data sources Metadata connector to scan for files and then you use this data to loop through in the File locations connector.

For SFTP this is not available.

You're better of keep using Qlik WebConnectors than getting IT to create a list files file on the FTP.

[steeefan]

I was thinking the same, basically by just using any SFTP client, ls is called all the time.

Anyways, thanks for trying to help out, @marcus_sommer and @p_verkooijen. I think this topic can be closed. As of now, there's nothing that can be done just within Qlik Sense excpet for sticking with the SFTP connector from the web connectors package.