How do I get Data Table to recognize dates uploaded by Data Shuttle?

Brian_Richardson

Hi, I'm trying to create a new Data Table using Data Shuttle. I have some columns with date date. It seems that, no matter how I format the dates in the source CSV, Data Table won't recognize them as dates.

Data Shuttle doesn't have an option to set the date format when creating a Data Table. I wish it would.

I know I can then go into Data Table and change the Date type once the table is created, and then it will recognize the dates in whatever format I've uploaded. But I'd like to skip that step as there's many date fields.

I've tried: MM-DD-YYYY , YYYY-MM-DD, MM/DD/YYYY, MM/DD/YY, and YYYY-MM-DD 01:00:00.000 but nothing seems to register. Any ideas, or am I stuck with editing the date fields after creation?

Brian_Richardson

Never mind, I found the solution. If you import dates in the format YYYY-MM-DDT00:00:00Z then Data Table will recognize that format as a date. I also tested syncing to a Sheet and the sheet recognizes that as a date and displays it correctly based on regional setting (mine being MM/DD/YY)

Brian_Richardson

Never mind, I found the solution. If you import dates in the format YYYY-MM-DDT00:00:00Z then Data Table will recognize that format as a date. I also tested syncing to a Sheet and the sheet recognizes that as a date and displays it correctly based on regional setting (mine being MM/DD/YY)

Danielle Wilson

Hi @Brian_Richardson!

Sorry I missed you the other day when you were talking to Grace! I poked into this question with the Product Manager for DataTable and we confirmed that the one date format that is supported by default is yyyy-mm-ddThh:MM:ssZ as you called out. If you change the format of the date to yyyy-mm-dd specially in the "Settings" for that date field, that will also work as a recognized date format!

Your suggestion to put in some guidance to our help documentation is a good one, so we'll work on dropping something into a help article.

Best!

Danielle Wilson

Product Marketing Manager

Smartsheet

Brian_Richardson

Hi @Danielle Wilson sorry I missed you too!

Couple of suggestions related to this:

1) It would be wonderful if DataTable could understand more date formats. There's some common ones like MM/DD/YYYY and YYYY-MM-DD (Smartsheet default), ISO/UTC, and Unix epoch that a lot of tools can produce for importing. Having to use a slightly odd format of YYYY-MM-DDTHH:MM:SSZ adds data transformation steps that aren't really necessary or value-add when trying to just load your DataTable.

2) Related - Data Shuttle should allow date format to be set when creating a DataTable or uploading data. It allows selection of "Date" as a field type, but not the format, so unless the date is in the YYYY-MM-DDTHH:MM:SSZ format already in the source data, you're out of luck and have to adjust the DataTable fields by hand afterward. Which would be ok for a couple, but I have 20 and can imagine even larger tables with 50-100 date fields to manage.

3) Bridge should be able to speak to Data Tables, it would help with pushing changes in rather than having to jump through hoops to get a file out, then shuttle in. Because we'd typically need to process bulk data into DataTable, the API would need to have ability to POST a JSON formatted body with objects, similar to Add Rows, which could be pieced together by Javascript.

4) If Bridge will speak to Data Table, you also should update the Compose Date/Time and Parse Date/Time to provide a result in the format that Data Table is looking for (YYYY-MM-DDTHH:MM:SSZ). Currently they don't produce this format.

(while we're on this topic - if the "checkbox" field could understand yes/no, and if the "number" field could understand to strip currency symbols and commas, that would also greatly streamline the process of loading DataTable and using it)

Danielle Wilson

@Brian_Richardson Thank you for all of the wonderful feedback! I've been digging into this even more this week and found a few more little odd complexities that I'm going to try to work into our Help page documentation - including a few of the ones you outlined! I passed along your feedback to the DataTable and Bridge teams and definitely have a better sense of how some of the date formatting works, so let me know if you have any other questions on this front before we get the documentation updated. :)

uwft

Hi @Brian_Richardson and @Danielle Wilson , I am trying to test this from above...

"Data Table is looking for (YYYY-MM-DDTHH:MM:SSZ)"

I have a Google Drive sheet with data like:

12/18/22 20:19:38

The Google Drive sheet datatable column is set to YYYY-MM-DD.

Google Drive works, although it appears there may be invisible date formatting happening there.

I have a sharepoint csv blob with data like:

12/18/22 20:19:38

The SharePoint CSV's datable "column" ... I have tried YYYY-MM-DD and many others.

The SharePoint CSV doesn't work. DataShuttle can parse it by semicolon separators, but the date as text in this format is not seen as text a date value, no matter the target DataTable field format.

Is there a way to "wrap" or further format the date values I'm misunderstanding?

Brian_Richardson

@uwft if you need the time in there, I think you'll need to ensure the text in your Sharepoint CSV file follows exactly with YYYY-MM-DDTHH:MM:SSZ format. Can you add a field in Sharepoint that uses some formulas to convert your date and time to that format? From 12/18/22 20:19:38 you need to reformat it to "2022-12-18T20:19:38Z"

On the Data Shuttle side, when importing to Data Table, be sure to set the field as a Date field. You cannot pick the format in Data Shuttle.

On the Data Table side, it should see that format as a Date type field, and you shouldn't need to adjust the format.

If instead you're trying to import data that looks like 12/18/22 20:19:38 I don't think you'll be successful in getting DataTable to recognize that as a date. In DataTable you can set the fields to different date formats by opening the table, clicking Settings, and Edit. However you'll notice that the various format options don't include time, except for one - the YYYY-MM-DDTHH:MM:SSZ format. So if your data includes time information, that's your only option for getting DataTable to recognize it.

It's frustrating I know - and Danielle is aware also (Danielle is a Product Mgr for Smartsheet).

uwft

Thanks, @Brian_Richardson - I wasn't able to get it working with time so changed to YYYY-MM-DD and possibly even rebuilt a datatable or two to essentially run the date column "the right way" from the start.

Along the way, I found what Smartsheet determined a bug where a date column with a blank cell value in DataTable appeared to be overlooked or even considered as "Today," that they are working on. I went through my datatable and manually deleted 8000 such rows which resolved it for me.

Sharing is caring. Thanks!

jeslynguo

Hi @Brian_Richardson , I am currently running into the same issue importing my excel sheet as a datatable with Data Shuttle. I have custom set the date type to be yyyy-mm-ddThh:mm:ssZ and Smartsheet is still not recognizing them as dates. For example, when I even just filter <field> <is a date>, it returns 0 rows. I've ensured there are no blank cells too.

Brian_Richardson

@jeslynguo did you set the field to be a Date type field in the import workflow and in the Data Table settings?

jeslynguo

@Brian_Richardson I just figured out the issue. I was setting the date type field in Smartsheet to be yyyy-mm-ddThh:mm:ssZ as well, but it needed to just be a date (yyyy-mm-dd). Thank you for checking in.