Delete file from SFTP

[HeinL]

I successfully created a workflow that downloads a file from SFTP.  After processing the file I want to delete the version on the SFTP site.  I copied my download workflow and changed the HTTP Action to DELETE. I add some screenshots of my workflow and some of the settings.  When I run the workflow I get no error messages but the file is not deleted.  I looked for a solution in the Community but can only find mentions of up- and download problems.  Anyone solved this yet?Workflow

Input from Excel

Download Configuration

Download Configuration

[mceleavey]

Hi @patrick_mcauliffe,

Right, I've tried every possible combination, and I've checked what's happening in Fiddler. It's passing the correct information through, and the URL is correctly being split into host and url components. The header request shows as:

The host is correct.

And the log is showing as:

I'm not sure about the handshake thing.

Basically, the workflow runs without errors, I can see the request hitting the sftp server, but nothing's happening.

Stuck!

 EDIT: Hang on. At the risk of sounding like Michael Caine...I've got an idea.

[HeinL]

Hi @mceleavey

I had exactly the same issues.  When I looked at Fiddler I found that it was telling me that DELETE is not a valid option for FTP.  I tried using RM (remove) etc.  I even tried curl.  Nothing worked.  I was told that it might be a problem between Windows and FTP. 

I really hope the problem gets sorted out.

Edit

I did manage to write a command that uses PSFTP which is a PUTTY product.  This can then be run from the Run Command tool.  I posted it earlier but it seems as if my post got lost in cyberspace. 

[mceleavey]

Yeah, I think I could cobble something together using command prompt, but it seems the download tool should just do this.

It's doing nothing for my rage.

[patrick_mcauliffe]

@mceleavey

I'm out of ideas (for now) and don't have an FTP server that I can tinker with currently.

[HeinL]

Hi @mceleavey, @patrick_mcauliffe

Here is a full description of how I got to delete files from SFTP.  I ran this as a .bat as an event in a workflow and with the Run Command tool.  I found the Run Command tool a bit of a pain due to it wanting an input or output.

1. Download psftp from either of the following links depending on the machine’s bit level:
   32bit: https://the.earth.li/~sgtatham/putty/latest/w32/psftp.exe
   64bit: https://the.earth.li/~sgtatham/putty/latest/w64/psftp.exe
2. Copy the psftp file into a directory that is in the machine’s path or to c:\windows
3. Run psftp from the command prompt for the first time just with psftp <user>@<ftp domain> to store the ftp server’s signature in the machine’s registry.
   The user will be prompted with something simmilar and choose y:
   
   The server's host key is not cached in the registry. You
   have no guarantee that the server is the computer you
   think it is.
   The server's rsa2 key fingerprint is:
   ssh-rsa "key"
   If you trust this host, enter "y" to add the key to
   PuTTY's cache and carry on connecting.
   If you want to carry on connecting just once, without
   adding the key to the cache, enter "n".
   If you do not trust this host, press Return to abandon the
   connection.
   Store key in cache? (y/n) y
4. Press CTRL C to break out of this command.
5. Now the psftp command can be run e.g.
   psftp –pw <password> <user>@<ftp domain> -b "c:\temp\commands.file"
6. Contents of Contents of commands.file:
   cd "<dir>" 
   del  <…> e.g. *.txt
   quit
7. The commands file must be referenced with the full path in step 5.
8. Replace all entries between and including <>

I hope this helps.  I followed the instructions one by one and it worked.  

Hein

[mceleavey]

Hi @HeinL and @patrick_mcauliffe,

Thanks for your help on this but I think I've managed to run this using Alteryx without third party software. I created a script which connects via the command prompt which I just trigger with a .bat file. I just need build the workflow to dynamically build the script then I'll post it up here for you guys to have a look.

Once again, thanks for your help.

I think I need to raise the issue with Alteryx because it appears the DELETE function on the download tool simply doesn't work.

M

[AniK]

@mceleavey  @HeinL @patrick_mcauliffe 

Hello All, Were you able to figure out if Delete function is working in Download tool? 

[mceleavey]

Hi @AniK ,

the short answer is it doesn't work in the download tool. This is due to how SFTP works. I achieved it by having Alteryx create and output an echo script which is then triggered by a dynamically created .bat file.

The echo script literally walks through the steps you would use if you manually going to the sftp site via the command prompt. It's much like a macro.

I'm planning on doing a blog/vlog on this because there's a lot of great use cases there, so I'll detail how it all works when I get a bit of time to do that. Sorry I can't be any more helpful at this time.

Look up how to create an echo script to run in the command prompt, that should get you started.

M.

[ninasun]

Hi @mceleavey ,

Do you have a link to the blog/vlog on this? Thanks!

[djodts]

Has anyone had success with this?  I have followed it and nothing happens.  I don't get any errors but at the same time, the files that I am attempting to delete are not deleting.  Other posts have referenced this claiming that it doesn't work and then others clarifying that the DELETE function is for HTTP not SFTP, however I do see that there is a solution. Was this just selected prematurely or does it actually work?  Thanks.