topic Re: Load Files From SFTP Folder in QlikView

Load Files From SFTP Folder

anjali0108 — Thu, 07 Apr 2016 09:24:08 GMT

Hi All,

I want to download many files from SFTP Server. The only information I have handy is :

URL of that SFTP Folder ,a username and a password to get into that .

I tried to use PuTTY as well as suggested in some post .

After downloading PuTTY, I launched PuTTY.exe and the following window is coming :

Could anyone please suggest further steps to connect to the same ?

Any help will be appreciated .Thanks in advance .

Re: Load Files From SFTP Folder

Gysbert_Wassenaar — Thu, 07 Apr 2016 11:03:32 GMT

I don't see how putty is going to help you. That's a tool for starting an interactive command shell on the remote machine. You probably want to use something like WinSCP instead. Anyway, this all has nothing to do with Qlikview. I suggest you find a tutorial on the internet with your favorite web search engine.

Re: Load Files From SFTP Folder

stevedark — Fri, 14 Jul 2017 14:52:45 GMT

You can use the free connector with Qlik Web Connectors and then enumerate around the files on the FTP server and save them locally, where you can then load them into QlikView.

This article has details on the free connectors:

https://www.quickintelligence.co.uk/free-qlik-web-connectors/

Steve

Re: Load Files From SFTP Folder

Thu, 27 Jul 2017 20:50:52 GMT

Hi Anjali Actually i have this problem how to resolve this? can you help me please?

Re: Load Files From SFTP Folder

stevedark — Fri, 28 Jul 2017 07:00:01 GMT

As I mentioned, just download and install the Qlik Web Connectors, put your SFTP settings into there, create a script to download the file list, loop around this to get each file.

Get a list of files using this expression (you will need to set the variables first):

Temp_Files:

LOAD

Name as ftpName

FROM [http://localhost:5555/data?connectorID=FileTransferConnector&table=SFTPListFilesAndFolders&host=$(vServer)&port=22&userName=$(vUserName)&password=$(vPassword)&remoteDirectory=$(vSourceFolder)&appID=]

(qvx)

WHERE IsDirectory = 'False'

;

You can also download file size and age etc. using this query (replace Name as ftpName with * to get the full table).

Then enumerate around the files, downloading each one:

for iFile = 0 to NoOfRows('Temp_Files') -1

let vSourceFile = Peek('ftpName', iFile, 'Temp_Files');

Results:

LOAD

1 as ResultCount,

'$(vServer)' as Server,

'$(vSourceFile)' as File,

Status as Status,

FileCount as Count

FROM [http://localhost:5555/data?connectorID=FileTransferConnector&table=SFTPDownload&host=$(vServer)&port=22&userName=$(vUserName)&password=$(vPassword)&remotePath=$(vSourceFolder)$(vSourceFile)&targetDirectory=$(vFTPOutput)&appID=]

(qvx)

;

next

You will need to URL Encode your file paths. You can either do this manually, or use this function:

sub Encode(vEncodeMe, vEncoded)

let vEncoded = replace(replace(replace(replace(replace(vEncodeMe, ':', '%3a'), '/', '%2f'), '?', '%3f'), '=', '%3d'), '\', '%5c');

end sub

This is used like:

Call Encode(vDocPath & vOutputRoot, vFTPOutput);

The GUI for Qlik Web Connectors will generate half of this code for you anyway, and you can simply copy and paste it into your application.

Hope that helps.

Steve

Re: Load Files From SFTP Folder

anjali0108 — Fri, 28 Jul 2017 07:21:55 GMT

Hi Omar,

I solved this problem by using WinSCP. I installed WinSCP and used a script to pull the files from SFTP Folder.

Also ,,check whether you are able to ping the SFTP Url from the server where you want to pull the files.

Let me know if you want to proceed this way.I will share the script with you then.

Re: Load Files From SFTP Folder

Fri, 28 Jul 2017 18:57:04 GMT

Hi Anjali thanks for help me,

Sure actually we have WinSCP

can you share the script please? , best regards

Just i have this information:

Re: Load Files From SFTP Folder

anjali0108 — Mon, 31 Jul 2017 05:48:29 GMT

Hi Omar,

As I am not able to attach document here, I am sharing the steps below. Please make changes in the lines underlined below :

STEP 1:

Install WinSCP in the same folder where you will keep the rest of your scripts and files for automatic execution.

STEP 2:

Follow the Steps to generate the private key:

1. Go to the folder where WinSCP is stored.
2. Go to “..\WinSCP\PuTTY” and click on Putty Generator
Click on generate :
4. Save private key
5. Click Yes

6. Save the key in the same folder.
7. Change the name of key in the following command

-privatekey=""F:\PrivateKey.ppk"""" of .vbs file stored before.

STEP 3:

Store the following script in the same folder as “.vbs” file type and change the lines that are underlined in the below code with ur url,username ,password and putty key :

Function SFTPDownload(byVal sLocalPath, byVal sRemotePath, byVal sRemoteFile)

Set oFTPScriptFSO = CreateObject("Scripting.FileSystemObject")

Set oFTPScriptShell = CreateObject("WScript.Shell")

sRemotePath = Trim(sRemotePath)

sLocalPath = Trim(sLocalPath)

sOriginalWorkingDirectory = oFTPScriptShell.CurrentDirectory

oFTPScriptShell.CurrentDirectory = sLocalPath

sFTPScript = sFTPScript & "option batch on" & vbCRLF

sFTPScript = sFTPScript & "option confirm off"& vbCrLf

sFTPScript = sFTPScript & "option transfer binary" & vbCrLf

sFTPScript = sFTPScript & "open "WRITE AS sftp://USERNAME:PASSWORD@SFTP URL":22 -privatekey=D:\Script\IngredionPrivateKey.ppk" & vbCrLf

sFTPScript = sFTPScript & "cd " & sRemotePath & vbCrLf

sFTPScript = sFTPScript & "get " & sRemoteFile & vbCRLF

sFTPScript = sFTPScript & "close" & vbCrLf

sFTPScript = sFTPScript & "exit" & vbCrLf

sFTPTemp = oFTPScriptShell.ExpandEnvironmentStrings("%TEMP%")

sFTPTempFile = sFTPTemp & "\" & oFTPScriptFSO.GetTempName

'Write the input file for the sftp command to a temporary file.

Set oFTPScript = oFTPScriptFSO.CreateTextFile(sFTPTempFile, True)

oFTPScript.WriteLine(sFTPScript)

oFTPScript.Close

Set oFTPScript = Nothing

sCmd = """C:\Program Files (x86)\WinSCP\WinSCP.com"" -script=" & sFTPTempFile

oFTPScriptShell.run sCmd

Wscript.Sleep 1000

' Get rid of temp file used for input to sftp

oFTPScriptFSO.DeleteFile(sFTPTempFile)

Set oFTPScriptFSO = Nothing

Set oFTPScriptShell = Nothing

End Function

Dim myResult

myResult = SFTPDownload("COPY PATH OF FOLDER WHERE YOU WANT TO STORE THE FILES", "/", "*")

STEP 4:

Run the .vbs file and all the files will come in the folder you have specified.You can use a .bat file to run the .vbs script like shown below:

@echo off

pushd %~dp0

cscript DownloadSFTPFiles.vbs

Hope that helps. Thanks.

Re: Load Files From SFTP Folder

anjali0108 — Thu, 03 Aug 2017 06:10:02 GMT

Hi Omar,

Just wanted to know if the above solution help you.Do let me know if you were looking for something else.

Thanks,

Anjali