Importing Date Fields from Excel to ServiceNow Via Transform Map

[jmiskey]

I have an Excel file of about 1300 records and 22 fields, 4 of which are Date fields.  I am trying to import them into a Custom table we have in ServiceNow, and am having problems with the date fields.  It is wildly inconsistent.  One field imports just fine, another imports with the wrong century (i.e. year 1905 instead of 2005), and another won't import at all!  The funny thing is, in ServiceNow, they are all Date fields, and in Excel, they are all set-up the same, so they should all behave consistently.

I have tried using the default "yyyy-MM-dd HH:mm:ss" format on my Transform map.  I have also tried "yyyy-MM-dd" and "MM/dd/yyyy".  None of them worked for all fields.  In Excel, I have tried importing them looking EXACTLY the same as the format on the Transform map.  I have tried having the dates entered as date win Excel, with the desired format.  I have also tried making them text entries instead, in the same expected structure.  I cannot get anything to work!!!

Is there any sort of "best practice" for importing date fields to a ServiceNow table from an Excel file via a Transform map?  I am a wiz with Excel, so I can put the Excel file fields it in ANY format that ServiceNow wants.  I just cannot seem to find one that it likes.  I just need to know what it wants!

Thanks

[Community Alums]

Hi @jmiskey ,

I have faced the same issue in the past, which was resolved by Excel file formatting. 

Date fields can be tricky when importing data from Excel to ServiceNow. Here are some best practices you can follow:

1. Consistent Date Format in Excel: Ensure that all the date fields in your Excel file are in a consistent format. Excel has a tendency to automatically format date fields, which can sometimes lead to inconsistencies. You can set the cell format to ‘Text’ before entering the dates to prevent Excel from auto-formatting them. 

   Common formats include:

   • yyyy-MM-dd: (e.g., 2023-12-12)
   • MM/dd/yyyy: (e.g., 12/12/2023)
   • dd MMM yyyy: (e.g., 12 Dec 2023)

2. Avoid Leading Zeros: Remove any leading zeros from day or month values in your Excel dates. ServiceNow might not correctly interpret dates like "01/10/2023" as January 10th.

3. Date Type: In Excel, ensure the date fields are explicitly defined as "Date" type. Avoid leaving them in a "General" format, which can lead to misinterpretations.

4. Use ISO Date Format: ServiceNow prefers the ISO date format ‘yyyy-MM-dd’. So, it’s a good practice to format your dates in this way in your Excel file.

5. Transform Map Field Type: Make sure the field type in your Transform Map matches the field type in your table. If your table field is a ‘Date/Time’ field, your Transform Map field should also be a ‘Date/Time’ field.

6. Use a Scripted Field: If you’re still having issues, you can use a scripted field in your Transform Map to manually parse the date. Here’s an example:

7. source.u_date_field = new GlideDate();
   source.u_date_field.setDisplayValue(source.u_excel_date_field);

       In this script, u_date_field is the field in your table and u_excel_date_field is the field from your Excel file.

5. Check for Invalid Dates: Sometimes, Excel might have invalid dates (like ‘1900-01-00’) that ServiceNow doesn’t recognize. You can use a script to check for these and either skip them or set them to a default value.

Let me know if you have any other questions. 😊

[jmiskey]

Thanks for the reply.  Some of the things in your list aren't quite consistent, when referring to Excel.  It is helpful to have a full understanding of how Excel actually stores dates.  Excel stores dates as number, specifically the number of days since January 1, 1900.  Time is then just a fraction of a date.  So all the validly entered dates in Excel are numbers with a special date format applied to them (you can easily see this if you enter today's date in, and then change the format to General, you will see Excel will show you the number as it sees it, 45272).

So, if you first format the columns as "Text" before entering your dates, you are NOT actually entering valid dates in Excel (as far as Excel can see) -- you are "Text/String" entries.  If you had try to do any sort of date calculations on them, they would not work, as Excel sees it as just a String, like any other String ("Dog", "Pencil" ,etc).

If you enter the dates as valid dates, as mentioned in step 3, you can change to any date format you want, and Excel will change what is displayed.  But note that only affects the displayed value, not the actual underlying value, which is a number like 45272.

So "dates entered as text" and "dates entered as numbers/dates" are 2 VERY differently things in Excel.  I am trying to determine which one ServiceNow wants.  It is not clear which one you are suggesting, as statements 1 and 3 seem to contradict each other.

You also mention not have leading zeroes, but then ServiceNow prefers the format "yyyy-MM-dd", which seems to imply it wants leading zeroes, or else the format would be like "yyyy-M-d".

So I am not sure that anything is really cleared up.  I still don't know what ServiceNow is expecting from Excel - dates entered as valid dates with a particular format applied, or dates entered as text.  I have no problem doing either one, I just need to know which one it wants.

I guess I will keep testing out until I find the right combination.

[jmiskey]

OK, I did take your advice and use the "yyyy-MM-dd" format, and I tested with both "dates entered as dates" and "dates entered as text", and the good news is, it looks like ServiceNow doesn't care which one you used and accepted both.  Here is a sample of some of the data I am using in testing.  Note that I just copied one date column to the next, to ensure all were formatted the same.

If you are familiar with Excel, you know that the ones that are left-justified are entered as text and the ones that are right-justified are entered as valid dates.

And here are the fields that I am importing these dates into in my Custom table.  You can see that they all have Type of "Date".

And here are the fields on my Transform Map:

So as you can see, all the date fields appear to be set up exactly the same in the table and on the Transform Map.  However, when I run the Transform, only the dates in 2 of the 3 fields are being imported, and I get errors on all the dates from my "DGC Awareness Only" field.  Here is what gets imported:

The errors I get for those 4 records look like this:

Unable to format 2,147,483,647 using format string yyyy-MM-dd for field dgc_awareness_only

I don't understand why only 2 out of 3 worked, when they should all be set-up and entered exactly the same!

What am I missing?

[Community Alums]

@jmiskey ,

ServiceNow prefers dates in the ‘yyyy-MM-dd’ format. Excel should have dates as valid dates, not text. The leading zero confusion arises from the difference in display and actual value in Excel. The underlying value is a number, and Excel displays it based on the format applied.

If possible please provide that Excel file.