I. What Is the CSV Import Feature?

I. What is the CSV Import Feature?

The CSV Import Feature is a Service Manager component that allows users to bulk-import instances of any class typeor projection typedefined in the Service Manager Configuration Management Database (SMCMDB).

This feature can be used to:

• Create Configuration Item or Work Item instances from data stored in a tabular format
• Bulk-edit existing database instances
• Populate the SMCMDB with data exported from an external database
• Circumvent data entry through forms when many class instances must be created at once

To import a set of instances using the CSV Import Feature, two files are required:

1. A data file that consists of a series of comma-delimited object instances. The data filemust end with the ".csv" file extension.
2. A format file that specifies the class type or projection type of the instances present in the data file. Every instance in the data file is assumed to be of the this type. The format file also specifies (1) the subset of properties (and, in the case of projections, components) being imported for the indicated type and (2) the order in which those properties appear (as columns) in the associated data file. The format file must have the same file name as the CSV file that it describes, and it must end with the ".txt" file extension.

II. Walkthrough: Using the CSV Import Feature

Let's examine a typical use of the CSV Importer. Suppose thatwe have an Excel spreadsheet describing the 10 new computers that we have recently purchased.We would like to add these new Configuration Items to the SMCMDB. The entries in our spreadsheet are:

1. Creating the Data File

To use the CSV Importer, we first need to save the spreadsheet as a .csv file. The first row of the spreadsheet is assumed to contain an object instance (and not a header), so we remove this line and save the spreadsheet above (in CSV format) in a file named "newcomputers.csv".

billing-comp-1, 172.30.14.21, DETROIT

billing-comp-2, 172.30.145.213, DETROIT

billing-comp-3, 172.30.145.214, DETROIT

hr-24, 172.115.14.21, DALLAS

hr-25, 172.115.14.22, DALLAS

hr-30, 172.125.30.21, CHICAGO

hr-31, 172.125.30.26, CHICAGO

audit-1, 172.126.15.36, CHICAGO

audit-9, 172.126.15.39, CHICAGO

audit-11, 172.126.15.33, CHICAGO

Figure 1: Contents of "newcomputers.csv" data file

2. Creating the Format File

Next, we must construct a format file suitable for importing the rows contained within newcomputers.csv.

The first step in writing a format file is identifying the class type or projection typeto be used for the instances in the CSV file. Appendix A of this documentation provides the names of classes and projection types and the properties that can be imported for these types (see section IV of this document: Using Appendix A).(Note:The CSV Importer is not limited to importing data in the classes listed in Appendix A. It can also be used to import instances of classes and projection types defined in custom management packs created by the user.)

Scanning the list of classes in Appendix A, we find that Microsoft.Windows.Computer is the most appropriate choice for the object type and property set that we wish to import.We beginby declaring the class of the object being imported:

CSVImportFormat

<ClassType="Microsoft.Windows.Computer"

...

</Class

</CSVImportFormat

Next, from Appendix A, we scanthe available propertiesof the Microsoft.Windows.Computer class and selects those that best correspond to the columns being imported.We settle on the following property mapping:

Column 1 →PrincipalName

Column 2 → IPAddress

Column 3 → DomainDnsName

Using this mapping, we construct the format file as below. The properties in the format file are specified by name in the order in which they appear in the CSV file. We savethe format file with the same file name as the data file but with the "txt" file extension. The format file must be saved in the directory where the CSV file is located.

CSVImportFormat

<ClassType="Microsoft.Windows.Computer"

<PropertyID="PrincipalName"/>

<PropertyID="IPAddress"/>

<PropertyID="DomainDnsName"/>

</Class

</CSVImportFormat

Figure 2: Contents of "newcomputers.txt" format file

Note that any class properties that are marked as key or required (Appendix A makes note of such properties) must be included in the format file definition.Key properties uniquely identify an instance in the SMCMDB; required properties must be set to a non-null value. Furthermore, the values specified for a property in the data file must be of the type expected. The table below shows example values for the possible property types.

