Parse SFTP file listing to columns using RegEx

Hello,

I am new to the community and haven't explored much on using RegEx yet. Looking for help in parsing the below SFTP file listing to columns. I need the file creation datetime and filename from the below listing to identify and download only the latest .dat * .control files.

-rw-rw---- 1 no-user no-group 11048 May 28 11:47 EX_RATES_20190528124700.dat
-rw-rw---- 1 no-user no-group 112 Jun 14 14:30 EX_RATES_20190614033005.control
-rw-rw---- 1 no-user no-group 11059 Jun 14 14:30 EX_RATES_20190614033005.dat

Thanks,

Raj

Regex

Accepted answers

DataKyle

The text to columns tool may be useful here as well.

Set it up as space delimited and you can create the date time from there.

All comments

DataKyle

The text to columns tool may be useful here as well.

Set it up as space delimited and you can create the date time from there.

danilang

Hi @RajR

I agree with @DataKyle. No need for regex here.

Use Text to Columns to parse out your 9 columns. Build the file date from the month, day and time columns. Summarize to get the most recent file

Note that I added in a second input record for the last .dat file with a time of 14:31 to demonstrate the summarize.

This method will have trouble around Jan 01, since the date info doesn't include the year.

Dan

LatestFile.yxmd

RajR

@Danilang and @kyledunn - thank you both for your help and the sample workflow. That worked fine.

Would you know why the download tool output to string for getting file listing from the SFTP server doesn't include the year for the file creation date?

Thanks,

Raj

Quick Links

This months top contributors

SophiaF 432

AkimasaKajitani 390

mceleavey 388

mbarone 337

michael_weaver 335