Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

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

anthony123
8 - Asteroid

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.

8 REPLIES 8
Qiu
20 - Arcturus
20 - Arcturus

@anthony123 

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

 

0128-anthony123.PNG

messi007
15 - Aurora
15 - Aurora

@anthony123,

 

Please see below :

Hope this helps!

messi007_0-1611820472583.png

Attached the workflow,

Regards

neilgallen
12 - Quasar

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.

 

1d1ca449dff50829d5d14455f482abdd 

JokeFun
8 - Asteroid

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
12 - Quasar

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
18 - Pollux

@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
8 - Asteroid

@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
18 - Pollux

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

Labels