Figure 3: Examples of valid values for each property type

3. Importing the Data

With the format and data files prepared, we are ready to use the CSV Import task. To access it, we click on the Administration Wunderbar as pictured below.

Figure 4: Administration Wunderbar Selected, Import Task Visible

We then select the "Import from CSV file..." task in the Tasks Pane (top right). The following dialog appears:

Figure 5: CSV Import File Dialog

We select the newcomputers.csv file and click the "Open" button. Upon doing so, the CSV Import code automatically locates the like-named format file in the same directory and proceeds to import the 10 computers in newcomputers.csv.

These imported computers are now visible in the All Computers view.

Figure 6: Imported Computer Instances Visible in All Computers View

III. Importing Projection Types

In some cases, it might appear as though rows in a CSV file can be imported as instances of a single class when , in fact, they cannot. Consider the case of an incident instance. An incident always includes an affected user, but this affected user cannotbe represented by any one simple property (a string, an int, a bool, etc.).The affected user is really an instance of a separate class with its own set of properties -- a user class -- and the incident classmerely establishes a referenceto that instance. As viewed in our incident form, an incident spans multiple classes and several relationshipsin Service Manager's type system.

Ifwe wish to import an incident with its affected user, then we must somehow define a format file that specifies both the incident and user classes and the "affected user" relationshipthat exists between them. This relatively simple example hints at a more general problem: how do we import classes that have multiple relationships to other classes, and how do we know what classes and relationships to use?

Service Manager simplifies this problem by supporting a kind of data type called a type projection.Type projectionsprovide a convenient way to combineseveral classes and relationships into a single object type. When a projection instance is committed to the SMCMDB, our SDK does the work of creating the individual classes and relationships that compose the instance.

Much can be written about authoring custom classes, relationships, and projection types in user-defined management packs. See the Authoring Guide for additional information.The CSV Importer can be used to import instances of user-defined classes and projections. For the remainder of this section, we'll focus on importing instances of a projection type that ships with Service Manager CTP2: System.Incident.ProjectionType.

1. Importing an Incident Projection

It makes sense to first examine the projection type being used to import the data. Let's assume that the incident data that we wish to import contains only information about the incident itself and the user affected by the incident.

Using Appendix A to determine the name of the type projection, we begin by defining a format file with the projection type only:

CSVImportFormat

<ProjectionType="System.WorkItem.Incident.ProjectionType"

...

</Projection

</CSVImportFormat

Figure 7

We next examine the list of classes available in the incident projection type to determine the objects that we need to import.

2. Specifying the Seed Type

Every type projection definition defines aseed class. The seed class "anchors" the projection to a class in the SM type space and allows the user to reference other class types through specific relationships on that seed class. As per the table in Appendix A, the System.WorkItem.Incident.ProjectionType class may have a seed type of System.WorkItem.Incident and an "AffectedUser" relationship that references a System.Domain.User object. It is this combination of two class instancesand one relationship instance that we wish to import for each instance in our data file.

We add the seed element to the format file as follows:

CSVImportFormat

<ProjectionType="System.WorkItem.Incident.ProjectionType"

<Seed

<ClassType="System.WorkItem.Incident"

...

</Class

</Seed

</Projection

</CSVImportFormat

Figure 8

We must now determine the subset of properties of the System.WorkItem.Incident class to import with each incident projection instance. The syntax for specifying these properties is identical to the syntax used to specify properties for a single class. We add theseto the seed element in the format file as follows.

CSVImportFormat

<ProjectionType="System.WorkItem.Incident.ProjectionType"

<Seed

<ClassType="System.WorkItem.Incident"

<PropertyID="Id"/>

<PropertyID="ContactMethod"/>

<PropertyID="ResolutionDescription"/>

<PropertyID="Impact"/>

<PropertyID="Urgency"/>

</Class

</Seed

</Projection

</CSVImportFormat

Figure 9

