nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

How to connect to SFTP Folder and download files based on the most recent date

tewynn

How to connect to SFTP Folder and download files based on the most recent date and pull that file through the Alteryx flow. I use the Text input + Download tool, but after that I get stuck on trying to pull the file I want from the folder and run it through the Alteryx flow.

Note: I've done this before years ago, but I haven't used Alteryx in depth for quite some time (maybe 3yrs). I very rusty lol

Workflow

Developer

Common Use Cases

Download

Accepted answers

All comments

Hsandness

Hi @tewynn ,

1. Split the Download Data column into individual rows using the Column to Split tool. The delimiter should be \n for new lines.

2. Next, get the three letter word for the current month using the formula tool and filter for records that are only within the current month.

3. Next, do a case sensitive RegEx search for the month and date e.g. "June 2" and sort descending.

4. Last, extract the timestamp in case there are multiple files on a given day and sort by descending on the time stamp.

I'm not sure if dates are displayed the same for everyone so if the month isn't showing as "June 2" for you, you'll have to adjust your RegEx accordingly.

Pablo-GP

Hi @Hsandness,

Thanks for the detailled step by step.

The challenge from my end on the configuration of the Download tool for SFTP access is in how to read the data.

I have been able to establish the connection, and I am able to view the files available in the relevant folder under the DownloadData field.

The step that I haven't been able to achieve yet was the one to open each of the files to extract the data into Alteryx. Which is the appropriate tool that I need to connect to read the files?

Thanks!

apathetichell

you do not open the file and extract. you:

1) identify the location in the directory.

2) use the download tool to download the file locally.

3) use a batch macro to open the file ---> passing in the download location.

4) use the extracted data in your workflow.

you must download the file locally.

Pablo-GP

Thanks, hsandness and apathetichell for the suggestions.

I am still stuck in the process, when I configured the Download tool to download the file available into a file name from a field is giving me the following error. I have checked that the folder is a valid folder, and I have writing permissions on the same. No other parameters have been configured in Headers/Payload/Connection tabs.

The URL is working as if I configured it just to find the file, it is locating the two files that are in the relevant folder

SFTP access to the file is readable

Any idea why this may be happening?

Thanks!

apathetichell

My hunch --- your download location is a location. you need a specific file so {PATH}{FILENAME} you are writing to just {PATH} which is wrong.

don't forget to mark correct solutions.

Pablo-GP

Hi Thank you both @apathetichell and @Hsandness I finally found a way to read the last file located in the SFTP folder. It took me a while, but the solution was on using the download tool twice. One to locate the information in the SFTP folder and one to target the specific.

@tewynn

I am attaching the solution that works for me. You may need to configure to your specifications.

The first download tool is configured using DCM Credentials, and targeting the directory information as an Output To a Field (String)

The second download tool target the specific relevant file in the directory information as an Output to a field (Blob)

The Blob Convert tool will assist you to see the data, mine is working with Text Encode UTF-8

Other steps can differ such as how to split the lines after the blob (Mine was with a page break) and separate the information into columns or how to find the last file in the folder (My files has a timestamp that I can use for the purpose)

SFTP Download Tool Configuration Alteryx Community.yxmd

Quick Links

Popular Tags

This months top contributors

atcodedog05 19598

Qiu 15908

binu_acs 15783

MarqueeCrew 13709

apathetichell 13703