Regex parse Date from Filename

[Nithinpanicker]

Hi,

I am pretty new in regular expression and been having trouble understanding it. I want to parse the date from a file name.

For example:

File name : FFTT INC Created_2023_02_01 

Date format in filename: yyyy_mm_dd

I want to extract the date so I can sort the files by date in alteryx for dynamic input. I have tried sorting with creation date but I have some reports which have been created on same dates but the data is for another date. Hence this request.

Any help is greatly appreciated. Thanks

Nithin

[ShankerV · Accepted solution]

Hi @Nithinpanicker 

(\d{4}_\d{2}_\d{2})

Please find the expected output.

[DataNath]

Hey @Nithinpanicker, would something like this work for you? Don't think there's any need for RegEx unless I'm missing the purpose here. The innermost Right() expression isolates the last 10 characters of the filename (your date). Then, we just convert this into an ISO date that Alteryx can work with (YYYY-MM-DD format) using DateTimeParse() and defining the incoming YYYY_MM_DD format:

[Felipe_Ribeir0 · Accepted solution]

Hi @Nithinpanicker 

One way of doing this:

DateTimeParse(REGEX_Replace([File Name], '(.*)(\d{4}_\d{2}_\d{2})(.*)', '$2'), '%Y_%m_%d')

[DataNath · Accepted solution]

Realised after posting initially that we don't even need to mess around with date functions etc as we're literally just going from YYYY_MM_DD to YYYY-MM-DD and so can simplify this even further with a formula along these lines. The output date type being set to a date will allow you to sort properly:

[Nithinpanicker]

Hi @DataNath @Felipe_Ribeir0 @ShankerV 

Thank you all for your valuable replies.

Yes all the proposed solutions are giving me the desired outcome.