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: