Extract Date from File Name

[ydelaney]

Hi, How can I extract the date from a file named "filename dd.mm.yy". I get a new file every day and the only way to identify what day it's for is from the file name.

I've trawled a lot of threads with similar queries and I'm guessing I need to use the Regex tool but I can't get my head around how to write the necessary syntax

[mbarone · Accepted solution]

Hi Yvonne,

Are you bringing the file into the workflow via an Input tool?  If so, you can select the Configuration option "Output File name as Field" and choose field name.  That will create a new column with the file name.  You can then parse out the date.  If the name will always be in the format you describe, then I would simply do a formula tool where the formula is Right([filename],8), call it "date_out_0", and then feed that into a DateTime tool using the custom input as dd.mm.yy.

[Kenda · Accepted solution]

Hi @ydelaney!

It kind of depends on how you want your date formatted once you've extracted it, but you're right in that RegEx is the way to go here. I would recommend using a Formula tool with an expression similar to this:

REGEX_Replace([FileName], "(.*)\s(\d+)\.(\d+)\.(\d+)", "$2-$3-$4")

Otherwise, if you are positive that the date part of the file name will always be 8 characters, you could also try this expression which does not use RegEx:

Right([FileName], 8)

[ydelaney]

Perfect. Thank you so much

[JohnBell]

@Kenda@mbarone

Thanks for sharing! 

Thanks for taking the time to answer @ydelaney question.

This was very helpful to me as well.

Regards,

[tom_montpool]

@mbaroneis correct that you can get Alteryx to add the filename as a field when using the Input Data tool.

In case someone else looks up this post, you can also use the Directory Tool to get a list of files.

You can then parse the filenames as described (using Regex or some other function (e.g. Right()) to get the date.

Then you could convert the date component of the filename to an actual date data type and do things like 'find the file created closest to today's date' and use Dynamic Input to open it.