Retrieve excel data from an SFTP site

[amirmbhd]

Hello everyone,
A third-party company is putting my excel files on an SFTP site, and I am trying to retrieve them through an SSH connector node, but I am not sure how to move forward with it. As you can see in the attached pictures, it looks like the SSH connector is working okay, but I don’t know the next step? I saw another thread that mentioned using the list remote files node, but that did not work! Could you please guide me through the next steps to retrieve the data and put it in my workflow?

[bruno29a]

Hi @amirmbhd , you can use the Transfer Files node after that:

By default, the Transfer Files node does not have an input port as you can copy from local source.

To add a port, just click on the 3 dots like this:

And follow the sub-menu:

This will add an input port where you can then connect your SSH Connector output port to:

More info:

Transfer Files

[amirmbhd]

Thank you so much @bruno29a , it worked perfectly. I just have one more question. Is there anyway to retrieve the files without saving them on local computer? I am just asking because that third party is going to keep uploading new excel files every week and I feel like the folder size gonna get bigger and bigger and I was curious if I can just read the excel file directly from the SFTP site?

[bruno29a]

Hi @amirmbhd , no problem.

Regarding your question, well, you are “retrieving” the files… so you are fetching/getting the files from the sftp.

Can you explain what do you do once you retrieve the files? Are you processing them in some ways? You can always delete the downloaded files after you processed them if space is going to be a problem.

[amirmbhd]

@bruno29a Sorry for being confusing. The way I set it up right now, it will download all the files and will put them in a folder for me. Then the folder gets overwritten with any new data. On the other side, I am using the excel reader node, and I am selecting my file from that folder on my local computer. I was wondering if I can just directly select the excel file from the SFTP site.

[bruno29a]

Hi @amirmbhd , no problem at all.

I don’t think you can read the Excel files directly from the sftp as you would need to authenticate to be able to go into the sftp.

Once you read the files locally and process them, you can have the workflow to delete the files.

[amirmbhd]

Thank you so much @bruno29a for all your help. That makes sense.

[ipazin]

Hello @amirmbhd,

@amirmbhd

I was wondering if I can just directly select the excel file from the SFTP site.

Don’t see why not if you connect SSH Connector to Excel Reader the same way as Transfer Files

Additionally here is link to (new) File Handling guide:

https://docs.knime.com/latest/analytics_platform_file_handling_guide/index.html#introduction

Br,
Ivan

[bruno29a]

Nice one @ipazin , I was not aware that we could add a connector port as input to the Excel Reader! Thanks for sharing as always.

[amirmbhd]

This is exactly what I needed, thank you so much!