How to import date/time values from Excel...

[DarkAvenger]

Hi all,

Earlier I needed help figuring out how to import date/time values from Excel without changing them to some other timezone. In particular I needed the underlying date/time field values in the target table to match the date/time values in Excel.

This issue has been resolved but I thought I leave this here as it may be helpful to others.

The spreadsheet had date/time values that looked like this:

Creation Time: 1/2/2012 8:29

Close Time: 1/2/2012 8:32

What I had tried:

A. Loading the data from the spreadsheet, as it is, into an import table in SN:

As a result I ended up with date/time values that were 8 hours ahead of the date/times in the Excel spreadsheet.

B. Converting the data into text type before loading it into SN:

After this I tried changing the values to text, as follows:

1. added two new blank columns of type Text (also named Creation Time and Close Time).

2. copied the contents of the Creation Time and Close Time columns into Notepad

3. pasted the contents from Notepad into the new columns

4. deleted the original (date/time formatted) Creation Time and Close Time columns.

I then imported the spreadsheet again. This time I ended up with Display Values that matched the Excel values, while the underlying date/times is now 5 hours behind the values in the spreadsheet. I assumed that the 5 hours difference came from my timezone (EST).

C. Used the steps listed in B together with changing my own user timezone to GMT:

Changing my own timezone had no effect on the results (same results as in B).

SOLVED: D. Used the steps listed in B together with a scheduled import set that has the timezone set to GMT:

Here I did as follows:

1. created a Scheduled Data Import record that uses the Import Set created in B as a Data Source.

2. selected Personalize > Form Layout and added "Run as tz" to the Scheduled Data Import form

3. selected GMT as the timezone in "Run as tz"

4. clicked on Execute Now

This actually solved the issue. The underlying display date/time values now matches the values in the spreadsheet.

I would be still like to hear of any other ways to accomplish this though.

[david_legrand]

Hi,

I didn't import dates for a long time but I usually have two options:

1. Transform the Excel column "Date" into a "TEXT" (and so use a formula to transform the dates into text cells because if you don't, you'll just have weird numbers)
2. Use CSV before import

And personally I prefer CSV than excel as the Data Source can only use the "1st spreadsheet" after the first import.

Regards,

[NeilH2]

In the transform map you can specify the date format you are importing as on the field, ServiceNow does the translation into the system format for you as part of the transform.

[david_legrand]

Hi NeiH,

Sure for the datetime format (we can add it's a column not displayed by default into the field mapping), the issue is more on the timezone, lucky you, you are on UTC time

It's not really a ServiceNow bug but it's a misinterpretation between Excel and ServiceNow (if you use the excel date time cells). That is why it's recommended (or at least I recommend) to use text format or CSV

Regards,

[antonymirza]

Hi, We are also facing the same problem while uploading the data from excel file format and it works ok with CSV format. For me, it seems to be a bug in Servicenow.

However, when I follow step B options it corrects, hh:mm:ss value but corrupt the date value.

Here is my testing results (Text format):

Before Load: 21-6-2013 9:55:52

After Load: 04-Dec-0026 09:55:52

Please advise if I am missing something.

Brgds, AM