Error: Failed to parse data

Hulachic5

Aloha All:

 

I have the error: Failed to parse data '43269' as type Datetime for column 'Entry Date'.  When I pulled the data into Excel to look for the error, I could not find it.  This error is on the raw data coming in from Workbench.

 

Any suggestions on how to fix this.  

 

Mahalo.

 

SPM

jaredpk

Hi ... Can you clarify if the error is happening in Workbench prior to upload, or in Domo after the upload.

 

Thanks

 

Jared

---

@Hulachic5 wrote:

Aloha All:

 

I have the error: Failed to parse data '43269' as type Datetime for column 'Entry Date'.  When I pulled the data into Excel to look for the error, I could not find it.  This error is on the raw data coming in from Workbench.

 

Any suggestions on how to fix this.  

 

Mahalo.

 

SPM

---

 

Hulachic5

Hi Jared:

 

It happened after the push from Workbench into Domo.  The data type is set to datetime.  I am unsure how to find and correct that data once it is in Domo; since the file appends.

 

Hope this helps.

 

SPM

jaredpk

Thanks for clarifying.

 

Basically what is happening is that Microsoft Excel encodes dates as the number of days from December 30, 1899 (not sure why they decided on that one).

 

So Excel is converting that serial number into a date for visualizing it in Excel, but Workbench is reading it as a number. 

 

One way to get around it, is to change the datafield in workbench to integer.  Then in a dataflow, add the following logic to convert it to a date:

 

select dateadd(d,`NUMBERFIELD`,'1899-12-30') as Date

 That would add the number of days to Microsoft's starting data to the actual date.