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

Alteryx Knowledge Base

Definitive answers from Designer experts.
New Data Science Blog

Check out the latest post: All Models Are Wrong

READ MORE

Upload to SFTP/FTP

Alteryx
Alteryx

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

 

1.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. 

2.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. 

3.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.

 

5.png

 

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

6.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-seperated 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. 

 

7.png

 

Now that I have my data in a single column, I will use a Sumarize tool to create the CSV rows by grouping on RecordID and concatinating the values with "," as the seperator. 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 concatinate the values in the Name column with "," as the delimitor. The result is a each record in my data has one row of comma seperated data all in one column.

 

8.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 ouptut. The resulting table is then concatenated into a single field with the Sumarize tool using "\n" (new line) as the delimitor.

 

9.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.

 

10.png

11.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) are 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).

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

Meteoroid

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_addressSmiley Tongueort/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://domainSmiley Tongueort/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