How do you import date fields from excel?

[#1]

I've been sifting through the Importing articles on the wiki, and trying various settings, but I'm still not having any luck. I want to import a number of tasts and set the Opened date field.

In excel, I've tried using various date formats, the General cell format, and the apostrophe ' prefix in the cell. Whenever I run the import it sets it to a random date like year 0099 AD etc.

[grm1973]

What if my CSV has the data in the format DD-MON-YYYY (e.g. 24-NOV-2011) .. Will it work? I tried setting the transform map date format to dd-MMM-yyyy but it didn't work very well. The error that I got was ....

Unable to format 2011-11-24 using format string dd-MMM-yyyy for field u_effective_end_date

The CSV had the data in the format 24-NOV-2011. So somehow it converted and understood that it is 2011-11-24, but was unable to store this value in the field u_effective_end_date

Please help!

-- GRM

[grm1973]

Hi All,
Ignore my previous post. It works well with the format dd-MMM-yyyy

I had some other script active as a part my R&D. After deactivating that script and setting the format .... data is uploaded properly.

-- GRM

[Valor1]

I was running into this same problem today..
It looks like we have improved our import processor to handle this better. The problem I was running into was when I created my import table from my excel spreadsheet, was that it actually recognized that the column in my Excel sheet "Due date" was a date-type field in Excel, so that's the field type it used in the transform temp table (date ONLY). However, when I ran the actual transform, the target field type was a date-time, and the system was expecting a date-time, not a date.

Once I changed my transform map date format (like you mentioned), to match the system date-only format, "yyyy-MM-dd" the import ran fine.

That being said, if you are still having issues, you might try unmapping the rows, and running an onBefore script (I haven't tested this, so YMMV):

// we have the date/time from Excel in its serial format: daysFrom1900.decimalOfDayElaspsed

// first, make sure it's a string, then convert to array if it contains a decimal (means it has both date and time
var dateTime = source.u_due_date.toString().split('.');
// to grab the date only, split the time decimal off if it has one
var date = dateTime.length == 2 ? dateTime[1].toString() : dateTime.toString();
var dateInt = parseInt(date,10); // convert to an integer
gs.log("date variable typeof: " + typeof dateInt); // log to just to make sure we have an integer
dateInt -= 25569; // number of days between 1/1/1900 and 1/1/1970
dateInt = date*24*60*60*1000; // multiply the difference by 24hrs, 60mins, 60secs, 1000millisecs
// we now have the number of milliseconds from 1970, and we can set it to the target.
gs.log("date value, after millisecond from 1970 conversion: " + dateInt);
target.due_date.setDateNumericValue(dateInt);

***Please let me know if this doesn't work***
You could make this a generic function, and expand to handle the time as well. Currently (if it works at all), it would only handle a date.

[sathishk]

Hi Valor,

I have been working on transform maps, in excel I'm having one field with general format which is actually a date. On loading excel data into snow, I need to automatically convert the value in excel sheet into date/time format in the incident form(It may be customized field also).

Entered_Date

40723.71736

40731.90556

40793.37917

I have used the Transform script which you've mentioned here: "

https://community.servicenow.com/message/626868#626868

". But now my issue is, the date displaying in my customized field is like "04-01-1970" instead of "07-07-2011". Can you let me know how to display the exact date.

FYI, I've changed the number of days in the script line no. 9

[Valor1]

Try setting the date format of the user importing (there's a date format field on the user table, if you didn't know) to system default "yyyy-MM-dd", logging out then back in and attempting your import again.

If this works, it's a ServiceNow bug.