Parse SFTP file listing to columns using RegEx

[RajR]

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

[DataKyle · Accepted solution]

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

[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