SFTP error: File not found | Excels

Hi everyone,

Recently I have been working on downloading a batch of excel files from SFTP. I have been successful in establishing the connection using a combination of Text Input Tool and Download Tool and using the URL to download the data. After this I got a list of excel file names that are present in the SFTP directory.

Now, I'm using Dynamic Input Tool to load the data of the excels in Alteryx. I separately saved a file from SFTP and specified the file and sheet in the input file format option of the mentioned tool.

For iterations, I'm using Fullpath field and Change Entire File Path option. Fullpath is URL of the SFTP concatenated with the filename and sheet name.

Fullpath=[URL]+'\'+[Download Data]+'|||'+ <Sheet Name>+ '$'

The issue I'm facing is instead of looking up at the above mentioned directory, alteryx is looking for the files at different location.

So I receive the error of File Not Found: "C:\Users\xyz\AppData\Local\Alteryx\bin\(ahead is the value of Fullpath field mentioned above)

Can anyone guide me on this please?

For you reference, I've attached the screenshot of the error. Due to compliance rules, can't share the workflow. However, if you need clarification on anything please drop a comment.

Thanks in advance!

error sftp.PNG

Workflow

Developer

Transformation

Database Connection

Accepted answers

danilang

Hi @NancyS

The Dynamic Input tool can't read directly from an FTP site. You'll need to use another Download tool to get the files first..

- Create a new field LocalPath='%temp%\+[Download Data]

- Use Fullpath=[URL]+'\'+[Download Data] as the URL in the 2nd download tool

- Use LocalPath in the "File name from a field" option in the second Download tool.

- Create a new field InputPath=[LocalPath]+'|||'+ <Sheet Name>+ '$'

- Use [inputPath] in your Dynamic Input

Dan

All comments

danilang

Hi @NancyS

The Dynamic Input tool can't read directly from an FTP site. You'll need to use another Download tool to get the files first..

- Create a new field LocalPath='%temp%\+[Download Data]

- Use Fullpath=[URL]+'\'+[Download Data] as the URL in the 2nd download tool

- Use LocalPath in the "File name from a field" option in the second Download tool.

- Create a new field InputPath=[LocalPath]+'|||'+ <Sheet Name>+ '$'

- Use [inputPath] in your Dynamic Input

Dan

NancyS

Hi @danilang , Thanks for the response. Got it, Alteryx does not loads the data from SFTP into the workflow like other data sources, it has to be saved at a local path and used thereafter. So, as suggested I created:

Fullpath='sftp://sftp.xyz.com/out-box/reports/'+[Download Data]+'|||'+'page1_1'+'$'

Note: [Download Data]= filename.xlsx

LocalPath='C:\Users\xyz\abc\'+[Download Data]
InputPath=[LocalPath]+'|||'+'page1_1'+'$'

In the Download Tool, used Full Path as URL and chose the option to save the output from File Name from field-LocalPath faced error: Error transferring data: Error in SSH layer

I checked for information on this error and apparently, this is faced when the permissions to read/write the SFTP files is not granted, which is not the case here

Can you help on this, please?

Thanks!

danilang

Hi @NancyS

The FTP site doesn't deal with file internals like sheet names, so remove the three pipes and sheet name from FullPath so it becomes

Fullpath='sftp://sftp.xyz.com/out-box/reports/'+[Download Data]

For the SSH error, make sure that the second download tool has the same connection and header information as the one that gets the file list

Dan

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703