QlikView has loading data from an FTP site built directly in to it's product. I recently ran in to a scenario that required a connection to a Secure FTP site. As of QV11, connection to an SFTP sites is not supported.

To handle this I used PuTTY, a free telnet/SSH client, to pull the file down to a local drive where QV could read it. One of the great things about PuTTY is that it does not HAVE to be installed. You can download a portable edition that just sits on a folder on your desktop. This is important because many QV developers don't have administrative rights on their box making installation a no go.

I'm not a PuTTY expert so I welcome any feedback. In the meantime, here's how I was able to get things up and running.

PuTTY is a command line tool that expects user interaction to perform a function.

If you type in 

You get

here I would type something like

This tells PuTTY to get the first file (20120410 - 20120411.zip), download it, and save it to "C:\20120410 - 20120411.zip". 

The fact that PuTTY is interactive and asks me for direction at every step causes an issue. I need to be able to automate the script. The answer is to point PuTTY to another batch script with the commands I want it to perform. If you use the -b switch with PuTTY, it will execute the .bat file you point it to. For example

All I've done is to take the initial command that opened the SFTP site and added the -b option pointing towards "get.bat". In this scenario, PuTTY will open the site, log on, and execute get.bat on the SFTP file system.

In my scenario, get.bat is the following

get.bat tells PuTTY to

1. 'cd /Properties5085' (change directory to Properties5085)

2. 'get 20120410 - 20120411.zip' (download the file)

3. 'C:\Share\Properties\20120410 - 20120411.zip' (put the file in C:\Share\Properties  and name it 20120410 - 20120411.zip)

Now that we know how to execute PuTTY and how to point it to a .bat file that will make it do our bidding, we need to automate the initial PuTTY command by taking this:

and pasting it in to Notepad. Save the file as PuTTY.bat.

This leaves you with two .bat files. The first (PuTTY.bat) will tell PuTTY to log in and pass things of to the second .bat file. The second .bat file (get.bat) will download the specified file.

There are many creative ways to launch this process but the best is to simply launch PuTTY.bat from windows scheduler. Many of you are thinking, 'I can just launch this from an EXECUTE command from a QlikView script'. That is true but doesn't work so well in a Server/Publisher environment. 

Server/Publisher doesn't know when a .bat file is done running so it will just hang until it's timed out. There are ways around this but are outside of the scope of this talk.