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

AshfordLC
AshfordLC Member
in Workbench

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?

Best Answers

  • AshfordLC
    AshfordLC Member
    Answer ✓

    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
    AshfordLC Member
    Answer ✓

    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

     

Answers

  • AshfordLC
    AshfordLC Member

    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
    Valiant Coach

    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
    image.png

     

     

     

     

     

    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
    AshfordLC Member

    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
    Valiant Coach

    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
    AshfordLC Member

    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
    Valiant Coach

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

  • AshfordLC
    AshfordLC Member

    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
    Valiant Coach

    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
    AshfordLC Member

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

  • CWebb
    CWebb Domo Employee

    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
    AshfordLC Member
    Answer ✓

    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
    AshfordLC Member
    Answer ✓

    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

     

This discussion has been closed.

Categories