topic Re: Extracting date from file path in Alteryx Designer Desktop Discussions

Extracting date from file path

rajputakansha — Thu, 07 Nov 2019 21:32:08 GMT

Hi all,

I am trying to extract month from file path so that I can do a padleft and get "08","09","10","11" as months in a new column.

Since the file path can change, I want to extract the month from the end(file name format will always be the same).

[C:\Download\2019 Sales\By months\Inputs\1.30.2019 Sales.xlsx|||`2 - Sales]

Can you please suggest the right/dynamic way to do this?

Thank you!

Re: Extracting date from file path

echuong1 — Thu, 07 Nov 2019 22:09:51 GMT

You can achieve this using regex. In your input data tool, check the box to include the "Output File Name As Field" and have file name only selected.

From there, add a regex tool and tokenize to grab the file name digit portion prior to the first period. From there, you can use a formula tool to pad the numbers if less than two digits. See below for sample configuration and workflow.

Re: Extracting date from file path

BetterFerret — Fri, 08 Nov 2019 19:37:55 GMT

@echuong1 you need a backslash before the period in the regex to escape it's special designation as 'any character'.
@rajputakansha a simpler solution is to use a regex formula PadLeft(RegEx_Replace([FileName],'^.*?(\d+)\..*$','\1'),2,'0')