Get list of files from SFTP

[BonusCup]

Hi,

I'm building a workflow where I need to go in and first get a list of filenames from an SFTP server.  Using the links below I found from the Community, I've created a workflow where I can successfully log into the SFTP and download a specific file using a Text Input and Download tool

ISSUE:  When I change the 'url' in the Text Input to just the directory where the files are located (http://abc-b0-c1:xxxx/def/data/ghi/) and change the Download Tool configuration to Output to a String the "DownloadData" field is blank in the Results 

Community Links:

https://community.alteryx.com/t5/Alteryx-Designer/Download-from-SFTP/td-p/44846

https://community.alteryx.com/t5/Alteryx-Designer/Download-SFTP-files-with-Pattern/td-p/482508

https://community.alteryx.com/t5/Alteryx-Designer/List-Files-on-SFTP-Server/td-p/78016

Thanks in advance for any assistance with this.

[DiganP]

@BonusCup Would it be possible to attach the workflow so we can take a look at the configuration?

[BonusCup]

@DiganP 

It's a basic workflow of Text Input > Download.  Attached is the workflow but I changed the URL in the text input and the username in the Download's Connection tab.  Let me know if anything else is needed.

Thanks

[aweldon1]

Hi - Were you able to sort this out? I apologize I'm unable to help but I am having the same issue where I'm not receiving errors but the "DownloadData" field ends up being blank. Hoping someone has found a solution! 

[BonusCup · Accepted solution]

@aweldon1 

I ended up using WinSCP to generate a transfer code to get the format I needed to log in and download the file I need daily.  If scheduling the job on an Alteryx server for automation, that server will need to have WinSCP installed also and you will need to know the location of the WinSCP.com application. 

After logging into WinSCP, I did the following:

• right-click a file as if you were going to download it
• Select "Generate Code..."
• Select "Batch File" as the Format
  • You should see the script below that

Using a Text Input, I enter each line of the batch into a field changing the "/log" path and the "lcd" directory to where I want the file downloaded to.  In my case, I use formulas to edit date in the filename I need for the "get " field.

From there I use a Transpose tool and under "Data Columns" I select all the fields needed for the batch file.  That should create a new "Value" field and in the Results you should see you batch file line by line.  Use a Select tool to only select that Value field.

I then use the Run Command tool and configure the Output like this:

• %temp% .bat
• Delimiter = \0

I call that %temp% .bat in the Command of the Run External Program section and for the input of the Read Results section I bring in the file that was downloaded to the LCD location.

It took quite a bit of effort getting this to work and I'm sure there is an easier way of doing it but this worked for me.  I hope this helps you out. 

Attached is the sample workflow.

[Suresh_Gurbaxani]

Hi,
Were you able to do it without winscp also?

[BonusCup]

@Suresh_Gurbaxani 

No, the only way we had figured it out was through WinSCP.

[sabapathiv]

Hi There,

I was not able to find a solution for this. I tried this solution and it worked for me.

Thanks a lot.

[kunvai1]

Hey, I am trying the same way you mentioned. But I am getting an error near run command tool: Failed to run external program. What should I mention in that run external command section? What should be the output and input?

[BonusCup]

@kunvai1 sorry, this was from around 4 years ago and I've since moved on to another employer and no longer have access to that workflow so I can't refer back to it.  Did you check out the example workflow I provided in one of the posts?