SFTP error: File not found | Excels

[NancyS]

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!

[danilang · Accepted solution]

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

[NancyS]

Thanks @danilang , that worked. Thank you for the help.