Regex

[kosikoya]

I'm trying to use Regex in Alteryx Designer and I want to convert dates in an excel file to ISO 8601 format in Alteryx Designer. I want to begin by parsing the field into like a column 1, column 2, column 3 of the dates in mm/dd/yyyy format. Any info on how I might do this would be helpful.

[BrandonB]

Have you taken a look at the Date Time tool? It can take strings and convert them to dates or do the reverse and take dates into string formats as needed. 

https://help.alteryx.com/current/designer/tools/parse/datetime-tool

[kosikoya]

I tried to use that but for example my excel file contains dates in the format of (11.,.-14'--/1954) with different punctuation's in between. I'm trying to get it to read the different punctuation's in between and format it to ISO 8601 in the form like mm/dd/yyyy.

[BrandonB]

Is the punctuation consistent and it is always month, some punctuation, day, some punctuation, and then year? If so, you could use a data cleansing tool and check the box to remove punctuation. Then you could use a date time tool with MMddyyyy to convert it into a date, then another date time tool converting date to string in the format of MM/dd/yyyy. 

[Jonathan-Sherman · Accepted solution]

Hi @kosikoya,

You could achieve this using regex inside a formula tool:

REGEX_Replace([Date String], '(\d+).*?(\d+).*?(\d+)', '$3-$1-$2')

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Regards,

Jonathan

[kosikoya]

I will try that

[aihnen]

If you know all the different date formats you could use REGEX Replace with something like (\d{1,2})[.,-](\d{1,2})['--/](\d{4}). Then replace the different punctuation using marked groups, $1/$2/$3, and then use the DateTime tool to change the metadata. This would also work if you parse by REGEX and then use a formula to add the correct punctuation

Check out the different solutions to Weekly Challenge #4 for inspiration  https://community.alteryx.com/t5/Weekly-Challenge/Challenge-4-Date-Parsing/td-p/36731

Another possible solution would be to parse out the numbers using the TextToColumn tool parsing by all of the punctuation types. Then use a formula to read them back into one column with correction punctuation in between such as "[MonthColumn]+"/"+[DayColumn]+"/"+[YearColumn]". Lastly use the DateTime Tool to change the data type.