Unable to Convert to a Date and Time Format

datadiva

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!

RGranada

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.

RGranada

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.

datadiva

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

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.

datadiva

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