Loading DD-MM-YYYY Date Format from CSV file with Workbench

AshfordLC

Trying to load via Workbench a CSV file (with path to local file).  One of the columns have date values with DD-MM-YYYY format and I wish that the "type" on the dataset shall be Date. Is there a way to use "Transforms" or anything else to load properly in DOMO?

AshfordLC

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.

AshfordLC

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

 

Valiant

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.

AshfordLC

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"

 

Valiant

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.

 

 

AshfordLC

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' 

Valiant

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

AshfordLC

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-2017

Compare when I tried the d/M, or M/d gave me a little more milage when it failed on line 7

Valiant

It might be the slashes '/' now that I look at it. Maybe swap '-' for '/' ?

AshfordLC

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'

Valiant

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!

AshfordLC

Thanks Colt for all your help, I'll be forwarding this very post to DOMO Support.

CWebb

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

 

 

AshfordLC

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.

AshfordLC

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