Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 .
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.
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
Hi Anjali Actually i have this problem how to resolve this? can you help me please?
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
(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
(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
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.
Hi Anjali thanks for help me,
Sure actually we have WinSCP
can you share the script please? , best regards
Just i have this information:
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:
-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.
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