How to load file to Excel Power query from SFTP site

Question

I am trying to load a file to Excel Power Query from the SFTP site and I don't see any option. Following are the sample WINSCP SFTP site credentials:

File Protocol: SFTP
Host Name: ftp.test.com
Port Number: 22
Username: Test1

I can load it from my local C drive, but Can you let me know how I can load it from SFTP drive site?

Can anyone help me with this? Thanks.

Answer 1

Currently, I don't think there is a connector that will do this. There are, however, a few possible workarounds if you're willing to do some scripting as suggested in this reddit thread.