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

robdmitchell
robdmitchell Member
in Magic ETL

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:

summary date.png
short date.png

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

Best Answer

  • ColemenWilson
    ColemenWilson Coach
    Answer ✓

    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:

    Screenshot 2024-02-18 at 4.01.40 PM.png

    If I solved your problem, please select "yes" above

Answers

  • ArborRose
    ArborRose Coach
    edited February 16

    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?

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

  • ColemenWilson
    ColemenWilson Coach
    Answer ✓

    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:

    Screenshot 2024-02-18 at 4.01.40 PM.png

    If I solved your problem, please select "yes" above

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

    This worked a treat, thanks for your answer.

Categories