Note that "Id" is a key property of the System.WorkItem class, the parent class of System.WorkItem.Incident. When specifying the properties to import for System.WorkItem.Incident, it is important that all key and required properties in the class hierarchy of System.WorkItem.Incident are included. Appendix A is designed to help you identify these key and required properties (they are marked in red).

3. Adding a Related Class

Now we must update our format file to specify that we are adding a separate class instance -- an affected user -- with every incident imported. To do this, we add a "Component" node under the "Projection" node. This is similar to the syntax used to define projection types in management packs.The alias attribute, present in the component definition, identifies the relationship between that component and the seed instance of the projection. This alias name originates in the projection definition. Appendix A contains a list of available components for each projection type.

CSVImportFormat

<ProjectionType="System.WorkItem.Incident.ProjectionType"

<Seed

<ClassType="System.WorkItem.Incident"

<PropertyID="Id"/>

<PropertyID="ContactMethod"/>

<PropertyID="ResolutionDescription"/>

<PropertyID="Impact"/>

<PropertyID="Urgency"/>

</Class

</Seed

<ComponentAlias="AffectedUser"

...

</Component

</Projection

</CSVImportFormat

Figure 10

Now that the AffectedUsercomponent has been specified, we must now supply a class with which to import affected users. An AffectedUser relationship instance relates a System.WorkItemobject to a System.User object. We cannot import instances of the System.User class, however, because it is defined as an abstract class (only its "concrete" child classes can be created). We must therefore use a class like the System.Domain.User class, a derived class not marked as abstract, to create an AffectedUser.

Note that we could have specified any class deriving from System.WorkItem.Incident in the seed class type definition (e.g.: Microsoft.SystemCenter.WorkItem.SCOMIncident, for example).Lists of valid classes to use for seed and component types appear in each projection type entry in Appendix A.

We add the System.Domain.Userclass -- and the properties we wish to specify -- to the projection format definition as follows:

CSVImportFormat

<ProjectionType="System.WorkItem.Incident.ProjectionType"

<Seed

<ClassType="System.WorkItem.Incident"

<PropertyID="Id"/>

<PropertyID="ContactMethod"/>

<PropertyID="ResolutionDescription"/>

<PropertyID="Impact"/>

<PropertyID="Urgency"/>

</Class

</Seed

<ComponentAlias="AffectedUser"

<Seed

<ClassType="System.Domain.User"

<PropertyID="FirstName"/>

<PropertyID="Domain"/>

<PropertyID="UserName"/>

</Class

</Seed

</Component

</Projection

</CSVImportFormat

Figure 11

One subtlety of this updated definition is that we wrapped System.Domain.User class declaration inside of a "Seed" tag. While each "Component" node must have a "Seed" node that identifies the related class type being imported, it may also contain additional "Component" subnodes. These subnodes are used to import class types related to the component's seed.

As an example, if we wanted to import a single file attachment instance and the user that added that file attachment with every incident in the data file, we could add the following nested components:

The syntax would be:

CSVImportFormat

<ProjectionType="System.WorkItem.Incident.ProjectionType"

<Seed

<ClassType="System.WorkItem.Incident"

<PropertyID="Id"/>

<PropertyID="ContactMethod"/>

<PropertyID="ResolutionDescription"/>

<PropertyID="Impact"/>

<PropertyID="Urgency"/>

</Class

</Seed

<ComponentAlias="AffectedUser"

<Seed

<ClassType="System.Domain.User"

<PropertyID="FirstName"/>

<PropertyID="Domain"/>

<PropertyID="UserName"/>

</Class

</Seed

</Component

<ComponentAlias="FileAttachments"

<Seed

<ClassType="System.FileAttachment"

<PropertyID="Id" />

<PropertyID="Extension" />

<PropertyID="Description" />

</Class

</Seed

<ComponentAlias="FileAttachmentAddedBy"

<Seed

<ClassType="System.Domain.User"

<PropertyID="FirstName"/>

