Date on a CSV as text to convert to a useable fate format

robdmitchell

Hello

I have been given some data to process daily, it has a date column but its formatted as Text, and has a short month, for example it looks like:

01 Jan

Data in DOMO looks like:

There is another column that has the full month name and the year (as a month summary) so i have trued to split out the Short and Date Summary to make something like 01 January 2024 and convert it to a date format but the ETL is throwing up an error saying it cannot convert it.

Is there another way?

Thanks
Rob

ColemenWilson

In MagicETL use the formula tile and create the following formula:

DATE(CONCAT(

-- Year

REGEXP_REPLACE(SummaryDate1, '[^0-9]+',''),'-',-- Month

CASE WHEN REGEXP_REPLACE(SummaryDate1, '[0-9]','') LIKE 'Jan%' THEN '01'

WHEN REGEXP_REPLACE(SummaryDate1, '[0-9]','') LIKE 'Feb%' THEN '02'

WHEN REGEXP_REPLACE(SummaryDate1, '[0-9]','') LIKE 'Mar%' THEN '03'

WHEN REGEXP_REPLACE(SummaryDate1, '[0-9]','') LIKE 'Apr%' THEN '04'

WHEN REGEXP_REPLACE(SummaryDate1, '[0-9]','') LIKE 'May%' THEN '05'

WHEN REGEXP_REPLACE(SummaryDate1, '[0-9]','') LIKE 'Jun%' THEN '06'

WHEN REGEXP_REPLACE(SummaryDate1, '[0-9]','') LIKE 'Jul%' THEN '07'

WHEN REGEXP_REPLACE(SummaryDate1, '[0-9]','') LIKE 'Aug%' THEN '08'

WHEN REGEXP_REPLACE(SummaryDate1, '[0-9]','') LIKE 'Sep%' THEN '09'

WHEN REGEXP_REPLACE(SummaryDate1, '[0-9]','') LIKE 'Oct%' THEN '10'

WHEN REGEXP_REPLACE(SummaryDate1, '[0-9]','') LIKE 'Nov%' THEN '11'

WHEN REGEXP_REPLACE(SummaryDate1, '[0-9]','') LIKE 'Dec%' THEN '12'

END,'-',

-- Day

LEFT(InspectionClosed,2)))

The result:

ArborRose

If the text is consistent….such as Jan always being "Jan"…you could split the values into multiple columns. One for the day and one for the short month name. Then convert the short month into its corresponding full month name, or use some logic in a formula to get the months back into a numeric. Then combine the day, full month name, and year to create a new column such as 01 February 2024. Then do a date convert into an actual date data type.

But it looks like that's what you are already trying. What's the error say?

ColemenWilson

In MagicETL use the formula tile and create the following formula:

DATE(CONCAT(

-- Year

REGEXP_REPLACE(SummaryDate1, '[^0-9]+',''),'-',-- Month

CASE WHEN REGEXP_REPLACE(SummaryDate1, '[0-9]','') LIKE 'Jan%' THEN '01'

WHEN REGEXP_REPLACE(SummaryDate1, '[0-9]','') LIKE 'Feb%' THEN '02'

WHEN REGEXP_REPLACE(SummaryDate1, '[0-9]','') LIKE 'Mar%' THEN '03'

WHEN REGEXP_REPLACE(SummaryDate1, '[0-9]','') LIKE 'Apr%' THEN '04'

WHEN REGEXP_REPLACE(SummaryDate1, '[0-9]','') LIKE 'May%' THEN '05'

WHEN REGEXP_REPLACE(SummaryDate1, '[0-9]','') LIKE 'Jun%' THEN '06'

WHEN REGEXP_REPLACE(SummaryDate1, '[0-9]','') LIKE 'Jul%' THEN '07'

WHEN REGEXP_REPLACE(SummaryDate1, '[0-9]','') LIKE 'Aug%' THEN '08'

WHEN REGEXP_REPLACE(SummaryDate1, '[0-9]','') LIKE 'Sep%' THEN '09'

WHEN REGEXP_REPLACE(SummaryDate1, '[0-9]','') LIKE 'Oct%' THEN '10'

WHEN REGEXP_REPLACE(SummaryDate1, '[0-9]','') LIKE 'Nov%' THEN '11'

WHEN REGEXP_REPLACE(SummaryDate1, '[0-9]','') LIKE 'Dec%' THEN '12'

END,'-',

-- Day

LEFT(InspectionClosed,2)))

The result:

robdmitchell

https://community-forums.domo.com/main/discussion/comment/89647#Comment_89647

This worked a treat, thanks for your answer.