Concepts

Tasks

Importing Asset Characteristics using Pivoted Values

When you wish to populate assets in an APM database with asset characteristics, APM data loading provides an alternative to manual data entry: you can import characteristics from a Microsoft Excel® workbook. You can also update existing asset characteristics using the Import Data wizard.

The Asset Characteristic Pivoted Values data loader allows you to import up to 50 characteristics per asset on one row of the spreadsheet. Each row also references a data loader set, which maps a characteristic class to characteristics and specifies the column order.

The import template’s column names are set to default values, for example, “Characteristic 01” and so on. To make it easier to enter information in the spreadsheet, we explain how to create a copy of the template configuration and change the column names to match the characteristics.

Note: To use this functionality in APM, you must first enable feature 120. In the Enterprise window, select the Customization Center view and the Enabled Features tab. Click Browse, select “Asset characteristics pivoted data loader”, and click OK. If APM is running as a smart client, click Refresh Enabled Features on the server. Then restart the client to use the functionality.

While using the Import Data wizard, you can set an option to process data immediately. If no errors are found in the imported data, the data is added to the specified APM site. If you do not select Process Immediately, the job remains in the process jobs queue until processing is initiated.

Processing includes validating the data and generating error messages if warranted. For example, if a mandatory field does not have a value, the error is shown in the staging table. The missing value must be supplied before the item can be added to APM. You can correct a row in the staging table tab and then mark the row as “Ready to Reprocess”. Be sure to mark the process job as “Ready to Process”.

If the process job is not to be processed immediately, it can be validated and processed in separate steps. The job can also be added to a process batch. After validation, it can be revised and reprocessed, if required.

After the information has been processed, an entry appears in the Data Import Requests tab on the Data Management view. The import worksheet and a results worksheet that includes each record’s import status and error, if any, are attached to the import request.

Fields are imported in the following order:

Excel Column Heading	Name in Asset Window	Mandatory?
Site Name	Site	Yes
Asset Number	Number	Yes
Characteristic Set Name	N/A	Yes
Characteristic 01	characteristic name	Yes
Characteristic 02, and so on to 50	characteristic name	No

This topic explains how:

•	To Set up Characteristic Data Loader Sets

•	To Create the Import Template

•	To Import Asset Characteristics

To Set up Characteristic Data Loader Sets

1.	From the Enterprise window, select the Administration menu and then Application Settings. The Application Settings dialog appears.

2.	Select the Characteristics tab and the Data Loader Sets tab:

3.	Click New. The Asset Characteristic Data Load Set Properties dialog appears:

4.	Provide a name for the set.

5.	Select the characteristic class.

6.	For each characteristic, click New. The Characteristic Set Detail Properties dialog appears:

7.	Select a column order number that has not been used in the set. The maximum number of columns is 50.

8.	Select the characteristic. The list is limited to characteristics that have not been used in the set.

9.	Click OK. The characteristic is added to the set.

10.	When you have finished adding characteristics to the set, click OK to close the Asset Characteristic Data Load Set Properties dialog.

11.	Click OK to close the Application Settings dialog.

To Create the Import Template

1.	From the Site window, click on the tool bar. The Enterprise window appears.

2.	Select the Data Loaders view and then the Characteristics tab.

3.	In the configuration list, select “Asset characteristic pivoted values”. This tab displays the staging table of asset characteristics that have been imported, if any.

The configuration’s column names are set to default values, for example, “Characteristic 01” and so on. To make it easier to enter information in the spreadsheet, we recommend creating a copy of the configuration and changing the column names to match the characteristics, as explained next. Be sure to match the column name and order to the information in the data loader set.

4.	Click the Configuration list and then click Copy. The Configuration window appears:

5.	Give the configuration a unique name.

6.	Use in-line editing to change the column names. Select a row and press F2 to open the column for editing:

7.	Enter the characteristic name and press Enter to go to the next row.

8.	Repeat steps 6 and 7 until you have renamed as many columns as needed.

9.	Click OK to close the Configuration window.

10.	In the list of configurations, select your copy.

11.	In the Import list, select Create Import Template.

12.	A new read-only Microsoft Excel worksheet opens, based on the format of the staging table. You can now “save as” the book in the location and with the name of your choice.

13.	Populate the worksheet with data from another source or manually enter the information. This is the source file that you will select in the Import Data wizard.

To Import Asset Characteristics

1.	In the Enterprise window, select the Data Loaders view and Characteristics tab. In the list of configurations, select your copy of the asset characteristics pivoted values configuration.

2.	Click Import. The Import Data wizard appears, showing the File to Import page:

3.	Click the folder icon () to locate and select the file. The source file must be an Excel file with the extension xlsx.

4.	Click Next. The Worksheets page appears:

5.	Set the following options:

Setting Name

Description

Worksheet

The worksheet to use for importing data.

Match columns based on

Specifies how the information in the Excel file is to be matched to fields in APM: • Headings in the first row of the file – Each column heading in the first sheet of the workbook is matched with the property that has the same name. The match is not case-sensitive • Column sequence – Columns on the first sheet of the workbook are matched to properties based on column and property sequence numbers. For example, the first column (Column A) is matched to the Site Name property (sequence number 1). The first row of the worksheet must be populated with values for all of the supported columns.

Note: If you choose to match import worksheet columns based on column sequence, you must confirm that data fields appear in the order expected by the staging table.

6.

Click Next. The Match Columns page lists the properties to import, the columns in the Excel file, and their match statuses. If the matching criteria is “Headings in first row” and a column does not match, the row appears in red. The Match Status column indicates the reason for the error. For example:

Tip: In an error row, you can click the Matching Excel Column cell and press F2 to make the cell editable. Then select a column from the import file to match.

7.	When you are finished, click Next. The Process Details page appears.

8.	Send an email when this job finishes is selected by default. If you would like an email notification to be sent to you or another employee, enter a valid email address in the Email address box.

Note: In order for APM to send automatic email notifications, the recipient’s employee record must record the email address and server email settings must include the SMTP host name. For more information, “Setting up Email” in APM Installation Guide.

9.	Process imported data immediately is selected by default so that the data is validated and processed as soon as it is imported. If this option is not selected, the data remains in the staging table until processing has been initiated.

10.	You can specify the maximum concurrency in the Number of threads to use box. By default, the value is “1”, which means that the data is processed sequentially. You can increase this number to improve processing speed for large jobs on computers with the appropriate capacity.

Note: The recommended maximum is the number of CPU cores minus 1.

11.	Click Finish. A confirmation message appears.

12.	Click Yes. An informational message appears to let you know that the import request is queued for processing. Click OK.

For information about monitoring processing, validating errors, and so on, see Working with Process Jobs.

When processing completes successfully, you can open the Asset window, select the Properties view and Characteristics tab, where the imported asset characteristic values are listed. Click Map to Asset Property to update the asset’s properties.