Import Set field type from Excel

andrewpilachows · ‎11-25-2016

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?

andrewpilachows · ‎11-25-2016

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:

View solution in original post

djoodjii · ‎11-25-2016

Hi,

In the Field Map record for Date/Datetime fields there will be a field visible called "Date format". The default value for this field is actually "yyyy-MM-dd hh:mm:ss" so this is where this comes from. You just have to change the format in the field map to your desired value and it should work.

andrewpilachows · ‎11-25-2016

I have seen where you can set date format in the transform map, is that what you mean by field map? I have seen nothing else to set when using Load Data to put an Excel spreadsheet into an Import Set table. I am not yet at the transform map stage, I need my Import Set table column to be Date/Time.

djoodjii · ‎11-25-2016

So did you load your table(excel table) first with load data?? then you have to create your transform map, and configure it(you will map each field from your source table to your distination table) at this stape if you have a field(type date ) in your distination table(table on servicenow exp: alm_hardware) you will see what I'm trying to explain!!!

please share an exemple of your excel(dont have to be the original) and the your destination table. you can attach a print screen.

andrewpilachows · ‎11-25-2016

Not the destination table, I am still creating the source (Import Set) table. I had SNow itself create the Import Set table based off the Excel sheet.

Excel columns:

Import Set Table: