Bulk Import Project Tasks from Excel

[David M1]

Hi all.

We are trying to get part of our business out of Excel and into SN ITBM.

The single biggest huddle is that their project plans and all the tasks are in Excel.

How can we do a bulk one time only Import of the Tasks into ITBM Projects from Excel?

We don't want to import into MS Project then SN if we can avoid it as that's just Time/Cost 

There are about a dozen different projects with thousands of tasks, we can use the template feature but first we want to build the templates from the Excel files rather than manually creating thousands of tasks.

Cheers

David

[Naveen Sagar S1 · Accepted solution]

Hi David,

This can be achieved using import sets. You can find more information import sets in the link below.

https://docs.servicenow.com/bundle/london-platform-administration/page/administer/import-sets/concept/c_ImportSetsKeyConcepts.html

First you need to load the data into an import set table and do all the validations on the data (for example: whether if a field is empty you can decide to load it or not). Then you create a transform map to map the columns in the excel sheet to fields in the SN. (which column should go into respective field).

Then later you load the data into your target table, once your validations and transforms is completed.

If you have a personal dev instance then you can try the same as the below link will guide you through a simple hands-on on the above topic.

https://developer.servicenow.com/app.do#!/lp/servicenow_administrator/app_store_learnv2_importingdata_kingston_importing_data_objectives?v=kingston

[MOkeefe12]

Is there an updated link to these instructions. The London document you pointed readers to is not available.

[David M1]

Thank you Naveen, this is what I needed.

If anyone from ServiceNow reads this, it would be great if the official import documentation for ITBM included this Awesome piece of info.

Thanks Naveen.

[mcastoe]

Hi,

Here is an updated link for Import Set concepts: https://docs.servicenow.com/bundle/tokyo-platform-administration/page/administer/import-sets/concept...

@David M1  - Import from excel to load Project can be fairly complex due to the complexity of the PPM data model.  If it is kept simple and all you are doing is loading Project and Project Task, here are a few tips and a general approach to follow.

1. Load in the following order
   1. Project [pm_project]
   2. Project Task [pm_project_task]
2. When loading the data, insure that for each given row in Excel, if something like a User is being referenced, that User is already in the system and the name,  user id or whatever it is that you will be using to reference/identify the referenced record is already in the system and can be looked up by the reference identifier.  
   1. this can be one of the most common issues when importing data
   2. where possible, consider setting a Correlation Id value on the record being referenced and use it in the data being imported to locate the correct record.  Names are a really poor key for lookups.
   3. for example, set the Correlation Id on your Project records being imported and you can easily and reliably reference them.  Same with Task Parents.
3. When loading the Project Tasks you must consider the hierarchy; that is the WBS hierarchy .  Top level tasks, those directly associated to the Project, must load first, followed by the second level, third etc.  I've seen some where the tasks are loaded in layers with an import for each layer.  You can do it all in a single pass if you order the records to be imported by their level in the WBS hierarchy.  Level 0 records first, 1 next and so forth.  Simply, the referenced record has to exist before you can reference it by another record so Parents first. 
4. Be sure to set the Top Project attribute on the Project Tasks.
5. Be sure you use the correct date format.  Import can be horribly picky about dates.

As mentioned, a full end to end import of Projects and tasks can be a huge challenge and that is why we prefer/recommend MS Project import.  However, I do understand it is not ideal at all to have to convert to MS Project only to import.  

hope this helps, if so, please mark helpful,

mark