Unable to Convert to a Date and Time Format

datadiva
datadiva Member
in Magic ETL

Hi,

I am importing data from a txt file.  I have several columns that are timestamps.  For example, the data is 20160421114552 - meaning 2016/04/21 11:45:52.

When I try to convert this to a date and time in Magic ETL, I select the column, put the new data type as "Date and Time", and select the format with "Year First".  Yet when I preview, I keep getting the message that it "Failed to convert the data" (see attachment).  I'm not sure where I'm going wrong here, but I need these to be timestamps as they are the date ranges for the data and I need to be able to select from them.  What do I do?

Thanks!

Best Answer

  • RGranada
    RGranada Contributor
    Answer ✓

    Ok forgot that...

     

    Yes, you can split all your columns as you need in Magic ETL, leave the dates in the fields as they are, then use a MySql Dataflow to convert the dates, use output dataset of your magic ETL in the MySql Dataflow then use the same code in the output section of the MySQL dataflow. You can convert multiple fields even. if you need to remove the non-converted fields from the final dataset you will have to list all the fields minus the original unconverted ones, something like this:

     

    Select Your_Original_DataSet_Field1, Your_Original_DataSet_Field2,

    Your_Original_DataSet_Field3, Your_Original_DataSet_FieldX,

    STR_TO_DATE(your_text_date_Field_name1,'%Y%m%d%h%i%s')  AS formated_date_Field1,

    STR_TO_DATE(your_text_date_Field_name2,'%Y%m%d%h%i%s')  AS formated_date_Field2, 

    From your_data_set_name

     

    Hope this helps. Don't hesitate to ask if you need more help on this.

    Ricardo Granada 

    MajorDomo@Lusiaves

    **If the post solves your problem, mark it by clicking on "Accept as Solution"
    **You can say "Thank you" by clicking the thumbs up in the post that helped you.

Answers

  • RGranada
    RGranada Contributor

    Hi,

     

    I think you will not easily achieve this in Magic ETL.

    Try a MySql Data Flow before your Magic ETL.

     

    Just add your table to a new data flow and use this code in an output of the dataflow:

     

    Select STR_TO_DATE(your_text_date_Field_name,'%Y%m%d%h%i%s')  AS formated_date, * From your_data_set_name

     

    Hope this helps. Don't hesitate to ask if you need more help on this.

    Ricardo Granada 

    MajorDomo@Lusiaves

    **If the post solves your problem, mark it by clicking on "Accept as Solution"
    **You can say "Thank you" by clicking the thumbs up in the post that helped you.
  • datadiva
    datadiva Member

    Thank you for the suggestion.  But now I have a new problem...when I try to go through a SQL Dataflow, I get the error message that "the row size is wider than MySQL supports".  So would I break out the columns that I need to convert with Magic ETL, then to the SQL, then rejoin them in Magic ETL?  I'm not sure how to approach that.

  • RGranada
    RGranada Contributor
    Answer ✓

    Ok forgot that...

     

    Yes, you can split all your columns as you need in Magic ETL, leave the dates in the fields as they are, then use a MySql Dataflow to convert the dates, use output dataset of your magic ETL in the MySql Dataflow then use the same code in the output section of the MySQL dataflow. You can convert multiple fields even. if you need to remove the non-converted fields from the final dataset you will have to list all the fields minus the original unconverted ones, something like this:

     

    Select Your_Original_DataSet_Field1, Your_Original_DataSet_Field2,

    Your_Original_DataSet_Field3, Your_Original_DataSet_FieldX,

    STR_TO_DATE(your_text_date_Field_name1,'%Y%m%d%h%i%s')  AS formated_date_Field1,

    STR_TO_DATE(your_text_date_Field_name2,'%Y%m%d%h%i%s')  AS formated_date_Field2, 

    From your_data_set_name

     

    Hope this helps. Don't hesitate to ask if you need more help on this.

    Ricardo Granada 

    MajorDomo@Lusiaves

    **If the post solves your problem, mark it by clicking on "Accept as Solution"
    **You can say "Thank you" by clicking the thumbs up in the post that helped you.
  • datadiva
    datadiva Member

    Thank you for the help.  That got the date conversion issue taken care of. 

Categories