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

Regex

kosikoya
5 - Atom

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.

6 REPLIES 6
BrandonB
Alteryx
Alteryx

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
5 - Atom

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
Alteryx
Alteryx

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

Hi @kosikoya,

 

You could achieve this using regex inside a formula tool:

 

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

 

image.png

 

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
5 - Atom

I will try that

aihnen
8 - Asteroid

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. 

 

 

Labels