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.

[James Bengel]

After literally minutes of research into how Excel serializes dates, it seems the date is the part of the value left of the decimal.  So if you're having a problem making this work it could be that the fix is as simple as changing this line:

var date = dateTime.length == 2 ? dateTime[1].toString() : dateTime.toString();

to this:

var date = dateTime.length == 2 ? dateTime[0].toString() : dateTime.toString();

Tried it in a node session, and the results would seem to support the theory.

u_due_date = 42007.80776
var dateTime = u_due_date.toString().split('.');
var date0 = dateTime.length == 2 ? dateTime[0].toString() : dateTime.toString();
var date1 = dateTime.length == 2 ? dateTime[1].toString() : dateTime.toString();
console.log('serial date [1]: '+date1)
console.log('serial date [0]: '+date0)

> node test_serial_date.js
serial date [1]: 80776
serial date [0]: 42007

[bburdick]

I am still having these issues. I have a date field for a start date for our users. When I import the field into ServiceNow, I get one basically two weird issues.

1. The date that gets sucked in is the wrong date. It gives me a date like 08/01/0197, when the date is 01/29/2012
2. I get an error that says something to the point of: Unable to format 01/29/2012 using format string MM-dd-yyyy for field u_start_date

I have tried importing with xls and csv. And having issues with both. I am not doing any scripting. Other than changing the format of the date format in the Transform Map to MM-dd-yyyy

[rgarland]

We had this problem several years ago as well and came up with the work around below. There have been several Servie-now releases since we came up with this so newer releases may have addressed the issues but here is what we found at that time. It is kind of convoluted and there probably is a better way to do it but it does work and is still working today.

The Service-now system date is a fixed format of yyyy-MM-dd. Our company system defined date field is formatted as MM-dd-yyyy which is different than the Service-now date field. Data imported to any table that has a date field defined as glide_date or glide_date_time (Service-now date fields) will not be formatted correctly.

The fields as created in the import table are created based on the data seen in the spreadsheet. This is really a hold over from the days when an import went DIRECTLY to the target table rather than going into an import table and then transformed to the target. Spreadsheet fields with dates are normally numeric fields with a date format and therefore in the Import Set Table these fields are created as dates. When the data type is glide_date or glide_date_time the import is only going to work correctly if your instances system date format is yyyy-MM-dd. Since our companies system date format is MM-dd-yyyy this will not work properly without performing the actions below. The result will be an Import Set Table where date fields are formatted as strings and then the date fields will be transformed correctly.

You only need to perform these steps one time for each Import Set Table you are creating. Additional imports using this Import Set Table can be done without having to make the modifications again.

1. Create a template spreadsheet that has the fields formatted the way you want them and has sample data in each field. Make sure any date fields are formatted as dates. Dates should be formatted as MM-dd-yyyy which is the way our instances are formatted. Put in one dummy record so the Import Set Table will be created correctly.
2. Load the template spreadsheet data creating the label you will use to identify the import set.
3. After the data has been loaded, refresh the Navigator on the left and go to the Import Set Tables section. Find the import set you just created and click on the name.
4. From the list of records displayed, right click in the list header and choose Personalize -> Dictionary. You will be presented with a list of fields that were created from your spreadsheet. For each date field, open the field record by clicking on the Table name. Click on the Delete Column button. (Note: You cannot just go into the date field and change the type of the date fields to String.)
5. Load your template spreadsheet again using the same import set table name as before. The deleted date fields will be recreated BUT this time as Strings.
6. Cleanup the template import sets by clicking on Cleanup under Import Set Tables. Select the import table you just created and click the Delete data only (preserve table structure) check box. Click on the Cleanup button. Also, go to Data Sources and delete the imported spreadsheet. After the cleanup has completed you can then proceed with a normal data import.
7. Load your import spreadsheet data, create the Transform Map setting the Coalesce and Choice Action fields as needed and leave the date format as defaulted.
8. Run the Transform and the date fields should import correctly. Any new imports using this Import Set table name should work without performing the setup steps again.

[bburdick]

In step 7 are you recommending recreating the entire Transform Map? I did my best to follow your instructions and I get this error now:

Unable to format 01/09/2012 using format string yyyy-MM-dd hh:mm:ss for field u_start_date

[rgarland]

It has been some time since I last did this but yes I think you would recreate the Transform map.