Using Data Shuttle and having issues with formulas on import.

KarenTF

I'm utilizing Data Shuttle to pull in a data set from an external source. Upon import, the Date Created column shows both the date and timestamp for an entry. Why is it that when I try to create a formula column to pull out just the date (so I can create another column to pull out the Month), it does not work? I keep getting INVALID DATA TYPE.

I tried to get creative and found a formula that would remove the timestamp, but now that column isn't registering as a date column even though the column properties are set as Date. It seems as though every workaround I find still hits a dead end. Any help?

To simplify what I am trying to do: I need to be able to use a formula to derive the Month from the Date Reported column from the import after Data Shuttle, but the column properties aren't cooperating after the import.

Darren Mullen

@KarenTF What is the formula you are using?

KarenTF

Hi Darren, I don't believe the formulas are the problem because I use the same formulas in other sheets and have no issues. It seems that if you have a Date_TimeStamp format of data in an external data source, and then try to Data Shuttle that in, it doesn't register as a Date Column and therefore no formulas want to cooperate.

I even tried to change the column properties to Date, which at first seemed to work because as soon as I changed the column properties (after Data Shuttle import) from Text/Number to Date, it automatically dropped the timestamp and then all the formulas worked. But once the data shuttle ran again, it still maintained the column properties, BUT the timestamp was back and the formulas INVALID.

KarenTF

So far, the only solution I've found is to manually change the column properties after import. Even though I set the target sheet column as a Date column, when the scheduled Data Shuttle workflow imports the data set, the column remains a Date column, BUT still retains the timestamp, which I believe is causing the error. Once I manually change the column properties back and forth, then the timestamp goes away and the formulas correct themselves.

Data Shuttle should have more Column Types as an option to assign during the workflow creation (currently only has Auto, Text, and Number), maybe that would help this issue?

SSFeatures

Hi @KarenTF, can you add a picture of the sheet after it has been imported, but before you manually change the column types?

It seems like the external data source is holding the data in a date format that Data Shuttle doesn't like.

I wonder if we could use a helper column with the DATE function, to convert the data into a date, so that you can use the helper column instead. This way the helper column could be a Date column, and you could use it without needing to remember to manually convert the main column every time that the data shuttle syncs.