<PropertyID="Domain"/>

<PropertyID="UserName"/>

</Class

</Seed

</Component

</Component

</Projection

</CSVImportFormat

Figure 12

4. Ordering Properties in the CSV File

Let's revisit the format file definition in Figure 11that contains only the incident projection type, the incident class, and the affected user related class. Now that the format file is written, how do we order the properties in the CSV file?

The order of properties specified in the CSV file is identical to the order in which they were defined in the format file. That is, the first comma-delimited field should contain a value for the Id property of System.WorkItem.Incident and the last comma-delimited field should contain a value for the UserName property of System.Domain.User. There should be 8 comma-delimited fields total for the CSV Format file in Figure 11.

Together, the example format and CSV files are:

CSVImportFormat

<ProjectionType="System.WorkItem.Incident.ProjectionType"

<Seed

<ClassType="System.WorkItem.Incident"

<PropertyID="Id"/>

<PropertyID="ContactMethod"/>

<PropertyID="ResolutionDescription"/>

<PropertyID="Impact"/>

<PropertyID="Urgency"/>

</Class

</Seed

<ComponentAlias="AffectedUser"

<Seed

<ClassType="System.Domain.User"

<PropertyID="FirstName"/>

<PropertyID="Domain"/>

<PropertyID="UserName"/>

</Class

</Seed

</Component

</Projection

</CSVImportFormat

Figure 13: "newincident.txt"

Inc-{0}, e-mail, resolved on reboot,System.WorkItem.TroubleTicket.ImpactEnum.High,  System.WorkItem.TroubleTicket.UrgencyEnum.High, Bob, DALLAS,  bobsmith

Inc-{0}, voicemail, network error,System.WorkItem.TroubleTicket.ImpactEnum.High, System.WorkItem.TroubleTicket.UrgencyEnum.High, James, DALLAS,  jamesw

Figure 14:"newincident.csv". The symbol indicates that the current line continues on the following line. The file above contains two instances.

Note that when {0} appears in the value of an autoincrement property of type string(here, the "Id" property of System.WorkItem), itwill be replaced with the next available ID.

5. Importing Multiple Related Instances under the Same Projection Component

Some projection types in the SM type system allow a seed instance to be related to multiple class instances under the same component. For example, an incident may reference multiple file attachments (System.FileAttachment) in its "FileAttachments" component.

To import multiple instances under the same projection component, the count attribute is used. Consider the simplified format file defined below:

CSVImportFormat

<ProjectionType="System.WorkItem.Incident.ProjectionType"

<Seed

<ClassType="System.WorkItem.Incident"

<PropertyID="Id"/>

<PropertyID="ContactMethod"/>

<PropertyID="ResolutionDescription"/>

<PropertyID="Impact"/>

<PropertyID="Urgency"/>

</Class

</Seed

<ComponentAlias="FileAttachments"Count="3"

<Seed

<ClassType="System.FileAttachment"

<PropertyID="Id" />

</Class

</Seed

<ComponentAlias="FileAttachmentAddedBy"

<Seed

<ClassType="System.Domain.User"

<PropertyID="Domain"/>

<PropertyID="UserName"/>

</Class

</Seed

</Component

</Component

</Projection

</CSVImportFormat

Figure 15: "multipleattachments.txt"

The format file above indicates that three System.FileAttachment instances will be imported alongside each System.WorkItem.Incident instance.

Since the count attribute of a component node in the format file is set to a value greater than 1, the number of properties expected in each row of the CSV file will be greater than the number of "Property"XML nodes appearing in the format file.

There are 4 properties specified in the format file:

• 1 for System.Workitem.Incident
• 1 for System.FileAttachment
• 2 for System.Domain.User

Since 3 file attachments are being imported (1 property per instance), each with a user (2 properties per instance), a total of 3*(1+2) = 9 properties are expected for the FileAttachments component. Adding the Id property of System.WorkItem.Incident brings the total property count to 10.