Importing datetime values from Excel without a transformation

DataSquirrel
DataSquirrel Contributor
in Archive

We've got a bit of a toolchain between some of our data and Domo:

 

Database --> Library --> Excel --> Workbench --> Domo

 

According to the Domo Excel Connector docs, Domo can handle a bunch of date, time and datetime formats automatically:

 

mm/dd/yy
dd-month-yy
weekday-month,dd,yyyy
m
m-dd
hh:mm:ss PM
hh:mm
mm/dd/yy hh:mm PM

 

So far, having no luck with datetimes. Pure dates can come in as dates, pure times can come in as times - but datetimes aren't making it in properly. They're either strings or numbers, not datetimes. To simplify testing this, I've abbreviated the path of the data to:

 

Excel --> Domo

 

I figure that if I can get this working, I can sort out how to do it back in our full environment.

 

Is there a way to set up an Excel worksheet to hold datetime values that Domo can interpret directly without any kind of transformation? The docs say that it's possible but, If not, I guess I'll use a Custom Date Format transform in Workbench instead of a DataFlow or Beast Mode to avoid any confusion in Domo with two different data sets. (We'll never need to chart with the badly interpreted datetime values.)

 

Thank you!

Comments

  • DataSquirrel
    DataSquirrel Contributor

    A bit more detail in case it gives anyone a clue as to what I'm missing. I've done a bit more:

     

    * Set my machine to US regional settings, just in case.

     

    * Hand-coded a converter to produce this format:

    mm/dd/yy hh:mm PM

    This is listed as a supported format from the Domo Excel Connector docs. I'm making sure to force digits in to keep the string lengths the same on every row. So,

    01/01/2017 04:14 AM

    Not

    1/1/2017 4:14 AM 

    I've tried importing the data into Domo directly and it comes through as text. 

    I've tried importing the data into Workbench, and the column is identified as TEXT, not DATETIME.

     

    Within Workbench, I've tried changing the column type to DATETIME and tried doing a custom date transform. Either way, I end up with errors like this:

    Could not parse date '08/11/2017 06:36 AM' from 'mm/dd/yyyy hh:mm tt'

    That looks like a perfect match to me, so I'm obviusly missing something in plain sight. By now, I pretty well figure I'm the obvious, and help from another set of eyes would be much appreciated.

     

    I'd really like to see a format that I can put in Excel that imports directly into Domo as a datetime without any fiddling. Does anyone have some junk data that I could see that imports as datetime?

     

    If not, I'd like to sort out a format that Workbench can retype or, if necessary, transform.

     

     

    Question:

    Is it possible that there's some magical attribute in Excel that's making the difference? For example, an internal type definition on the column? I use Excel very little and know nothing about it's internal formats.

     

    Question:

    If I have to use Workbench, the column retype looks simplest. Does this take special privileges? I've got the rights on the Workbench user dialed way down. For example, I don't think it's got the authority to run transformations at the moment.

     

     

     

  • DataSquirrel
    DataSquirrel Contributor
    Sorry for the typos...it's late here.

Categories