Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

SFTP Connection, Directory Input, Wildcard File Selection

Jes
8 - Asteroid

I am able to successfully connect to SFTP via using the Text Input + Download File functions but would like to know how to be able to connect to SFTP, show a list of available directories/folders and use wild card formula tool to select a file to download to OneDrive.

 

The current Text Input+Download file forces a user to specify which specific file in the SFTP connection to download. 

Given that the file that I am looking to download has a date stamp that changes daily, I am looking to use a formula tool to identify which specific file to download.

 

I have previous experience performing such a function on files that are stored on shared drives/share point by using Dynamic Input tool and would like to see if there is a similar method for SFTP as well.

7 REPLIES 7
Harbinger
9 - Comet

@Jes you'll likely need to iterate with multiple downloads passing deeper and deeper folder structures. can you share a screen shot of what you're returning with the first output from the download? 

Jes
8 - Asteroid

When using text input & download file, the downloaddata output is a long string without any sort of pattern to how it is pulling the directory's files.

Looking to extract CSV files based on date stamp, which is embedded in the file name.

 

 

 

JH0123_0-1601574974837.png

 

 

 

 

 

PedrodeOl
9 - Comet

Hello @Jes ,

 

If you're using the first download tool with the path until the directory, probably you'll get a confuse list, but with some informations like the upload date or the file names, for example... You'll need some parsing to be able to get the file names only and join them with the directory path to finally pass it through another download tool.

 

Could you share a sample?

Jes
8 - Asteroid

Hi Pedrode,

 

The reason the download icon is looking at a specific directory and not a specific file is because:

 

1. the files in the directory has a date stamp in the file name.

2. Given that the file name has a specific structure, I can parse each file name to extract the date portion from file name.

3. Use the date portion to identify which file in the directory to download.

 

When I use a single download tool to point to a directory, this is the output I get (screenprint below as well).

 

drwx------    4 sftp_prod sftp_prod     8192 Oct  2 11:00 .drwx------   13 sftp_prod sftp_prod      172 Aug 18 08:48 ..-rw-------    1 sftp_prod sftp_prod   917036 Jun 29 10:40 StartofDay_06292020061053_20200629_064005043.csv-rw---

 

The highlighted portions are the individual file names found in the SFTP directory. The output of my download tool is a long string as you can see.

Given that it is currently being output as a long string, it is impossible to use text parse formula to extract the CSV file name. Any suggestions?

 

Alteryx_Download_String.png

PedrodeOl
9 - Comet

@Jes 

 

Could you try:

 

1. Use the data cleasing tool to remove trailing spaces or duplicate whitespaces in the Download Data field

2. Use the text to columns tool and split the information into rows using \s

 

Probably there will be a pattern.

 

Please let me know.

Jes
8 - Asteroid

This worked!!

Cleanse the string, split to rows, filter rows to identify file names only, split to columns (to extract the date from the filename).

Screen print of my workflow below:

JH0123_0-1601926302001.png

 

PedrodeOl
9 - Comet

Well done, mate!

 

Glad that has worked!

Labels