Alteryx Designer Discussions

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

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
17 - Castor

@anthony123 

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

 

0128-anthony123.PNG

messi007
13 - Pulsar

@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 

SciencePeak
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
15 - Aurora

@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.

SciencePeak
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
15 - Aurora

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