QWC SFTPGetRawFile - load multiple files

[fks]

I've got files on a SFTP-server updated daily that I would like to load directly into Qlik Sense.

I use the Qlik Web Connectors SFTPListFilesAndFolders to generate a list of new or modified files to download.

My idea was then to loop through the list and (down)load the files with SFTPGetRawFile, but it seems that this function does not support dynamic naming/multiple files?

I would prefer to have the data loaded directly into Qlik Sense and not downloaded locally as an intermediate step.

I'm running Qlik Sense in Standard Mode but could possibly change to Legacy Mode if it would make any difference.

[chrisbrain]

Hi - How do you mean 'support dynamic naming/multiple files?' I think you should be able to write some load script to dynamically update the file being loaded based on the results in the SFTPListFilesAndFolders table.

[fks]

The filelist from SFTPListFilesAndFolders  is filtered down to a daily list of files to be fetched from the SFTP-server. This is working very well.

What I struggle with is then to get the SFTPGetRawFile to loop through the list and load each file to a common table in Qlik.

[chrisbrain · Accepted solution]

Hi - I put together this sample script with some notes at the top about the steps I went through to create it. Hope it helps.

/*
Steps to create this script:
1) Run the SFTPListFilesAndFolders table in the QWC UI to generate the first LOAD statement. Update (shorten) the field and table names. Follow advice in web UI about settings up web fie connection and creating vQwcConnectionName variable.
2) Construct for/next loop to iterate through each row in the FilesAndFolders table and process each row which represents a file (IsDirectory=False).
3a) In the QWC web UI, for one of the files, run the SFTPGetRawFile table and obtain the generated request URL.
3b) Create a new web file connection in Qlik Sense using this URL and then use it to select the data required from the source file. This will generate the basic structure of the second LOAD statement which is then pasted inside the if statement of the for/next loop.
3c) The web file connection created in 3a is no longer needed as we can reuse the first one.
4a) Update both load statements so that the remoteDirectory (for the SFTPListFilesAndFolders table) and the remotePath (for the SFTPGetRawFile table) are updated dynamically from script variables.
4b) Added some simple URL encoding of common characters which can cause issues in the load statements to QWC.
5) I also added an additional '$(vFilePath)' as Path field to the data table so it can be linked to the FilesAndFolders - this may not be necessary. You may even just want to
6) As a final step, I extracted all the common parts of the URL request to the FTP connector to a separate vFtpBaseUrl variable at the top.
*/

// Update this with your directory name
let vDirectory = '/';

let vQwcConnectionName = 'lib://QWC';
let vFtpBaseUrl = 'http://localhost:5555/data?connectorID=FileTransferConnector&host=[YOUR FTP HOST]&userName=[YOUR FTP USERNAME]&password=XXXXXXX&ignoreProxy=False&ignoreClientCertificateErrors=False&appID=';

let vDirectoryEncoded = replace(vDirectory, '/', '%2f');
vDirectoryEncoded = replace(vDirectoryEncoded, ' ', '+');
      
FilesAndFolders:
LOAD
    Name,
    Path,
    IsDirectory,
    CreationTime,
    LastAccessTime,
    LastWriteTime,
    Size
FROM [$(vQwcConnectionName)]
(URL IS [$(vFtpBaseUrl)&table=SFTPListFilesAndFolders&remoteDirectory=$(vDirectoryEncoded)&appID=], qvx);

LET vNoOfFiles = NoOfRows('FilesAndFolders');
	
for i=0 to $(vNoOfFiles)-1
	
	if(peek('IsDirectory', $(i), 'FilesAndFolders') = 'False') then

		let vFilePath = peek('Path', $(i), 'FilesAndFolders');
		let vFilePathEncoded = replace(vFilePath, '/', '%2f');
		vFilePathEncoded = replace(vFilePathEncoded, ' ', '+');

		trace 'Loading file $(vFilePath)';

		// You will need to update this as per step 3) above.
		Data:
		LOAD
		'$(vFilePath)' as Path,
		"Field1",
		"Field1",
		// etc.
		FROM [lib://vQwcConnectionName]
		(URL IS [$(vFtpBaseUrl)&table=SFTPGetRawFile&remotePath=$(vFilePathEncoded)]
		, qvd);


	endif 
    
next i

// Optional
// drop table FilesAndFolders;