community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx Designer Knowledge Base

Definitive answers from Designer experts.
#SANTALYTICS

The highly anticipated Alteryx Community tradition is back! We hope you'll join us!

Learn More

Upload to SFTP/FTP

Alteryx
Alteryx
Created on

In a previous article, we've shown you how you can upload to FTP using the Download tool. With the release of Alteryx 10.5, the Download tool now supports uploading to SFTP. With this addition, we'll take the opportunity to show you some more examples of uploading data to SFTP/FTP and show you how seamless it can be.

 

In this article, I'll show two examples of uploading to an SFTP server:

  1. a file from local disk or network share
  2. workflow data directly from within Atleryx as a csv file

If, like me, you don't have and SFTP server to work with but you still would like to test this functionality, you can download and run an SFTP server localy - I'm testing with this free self-contained one. Let's get started!

 

 1. Upload a file from local disk or network share

 

 

2019-06-18_16-10-08.png

 

In the first example, I will point Alteryx to load a file from a path on local disk and use the Download tool to upload it. I start by using a Text Input tool to input the URL (the address of the ftp/sftp server) including the port number and the full file path with the filename of where I want to save the file . I'm running my SFTP server on localhost port 22334 and I want to save my file on the root directory of the SFTP server therefore my URL is sftp://localhost:22334/filename.xlsx. Don't forget to start the URL with sftp:// or ftp:// depending on what protocol the server is running. I also add the file location in the second field. Of course you can use a Formula tool to create more dynamic URLs and file names but I'm keeping it simple for this example. 

 

 

 

2019-06-18_16-04-58.png

I haven't mentioned any special requirements for uploading data to SFTP/FTP so now is a good time to do so. For the Download tool to upload data, the data must be transformed into a Blob object and the tool has to be configured to send a PUT command. I will read-in the file and transform its content into a blob in one go using the Blob Input tool. In the Blob Input tool, I'll put a placeholder file name, configure the tool to Replace Entire Path With Field, and point to the field that has the input file path. The output of the Blob Input tool is a blob object. 

 

 

2019-06-18_16-06-27.png

 

 

 

In the Download tool on the Basic tab, I point the URL to the field that has the server details - "Address" in my case. On the Payload tab, I set the HTTP Action to PUT and set the tool to Take Query String/Body from Field and point it to the field that has the Blob object. On the connection tab, I set the username and password to the correct credentials. Now, I run the workflow and Alteryx will read the file, create the blob object and upload it to the SFTP server. 

My SFTP server root folder is set to C:\temp\sftp and there I can see the filename.xlsx file that I just uploaded.

4.png

 

2. Upload workflow data directly into a csv file on the SFTP server 

 

In the second example, I'll read-in data from an Excel file, deselect some columns and then upload the result into a csv file using SFTP. You can think of the Select tool as the part of your workflow that is processing data and from the Record ID tool to the Download tool as the part to prepare the upload. The upload process is very similar to the first example and the majority of the workflow is for preparing the data and creating the correct object to upload.

 

 

2019-06-18_16-10-42.png

 

 

Now that I have read-in, cleaned, and prepared my data, this is how it looks. 

 

 

2019-06-18_16-08-39.png

 

 

As a reminder, we will need a blob object to feed into the Download tool with the Put command. The next section of the workflow will take the values in the columns and rows of my table and concatenate them into a single field containing comma-separated data. I will feed this field into the Blob Convert tool and the output to the Download tool to upload it to the server. Since I will be transposing and filtering data, I will need to have a point of reference to the original details and therefore I use the Record ID tool to create this point of reference. I then transpose the table using the Transpose tool with the Record ID as a pivot point (the key field) to work with a single column instead of replicating work across multiple columns - in general, transposing data and working with Name-Value columns tend to simplify transforming and manipulating tables. 

 

 

2019-06-18_16-12-25.png

 

 

Now that I have my data in a single column, I will use a Summarize tool to create the CSV rows by grouping on RecordID and concatenating the values with "," as the separator. I will also need to create the first row of the CSV file - the field names - so I take the first Record (Record ID = 1) with a Filter tool and concatenate the values in the Name column with "," as the delimiter. The result is each record in my data has one row of comma separated data all in one column.

 

 

2019-06-18_16-13-21.png

 

 

