topic Re: Read files from SFTP folder dynamically into QLiksense in Connectivity & Data Prep

Read files from SFTP folder dynamically into QLiksense

SunainaUmberkar — Mon, 11 Apr 2022 11:10:45 GMT

Hello All,

I need to extract files from SFTP folder dynamically. I am able to list the files in the folder.

This file will be updated everyday. i need to extract the file dynamically. I tried to use variables to do same. There is no error displayed while loading but file data is not extracted.

Please find the code below.

FileList: LOAD Name as Name1, Path, IsDirectory, CreationTime, LastAccessTime, LastWriteTime, Size, FileName() as filename FROM [http://localhost:5555/data?connectorID=FileTransferConnector&table=SFTPListFilesAndFolders&host=10.243.216.11&port=22&userName=sftp_07312&pathToKeyFile=E%3a%5ctemp%5cRSA_Key%5cRSA_Key%5cPREPROD_Private_Key&ignoreProxy=False&ignoreClientCertificateErrors=False&remoteDirectory=%2finternal%2fsftp_05958_07312&appID=] (qvx) where WildMatch(Name,'Contractor_*');

Let vContractor = Peek('Name1');

Let vFilepath='[http://localhost:5555/data?connectorID=FileTransferConnector&table=SFTPGetRawFile&host=10.243.216.11&port=22&userName=sftp_07312&pathToKeyFile=E%3a%5cTemp%5cRSA_Key%5cRSA_Key%5cPREPROD_Private_Key&ignoreProxy=True&ignoreClientCertificateErrors=False&remotePath=%2finternal%2fsftp_05958_07312%2f';

Trace $(vContractor );

Trace $(vFilepath);

Now trying to read this variables in below code to extract files.

Load * from '$(vFilepath)$(vContractor )&appID=]';

This code is not loading the data from file. Kindly suggest where i am going wrong in this. Thanks in advance.

Re: Read files from SFTP folder dynamically into QLiksense

LRAngela — Mon, 26 Jun 2023 03:03:11 GMT

Hi，I have the same problem as you, do you have a solution?

Re: Read files from SFTP folder dynamically into QLiksense

SunainaUmberkar — Mon, 26 Jun 2023 07:29:42 GMT

Hello LRAngela,

Yes i have applied logic and it is working fine.

You can initially define a varaible for your Folder.

Let vFolder = replace('/internal/sftp_05958_07312','/','%2f');

FileList:
LOAD
Name ,
Path,
IsDirectory,
CreationTime,
LastAccessTime,
LastWriteTime,
Size,
replace('$(vFolder)','%2f','/') as SFTP_Folder_Name,
'$(vFolder)' as SFTP_Folder_Encripted
FROM
[http://localhost:5555/data?connectorID=FileTransferConnector&table=SFTPListFilesAndFolders&host=10.243.208.12&port=22&userName=sftp_07312&pathToKeyFile=E%3a%5cTemp%5cRSA_Key%5cRSA_Key%5cPROD_Private_Key&ignoreProxy=True&ignoreClientCertificateErrors=False&remoteDirectory=%2finternal%2fsftp_05958_07312&appID=](qvx)
where WildMatch(Name, 'ALPSToQLIK_Contractor_*');

Files:
Load Distinct
Name as File_Name ,
SFTP_Folder_Encripted as Folder,
LastWriteTime as Time
Resident FileList
order by Name,
SFTP_Folder_Encripted;

Let vContractor_Time = Peek('Time');
Trace $(vContractor_Time);

Then if you have multiple files in folder then u can apply loop and read the files from sftp folder.

For i=0 to NoOfRows('Files')-1
Let vPathF = Peek('Folder',$(i),'Files')&'%2f'&Peek('File_Name',$(i),'Files');
Let vFileName = Peek('File_Name',$(i),'Files');

Contractor_Current_Load:
Load
[ALPS ID] as Emp_ID,
[EMPLOYEE GROUP],
[FIRST NAME],
[LAST NAME],
[LINE MANAGER] as LineManager_ALPS1,
[COST CENTER] as CostCenter_ALPS1,
[REPORTING UNIT] as ReportingUnit_ALPS1,
[COMPANY CODE] as CompanyCode_ALPS1,
[COUNTRY] as [Country],
[EFFECTIVE DATE],
EMPL_STATUS as [Employee Status],
'Contractor' as Flag
from
[http://localhost:5555/data?connectorID=FileTransferConnector&table=SFTPGetRawFile&host=10.243.208.12&port=22&userName=sftp_07312&pathToKeyFile=E%3a%5ctemp%5cRSA_Key%5cRSA_Key%5cPROD_Private_Key&ignoreProxy=True&ignoreClientCertificateErrors=False&remotePath=$(vPathF)&appID=](txt, codepage is 28591, embedded labels, delimiter is ',', msq);