Populating CA CMDB › Federation Adapters › Load CIs from an Excel Spreadsheet

Load CIs from an Excel Spreadsheet

The ability to load CI information from a spreadsheet provides the following:

• A good learning tool for your first use of ADT
• An easy way to load data that was maintained on a spreadsheet on some ones desk.

You can use this adapter to import any data for any family of CI; it can be used as a general framework for importing CIs of any family. Unless you are familiar with defining ODBC tables in an Excel spreadsheet, copy cidata.xls and make all your modifications to this file. If you want to start with a new or blank spreadsheet, perform the necessary insert/name operations in the spreadsheet to define the tables.

To load CIs by using an Excel spreadsheet

1. Navigate to the following location and copy the blank spreadsheet that is included with CA CMDB:

   <root>\cmdb\data\federationAdapters\cidata.xls
   

2. Add or remove rows of data to edit the cidata.xls spreadsheet. If you want to change the order of the columns or remove columns, use the ADT Mapper to scan the new table.

   Important! All cells in the spreadsheet must be formatted as Text. Failure to format all cells as Text can result in incorrect data conversion and corrupted data.

   The cidata.xls spreadsheet has input areas defined for every CI family, and definitions for the following additional tables:

   • Common_attributes

     Lists common attributes which every family includes; the intersection of attributes every family includes.

   • All_attributes

     Lists attributes which one or more families includes; the union of attributes every family includes. When using this adapter, insert rows into the all_attributes data area that is defined within the spreadsheet. Only data in the all_attributes data section are loaded; other data is ignored.

   • Relation

     Enables the definition of relationships between CIs.

   Note: Predefine some field values so they are accepted as valid input. For example, define Contacts and Locations before a CI can reference them.

3. Save and close the spreadsheet.
4. Create an ODBC DSN to the spreadsheet.
5. Change the cmdb_profile_xls profile to refer to the DSN that exists for the CA CMDB Federation Adapter. Specify the DSN name that you created previously in the server tab in the cmdb_profile_xls profile.
6. Review the output table definition in the output table properties to verify the output table destination.
7. Generate and compile the script.
8. Run the compiled script to transform the Excel spreadsheet into an XML document.
9. Use the GRLoader utility to read in the XML document and load in the data.