With the two tables ready, the next step is to union the field names and the values into a single table with the Union tool and making sure that the tool is configured to Set a Specific Output Order. That way you get the field name before the field values in the output. The resulting table is then concatenated into a single field with the Summarize tool using "\n" (newline) as the delimiter.

 

2019-06-18_16-16-16.png

 

 

The resulting data is then fed into the Blob Convert tool to create the Blob object. In addition, the target URL is appended to the table using an Append Fields tool. the Download tool is setup with a similar configuration to the first example.

 

2019-06-18_16-18-05.png

 

2019-06-18_16-19-18.png

 

 

Execute the workflow and, voila, we have the file uploaded to the server as filename.csv.

 

12.png

 

Both the workflow used in this article and the RebexTinySFTPServer configuration file(rename file from .log to .exe.config) is attached to this post. Please note that you must change the configuration of the Download tool (address and credentials) for the workflow to work on your setup. These examples were built with Alteryx 10.5 (10.5.9.15014).

Attachments
Comments

I have several scenarios where this will be helpful! So glad I found this. In earlier versions of Alteryx I had experimented with using the Run command and some freeware apps that did SFTP uploads. If this works it will be MUCH easier.

Alteryx
Alteryx

This is great, thank you, do you know if the only way to upload via the Download tool is to convert the data to a Blob?

Alteryx
Alteryx

@AnthonyJ this is @TashaA's comment on a similar question: 

 

"The help shows that we only accept data from a field that is in BLOB format for the "PUT" request. 

 

Based on my research, this is likely due to the library that we use to execute requests in the Download Tool (libCurl) has specific parameters for using the PUT. One the parameters required is a file, so we use BLOB. Let me know if I can add any additional information. "

 

Thanks @TashaA

Meteoroid

Hello - any thoughts on how to get around the string size limitations? I'm attempting to push a 20 million row output through this SFTP solution and the summarize tool that concatenates all values to a single record (before data is transformed to blob format) is causing the error:

 

Designer x64 Tool #3: Tool #26: Error String size limit reached: Strings are limited to 2147483648 bytes

Alteryx
Alteryx

Hi @-Daniel- for your use case it might be easier to use the Run Command to write out and then upload the file using a 3rd party application. something similar to this post: https://community.alteryx.com/t5/Data-Preparation-Blending/ZIP-and-SFTP-ToFile-ToFileToZIP-ToFileToS...

Woman LOLHeart Thank you so much for this, love it.HeartWoman LOL

Meteoroid

I have a similar situation but the catch is that we never provide Port number while logging into SFTP . Since you also mentioned port number in your address url is it mandatory to provide it?

 

Thanks,

Nancy

Alteryx
Alteryx

Hi @nchikara

 

It is not mandatory to add the port numbers if you're using standard port numbers. Alteryx will assume port 21 for FTP and port 22 for SFTP. If you use different ports then you'll have to add them. 

Meteoroid

Hi @FadiB,

 

Thank you for your reply!

 

Regards,

Nancy

Meteor

Great to find this and it worked for me connecting to a test SFTP with userid and password.  However the SFTP site I need to connect to requires Private Key as part of the authentication - do you know if this is still possible or not supported?

Alteryx
Alteryx

@cbridge, at the moment, the Download tool doesn't support PK certificates for authentication. You might want to try using Winscp with Alteryx, similar to this post: https://community.alteryx.com/t5/Data-Preparation-Blending/ZIP-and-SFTP-ToFile-ToFileToZIP-ToFileToS...

Alteryx Partner

Thank you for this very helpful guide. I have pretty much replicated your method. However I need to upload the file using a specific port and to a specific location: I am currently using the following format for the Address:

 

sftp://ip_address:port/folder/subfolder/filename.csv

 

Is this format correct? I am trying to make sure this format is correct so I can isolate if there is some other authentication problem.

 

Thanks

Atom

 

I have am using the scenario 2 version, but when the file gets to the FTP site it is not encoded as Unicode UTF-8? Has anyone dealt with this before or have any ideas on what I am doing wrong? Below are screen shots of my set up.

 

 UTF-8.PNG

 

payload.PNG

Asteroid

