Unable to Convert to a Date and Time Format
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
-
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.1
Answers
-
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.0 -
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.
0 -
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.1 -
Thank you for the help. That got the date conversion issue taken care of.
0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 295 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 608 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 711 Beast Mode
- 50 App Studio
- 39 Variables
- 668 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 45 Workflows
- 7 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 116 Manage
- 113 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive