Import Set field type from Excel

[andrewpilachows]

Our instance is running Geneva Patch 9.

From System Import Sets, I went to Load Data, Create table and selected an Excel file we are going to be using.   These files are auto-generated daily, so I do not directly have control over the formatting.   The date/time columns may or may not have values in every row, and the cell format is showing Custom: m\/d\/yy"   "h\:mm (i.e. 9/6/16 13:01).   Our SNow system default date format is MM/dd/yyyy.   What I am stuck on is on import, the date/time columns were identified as either Floating Point Number or String and I would like to have the columns as Date/Time.   It is not letting me change the field type presumably because there is an active import set and data in the table at this point (I have not created the transform map yet).   Would the best course of action be to delete/re-create the table column as Date/Time, or is there an alternative that would allow me to correct it?

[ohhgr]

Hi Andrew,

Not sure that would be the best approach for this, but you can always load the data as string and while transforming use script to convert the string values to correct date values.

-Mandar

[andrewpilachows]

If anything that could be a fallback.   Will SNow scripting convert if its being put into the Import Set as a string floating point number and not as a string date format?

[ohhgr]

Hi Andrew,

Excel internally stores the dates as sequential serial numbers with 1st January 1900 as start point. So I believe above day 42678 happens to be 4th November as per this calculation.

So, I think, we can have this calculation done in ServiceNow. But, I agree with you, it should be a fallback. I hope someone who might have faced similar issue could give us a simpler and better way to handle this.

Thanks,

Mandar

[andrewpilachows · Accepted solution]

This was holding me up so I decided to go ahead and try to get a solution worked out.   What I did was first go into Import Sets and delete the import set for this table (which was still pending because a transform had not been run).   A popup verified that if I did this it would also delete the rows from the Import Set Table (which I was aiming for anyway to re-do it).   After the Import Set and the rows were deleted, SNow then opened up all field types so I could change the columns to Date/Time.   I re-loaded my Excel spreadsheet as a new import set and all the date/times were imported correctly.

Import Set Table:

Import Set: