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

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

[Hsandness]

What @apathetichell said. Here's the simplest setup where there's one file and one output file. If you need to save to specific folders that can change you'll have to use the formula tool to update your output location.

The DownloadDirectory field in the screenshot is actually the full file path to the single SFTP file. In your case, you'd also need to reconstruct the SFTP file paths once you get the ones you need. I've never downloaded multiple files at once from an SFTP but if that's your use case you'd have to put the download tool in the batch macro as well and pass it the SFTP file paths and the output file paths where they will be saved on your local or network drive.

[tewynn]

Same here

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