Parse date from a filename and convert MMddyy to a Date?

[anthony123]

Hello,

I need assistance parsing a date string within a filename. It is formatted as MMddyy and I need to convert that to an actual date field.

Example filename: DATA_ALL_ITEMS_012721.csv

Is it possible to extract the date portion using Regex?

Thank you.

[Qiu]

@anthony123 

This should do, just in case you have a 8 digit date, it will also work.

[messi007]

@anthony123,

Please see below :

Hope this helps!

Attached the workflow,

Regards

[neilgallen]

why use two tools when one will do the trick?

a formula tool with:

datetimeparse(

REGEX_Replace([Field],".*?(\d+)\.csv","$1"),

"%m%d%Y")

gets you the same result.

 

[JokeFun]

Hi @neilgallen,

Could you help on this? 

Most of the files are named like "Daily_Mapping_ABC_20210421.xlsm".

But some could be "Daily_Mapping_ABC_20210421.v2.xlsm" or "Daily_Mapping_ABC_20210421.CD2.xlsm" etc.

I tried REGEX_Replace([FileName],"[^\d{8}]","") which however still gets all the numbers. I know left([field],8) should work here. But I still wonder if Regex formula itself can get the numbers with length 8 only. At least the Regex Parse tool can do this.

thanks.

[neilgallen]

assuming you're trying to get the numbers out of the filename, then 

(?:\D+?)(\d+)(?:\..*)

would work, as it ignores any non-digits in the filename until the first number, parses the entire number string until the first period, and then ignores anything after that period.

[apathetichell]

@neilgallen's formula works perfectly but you will have to switch the order in the second parameter of your datetimeparse()

Currently it's set up for a date in DayMonthYear order (ie 05062021) bu the files you are testing on are in 20210506 order - so swap "%Y%m%d" for "%m%d%Y" in datetimeparse.

[JokeFun]

@neilgallen Thanks. This is really cool. Now let me ask one more question. Since different users are processing this folder, the file names could vary. Like

•Daily_Mapping_ABC_20210421.v2.xlsm

•Daily_Mapping_ABC_20210422CD2.xlsm

•Daily_Mapping_ABC_20210423V1.xlsm

So how to just extract the date numbers when it is not always a period after it.

[apathetichell]

datetimeparse(REGEX_Replace([Field1],".*(\d{6,}).*", "$1"),"%Y%m%d")

If it's at least 6 digits that would work. Your datetimeparse might need to be adjusted if it's sometimes follows a different format than yyyymmdd