Cleaning date column which has text
Hoping someone can help me with this. I want to ingest csv files from our S3 bucket where the first column is the date column. That column also has text in it:
I am also getting this error "Failed to parse text '2/1/23' from column 'Date' as type 'Date'" from one of my files. I've tried fixing the file itself but it's not working.
I tried writing a beastmode to clean this data but not sure how to take care of both issues at once.
Thanks!
Best Answer
-
One of the easiest ways to do this in Magic ETL is to click on your input dataset and click on the data handling section. You can then select what data type you want to change it to. Be sure to click on the wheel cog next to it and in the Bad Values drop down, select Null. This will instruct the ETL to convert the data to date field, when it can, and replace bad values with null.
Another option is to use the Formula tile and use TRY_CAST(yourfield as DATE), which will do the same thing.
**Check out my Domo Tips & Tricks Videos
**Make sure toany users posts that helped you.
**Please mark as accepted the ones who solved your issue.0
Answers
-
One of the easiest ways to do this in Magic ETL is to click on your input dataset and click on the data handling section. You can then select what data type you want to change it to. Be sure to click on the wheel cog next to it and in the Bad Values drop down, select Null. This will instruct the ETL to convert the data to date field, when it can, and replace bad values with null.
Another option is to use the Formula tile and use TRY_CAST(yourfield as DATE), which will do the same thing.
**Check out my Domo Tips & Tricks Videos
**Make sure toany users posts that helped you.
**Please mark as accepted the ones who solved your issue.0 -
Here is a video that walks you through it in more detail.
**Check out my Domo Tips & Tricks Videos
**Make sure toany users posts that helped you.
**Please mark as accepted the ones who solved your issue.1
Categories
- All Categories
- 1.9K Product Ideas
- 1.9K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 303 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 3K Transform
- 105 SQL DataFlows
- 643 Datasets
- 2.2K Magic ETL
- 4K Visualize
- 2.5K Charting
- 771 Beast Mode
- 74 App Studio
- 43 Variables
- 721 Automate
- 185 Apps
- 463 APIs & Domo Developer
- 59 Workflows
- 14 DomoAI
- 40 Predict
- 17 Jupyter Workspaces
- 23 R & Python Tiles
- 402 Distribute
- 116 Domo Everywhere
- 277 Scheduled Reports
- 9 Software Integrations
- 135 Manage
- 132 Governance & Security
- 8 Domo Community Gallery
- 44 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 111 Community Announcements
- 4.8K Archive