Loading DD-MM-YYYY Date Format from CSV file with Workbench
Best Answers
-
Hi Cody,
Domo Support got back to me and advised that the Schema should be set to STRING and not DATE.
And this (verbatim) note as well:
Note that this is still a string column. You’ll have to convert the data into a date using a dataflow or beastmode on the Domo side.
I don't like it that WB cannot have the final column as a DATE but at least it works now.
1 -
Some follow up on this issue:
The data provider has since decided to also provide time granularity.
Per the documentation, 'hh' should work:
dd-MM-yyyy hh:mm:ss
but it does not! Only 'HH' works:
dd-MM-yyyy HH:mm:ss
1
Answers
-
Setting the field type is actually done in the Schema section of the workbench:
Once in the Schema section, just hit the Data Type dropdown and select DATE:
If you need to alter your date format in the upload, you can choose the "Custom Date Format Transform" and use the following knowledge base article Setting_a_Custom_Date_Format_on_a_Column_in_Workbench_4
Hopefully this gets you going on the right track.
Please let me know if you have any other questions.
Sincerely,
Colt
**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the thumbs up if this post helped you.6 -
Thanks Colt for this info, I did set the type already to DATE in the schema. The link you provided was very helpful, I was able to add a transform. Though now, WB is showing different error:
Could not parse date '9/9/2017 12:00:00 AM' from 'DD-MM-YYYY'
It does not seem right, how does WB changes from how the actual values are in the file:
"09-09-2017"
"11-09-2017"
"12-09-2017"
"13-09-2017"
"14-09-2017"
"15-09-2017"
"16-09-2017"
"17-09-2017"
"18-09-2017"
"19-09-2017"0 -
In testing your scenario, I actually ran into the same error. If I use the following in the Custom Date Format, it then works:
M/d/yyyy hh:mm:ss tt
I'm unsure why it's wanting to convert to a datetime, but this works when I used it for your given date format.
3 -
Thanks I tired that, only I think in my case, I'd need d/M/yyyy, but I tried both and none works
Sounds like a BUG in WB, if you look at the error msg relative to the actual data being passed!
Could not parse date '14-09-2017' from 'd/M/yyyy hh:mm:ss tt'
Could not parse date '14-09-2017' from 'M/d/yyyy hh:mm:ss tt'First, it seems that WB is messing with my input and adding 12:00:00 AM. It looks as if WB is first converting the NN-NN-NNNN to a date given the Schema setting for that column.
2nd, it seems WB is stripping out the LEADING ZEROS from the value(s)
3rd, the failure of parsing '14-09-2017'
0 -
If both your months and days have leading 0's you'll need to use this format:
dd/MM/yyyy
dd/MM/yyyy hh:mm:ss tt
Try one of those and let me know if it's still failing.
Sincerely,
Colt
2 -
I tried 'em both, here's what I get
Could not parse date '9/9/2017 12:00:00 AM' from 'dd/MM/yyyy'
Could not parse date '9/9/2017 12:00:00 AM' from 'dd/MM/yyyy hh:mm:ss tt'It's failing on FIRST line, If you look at the actual values:
1 09-09-2017
2 09-09-2017
3 09-09-2017
4 09-09-2017
5 09-09-2017
6 12-09-2017
7 14-09-2017
8 14-09-2017
9 18-09-2017
10 18-09-2017Compare when I tried the d/M, or M/d gave me a little more milage when it failed on line 7
0 -
It might be the slashes '/' now that I look at it. Maybe swap '-' for '/' ?
2 -
Tried just that's here's the log:
Could not parse date '9/9/2017 12:00:00 AM' from 'dd-MM-yyyy hh:mm:ss tt'
Could not parse date '9/9/2017 12:00:00 AM' from 'dd-MM-yyyy'0 -
Went back through my testing and found I was using Excel previously. When I switched to CSV, I'm seeing the same issues you are.
It does indeed look like something may be off with Workbench and how it processes the dates.
If you end up finding a solution from Domo Support, please let us know.
Best of luck!
1 -
Thanks Colt for all your help, I'll be forwarding this very post to DOMO Support.
1 -
Hi @AshfordLC,
I'm on the Workbench team here in Domo Support. I'd like to investigate this further. Please send me a sample of your CSV data and I will make a recommendation on which configuration to use in the Workbench job. You can send me a private message with your phone number and I can call you to make other arrangements.Thank you,
Cody Webb
Domo Support
1 -
Hi Cody,
Domo Support got back to me and advised that the Schema should be set to STRING and not DATE.
And this (verbatim) note as well:
Note that this is still a string column. You’ll have to convert the data into a date using a dataflow or beastmode on the Domo side.
I don't like it that WB cannot have the final column as a DATE but at least it works now.
1 -
Some follow up on this issue:
The data provider has since decided to also provide time granularity.
Per the documentation, 'hh' should work:
dd-MM-yyyy hh:mm:ss
but it does not! Only 'HH' works:
dd-MM-yyyy HH:mm:ss
1
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 616 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 738 Beast Mode
- 57 App Studio
- 40 Variables
- 685 Automate
- 176 Apps
- 452 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 124 Manage
- 121 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive