Parse date from a filename and convert MMddyy to a Date?
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
