Using files from an SFTP as a dynamic data source

[paulzov]

Hi all,

I have a template that I populate with raw data in a .csv format. These .csv raw files are located on an SFTP. The idea here is I am trying to update my template directly from the SFTP data source, as opposed to pulling the newest file each day, and inputting it manually onto the template.

After a lot of searching on the web, I am having difficulty in finding a solution to this. I'm pretty strong with the the general-use functions in Excel, and know enough to attempt at making VBA code modifications to work when I encounter them, but this task might be a bit beyond me, I'm not even sure how or if I can even connect to an SFTP (not apparent to me when to use the data connection wizard). Any help on approaching this or if it's even feasible that would be much appreciated, the general vibe I've seen is that these types of tasks are usually done with a SQL database like Microsoft Access, but unfortunately I do not have access to those tools other than the SFTP client I'm using (WinSCP).

Thanks in advance for any help!

Paul

[excelforumnev]

I have the same need. My spreadsheet uses a local file. I hit refresh inside Excel and the new data is brought in. Same as Paulzov, I use an SFTP client to pull the file down from the server into the correct local directory.

It sure would be nice to have the spreadsheet pull the file from the server. The main reason is that several people want to see this spreadsheet, it would also just be quicker and easier. If Excel could do it, I could give them the spreadsheet and be done with it. Currently, I have to pull the data, load it, then send the updated spreadsheet to them.

Possibly, I could map a local folder to the SFTP server with some software, so Excel thinks it's loading a local file.

Or, I could write a script that pulls it down to the local drive, which would be easier than firing up WinSCP, etc. Maybe end users could run a script.

hmmm ... is there a way to have Excel run that script? That would be a great solution if Excel can't talk to SFTP. I mean, Excel can talk to a database on a remote server, so the remote server part is already there ...