Downloading Entity Data to a Spreadsheet

You can download entity data from APM to a Microsoft Excel spreadsheet. This data can be all data for all entities of a specific entity type or specific data such as data for only the types of entities, or specific entities or fields you want.

Caution: If the spreadsheet you use for the download already contains data, some or all of the data might be overwritten during the download process. The data that might be overwritten is located beneath the header or token row (whichever row is last). Any data in the rows or columns that are needed to hold the downloaded data is overwritten with new entity data. If the spreadsheet contains data in more rows or columns than are needed for new data, those rows or columns are not overwritten.

Note the following:

  • To download all data for all entities of a specific entity type, you can start with a blank spreadsheet.

  • To download data for specific fields or entities, you do one or more of the following:

    • You can select the fields and entities for which you want to download data when you configure the entity type you are going to download (this option is described later in this section).

    • You can prepare a spreadsheet that contains, at the minimum, a header row with the names of only the fields for which you want to download data.

    • You can prepare a spreadsheet from a template you created with the Data Migrator.

To download entity data:

  1. Access the Select an Action to Perform dialog box. For instructions, see Connecting to the PPM Server using the Data Migrator Wizard (for Entity-Related Tasks).

  2. Click the Download Records icon to download entity data of the type you selected when you connected to the PPM Server.

    One of the following occurs:

    • If the spreadsheet is blank, the Download Options dialog box opens. Go to step 11.

    • If the spreadsheet is not blank, the Map Attributes to Columns dialog box opens. Go to the next step.

  3. Do one of the following:

    • If you are working with a spreadsheet that was created from a Data Migrator template, review the preconfigured entries in this dialog box. If you need to make changes, go to the next step. If the default settings are correct, go to step 10.

    • If you are working with a spreadsheet that you created, go to step 4.

  4. To the right of the Column Header Row field, click the selection icon ().

    The Select a Row dialog box opens.

  5. In the spreadsheet, click a cell in the row that contains the column headings.

    The identifier for the cell you clicked is entered in the Please select the row for field headers field in the Select a Row dialog box.

  6. Click OK.

    The Select a Row dialog box closes, and the number of the selected row is displayed in the Column Header Row field. The headings from the row you selected—with the letter identifying the column they are in as the prefix—are added to the options available in the Unique record identifier drop-down list. The headings are also displayed in the Spreadsheet list.

  7. If the spreadsheet contains a row of tokens, do the following:

    1. Select the (Optional) Token Row checkbox.

    2. To the right of the (Optional) Token Row field, click the selection icon ().

      The Select a Row dialog box opens.

    3. In the spreadsheet, click a cell in the row that contains the tokens.

      The identifier for the cell you clicked is entered in the Please select the row for field tokens field in the Select a Row dialog box.

    4. Click OK.

      The Select a Row dialog box closes, and the number of the selected row is displayed in the (Optional) Token Row field. The Data Migrator automatically attempts to map the spreadsheet columns to the fields in the Entity Type list. Successfully mapped columns and fields are displayed in blue text.

      If you do not want to download data for all of the fields, select the checkboxes for each field you do not want to include and click Unmap.

  8. From the Unique record identifier drop-down list, select the column that contains the request numbers that were assigned to the entities by the Data Migrator when they were created.

  9. Manually map any unmapped columns to fields for which you want to download data. Map the columns in the Spreadsheet list to the fields in the Entity Type list. The list of fields is displayed in the same order—and in the same sections—as the fields appear in the entity. For descriptions of entity fields in the sections and order they appear, see Entities.

    Note: You are only required to map the columns for the fields you want to download.

    For additional information about mapping columns to fields and instructions, see the notes and instructions in step 9.

  10. Click Next.

    The Download Options dialog box opens.

  11. In the Download Options dialog box, select or clear the checkboxes as necessary so only the fields for which you want to download data are selected. The Download Options dialog box shows the mapped fields and their corresponding sections, columns, headers, and data types. The field containing the request number and fields designated as being read-only by gray column headings in the template spreadsheet are not included in this list.

    To clear all of the selected checkboxes, click Uncheck All. To select all the checkboxes, click Check All.

  12. Click Advanced Options to configure download settings. To cancel any changes you make to the Advanced Options dialog box, click Cancel.

    • On the General tab, configure the following settings:

      • In the Highlight Success field in the Coloring box, select the color that the Data Migrator uses in the spreadsheet to show the entities that were downloaded successfully. To change the color, click the Color button and select a new color. To define custom colors, click Define Custom Colors in the Color dialog box.

        By default, entities that downloaded successfully are not highlighted.

      • In the Concurrency box, select the maximum number of concurrent threads—up to 20—from the Maximum Threads drop-down list. When you configure this field, consider the performance objectives for your environment. Setting this field to a higher number could improve throughput but might also put more of a load on your  PPM Server. The default value is 5.

    • On the Data tab, define the delimiters for fields that have more than one value. The default delimiter is a semicolon (;).

      1. In the Set Delimiter field, type the delimiter.

      2. Select the checkbox for each field in which this delimiter is used.

      3. Click Apply.

        The delimiter is displayed in the Delimiter column for each of the selected fields.

      4. Clear the selected checkboxes and repeat step i through step iii to define delimiters for additional fields.

    • On the Filters tab, configure the filters that determine the entities to be downloaded.

      1. If you want to limit the number of entities that are downloaded, select the Enable filtering of records to be downloaded checkbox.

      2. To limit the entities that are downloaded to those containing a specific word in their description, type the word by which the entities are to be filtered in the <Name> contains field.

      3. To display a list from which you can select the entities to be downloaded, select the Manually pick requests to download checkbox. This is a filtered list if you entered a word in the <Name> contains field.

    When you are finished configuring the advanced options, click OK to save your changes and close the dialog box.

  13. Click Next.

    Caution: If you click Cancel before the process is completed, only some of the data might be downloaded. For more information, see Cancelling a Download, Create, or Update Process.

    • If you selected the Manually pick requests to download checkbox on the Filters tab, the Select Records dialog box opens.

      1. Select or clear the checkboxes as necessary so only the entities you want to download are selected. To clear all the selected checkboxes, click Uncheck All. To select all the checkboxes, click Check All.

      2. Click Next.

        The Data Migrator downloads the selected entities and fields.

    • If you did not select the Manually pick requests to download checkbox, the Data Migrator immediately downloads the selected entities and fields.

    The download could take several minutes. When the process is complete, the message <N> of <N> is displayed (where <N> represents the number of entities). This indicates the number of entities—out of the total number of entities that you selected for downloading—that were downloaded.

  14. Click Finish.

    The Data Migrator closes.

  15. Save the spreadsheet.