Load Files From SFTP Folder

[anjali0108]

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 .

[Gysbert_Wassenaar]

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.

[stevedark]

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

[#4]

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

[stevedark]

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=$(vS...]

(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...]

(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

[anjali0108]

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.

[#7]

Hi Anjali thanks for help me,

Sure actually we have WinSCP

can you share the script please? , best regards

Just i have this information:

[anjali0108]

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. 1.      Go to the folder where WinSCP is stored.
2. 2.      Go to “..\WinSCP\PuTTY” and click on Putty Generator
3. Click on generate :
4. 4.      Save private key
   
5. 5.      Click Yes                                          

1. 6.      Save the key in the same folder.
2. 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.

[anjali0108]

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