very informative...my issue using the SFTP was getting verification that the file AND data was uploaded.  Unlike when doing a FTP upload and you get the completed transfer verbiage in the cell viewer...uploading to a SFTP site doesn't give you that information.  Working with Alteryx Support, we confirmed that the SFTP upload worked  by downloading the free app "FileZilla" and copying the file that was uploaded  to the SFTP site to my hard drive and opening it there, and validating that the complete data did indeed upload to the SFTP site.

 

 

Hi - will also echo  on uploading to a different folder

 

 …. I need to upload the file to a specific location: I am currently using the following format for the Address:

 

sftp://domain:port/Home/filename.csv

 

~~ however my login for sFTP takes me to the default folder of Root not Root/Home - logicaly I would need to

cd /Home before putting my file and after establishing the sFTP connection with the tool.

 

Thanks

Thank you so Much!!!! Perfect!!!!

 

Best Regards.

 

Carlos Tomé

 I have a workflow that upload the data to SFTP. But does anyone know how to automate this process of loading the alteryx o/p to SFTP. Thanks 

Atom

Hi @FadiB ,

 

I was able to successfully emulate the workflow you've laid out that uploads workflow data directly into a csv file on the SFTP server.

 

However, I'd like to dynamically rename the output .CSV file (that will land in the SFTP) to include the day it was uploaded. 

 

Would you be able to provide direction here? 


Thanks

Asteroid

Hi @FadiB ,

 

I have a file that writes output data to a location dynamically and the extension it has is <File Name>.<Today's Date> and no .xlsx extension. The file name is Myfile.20190529 . Even though the output file has .xlsx extension, using formula tool I get the output file name as Myfile.20190529. This action takes place the day I run the file. So the variable out here is 20190529. It might change to 20190530.

 

With respect to the file you uploaded, could you please show how to dynamically upload the file to a server with extension changed.

For server, I have the user name and password.

Your support will be appreciated.

 

Thanks. 

This great and what I exactly needed. Thank you so much. 

Asteroid

@FadiB Thanks for sharing your guidance. Your instructions was very helpful. I need your thoughts on this. I successfully uploaded but i do not get back any success message. I get a warning saying "No Data Received"

 

Warning: Download (3): No data received from "sftp://<CompanyName>:<Port>/User2.txt"; Check the headers for details.

 

Even the header is empty. Any advise?

Meteor

I'm trying to replicate option 2 to upload data directly to the sftp server but in my specific case I need to do the "ftps" protocol wrapped in tls.  When using the "run" command and WinSCP this meant I had to add the "-explicittls" option.  I can't seem to find or add that analogue here.  Am I missing something?  Thanks, -Hayes

Hello,

 

I tried this option and it worked fine for files which are less than 1 GB but it did not move the files which is of 4GB size. We tried selecting the option to split the blobs into Chunks and workflow was successful but it moved only 1GB of data as the max size of Chunks size in alteryx is 1048576 KB. Is there any other setting which I have to do to SFTP large files ( 4GB  or more )

Appreciate any advice on this.

 

Thanks,

Harish

Atom

Hi!

 

I have a file I have to transfer to an SFTP location M-F, and the file has ~5 million records in it (total file size is around 500MB).  I'm using Option 2 above, and when I get to the BLOB Convert tool, I get an "out of memory" error.

 

Is there an alternate solution to doing a transfer like this for large files?  I'm assuming there has to be a limit on how much a BLOB can hold?

 

I thought about breaking up the file into multiple streams, but I'm not sure how to get all of the streams to append to one another on the SFTP server using the Download Tool, since it doesn't specify file create vs append to file.

 

Thanks in advance!

Kim

Asteroid

Has anyone encountered the following when attempting to upload to the SFTP:

 

SSH-2.0-OpenSSH_7.6
Protocol mismatch.

 

A few things to note:

  • My workflow runs successfully without errors but does not actually transfer data
  • I am positive my credentials are correct
  • The above is what is passed through as a value in the "DownloadData" field after the download tool
  • I know my IP is whitelisted and have proper write permissions (i am able to access the sftp and drop files to it manually via WinSCP)

 

Does anyone have any suggestions for troubleshooting? I also persisted this question on another thread, which i thought had answered before realizing the file wasnt actually dropping

 

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Using-the-download-tool-to-upload-to-S...