Worksheets and Fields

About the Data Loader Worksheets and Fields

Field formats for the data loader workbooks carry specific value and cell requirements. This topic contains guidelines for data/time format, units of measure (UOM), and cell color coding.

Important: When using an Excel data loader workbook, to delete rows from the workbook, you must right-click the row number and then select Delete.


Worksheet Field	Description
Cell Color Coding	In order to differentiate between required and recommended field values in the Excel workbook, cells adhere to the following color coding scheme: Red: a required field. Blue: a recommended field. Note: The Column ID and Associated Field ID will both be colored the same.
Cells that Require Logical Values	When a cell corresponds to a field in APM that requires a Logical value, you should enter True or False.
Character Limits in Character Fields	In APM, family fields that have the Character data type (Character fields) restrict values to a maximum length. In the Excel workbooks, when entering data in a cell that represents a Character field, the values must not exceed the character limit of the corresponding field.
only in GEDA fields	These fields are only ingested into the GEDA and can be viewed in the Asset Instance module.
Date and Time Format	Dates should be entered as text in the following format: YYYY-MM-DD hh:mm:ss, where: YYYY is the four-digit year. MM is the two-digit month. DD is the two-digit day. hh is hours in 24-hour notation. mm is minutes. ss is seconds. Note: It is recommended to enter the dates as text in the date fields stored in the data loader workbooks. For example, a complete entry would be the following: 2015-05-01 17:53:00. This corresponds to 5:53 PM on May 1, 2015. Note: Hours must be entered in 24-hour notation (e.g., 17:53:00, not 5:53:00). AM and PM should never be included. Unless otherwise noted in the applicable Mappings Document, times entered should be based on the time zone of the user who will be performing the data load.”
Language	Data loader Excel workbooks are available with field captions in all baseline languages supported by APM.
Reference Sheets	You can insert additional worksheets into your data loader workbook that will not be loaded into APM by encasing the worksheet name in parentheses. For example, if you wanted to add a sheet to record notes on the data loaded into the workbook, you could add a worksheet named (Notes). Because the name of the worksheet is in parentheses, the worksheet will not be loaded into APM.
Units of Measure	In the workbooks, values that correspond to a Unit of Measure (UOM) should be entered based on the current UOM Conversion Set setting for the user performing the data load. For example, if the UOM Conversion Set for the user is Metric, metric values should be entered in the Excel workbook. In cells that correspond to a field that uses a UOM, you should not enter the actual UOM. For example, you should enter 10, not 10 Inches.

About the Configuration Worksheet

The Configuration Worksheet is a common worksheet, which can be implemented by each of the data loaders. It allows you to control aspects of how your data is loaded such as batching and which other worksheets should be processed.

Note: The Configuration Worksheet cannot be used by the Failure Modes and Effects Analysis (FMEA) Data Loader, the Reliability Centered Maintenance (RCM) Data Loader, Thickness Monitoring (TM) Equipment Data Loader, or the Thickness Monitoring (TM) Functional Location Data Loader.

The Configuration worksheet is needed to describe the type of data that you will be loading and how that data should be handled during the data load.


Worksheet	Description
Configuration	The Configuration worksheet is needed to describe the type of data that you will be loading and how that data should be handled during the data load.

Configuration Worksheet

Note: For the Unified Asset Ingestion Data Loader, the only supported actions are ACTION_INSERTUPDATE, ACTION_LOCATE and ACTION_NONE.


Field Caption	Field ID	Data Type (Length)	Comments
Number of Rows to Chunk	OPTION_NUMBER_ROWS_TO_CHUNK	Character	Option to break-up or chunk data.
Batch Size	BATCH_SIZE	Character	Modifying this field is required to determine the number of records processed in each batch. Enter the batch size you want, and the Data Loader will process that many records per batch. For example, if you want to use a batch size of 100, enter 100, and the Data Loader will process 100 records per batch. Note: The recommended batch size is 100. If the Batch Size column is removed from the source workbook, the data loader will default to a batch size of 100. In addition to processing the data in batches, the log file reports progress by batch.
Data Worksheet ID	DATA_WORKSHEET_ID	Character	This column contains the name of the <data> worksheet where the actual data is located. It needs to have the same name as the <data> worksheet in the data loader workbook.
Load Data From Worksheet	LOAD_DATA_WORKSHEET	Boolean	Identifies if data from the corresponding worksheet identified in the Data Worksheet ID column will be loaded or not. True: The corresponding worksheet will be processed. False: The corresponding worksheet will not be loaded into the Meridium database.
Primary Family ID	PRIMARY_FAMILY_ID	Character	Depending on the type of data that you are working with, this will contain the Relationship Family ID or the Entity Family ID where the data is being ingested. If the Family ID in the APM metadata contains spaces, you must specify this value in < >.
Primary GEDA Type Name	PRIMARY_GEDA_TYPE_NAME	Character	The value represents the type of the data in the corresponding worksheet. For example, you can define the basic types as generic-enterprise-type generic-site-type generic-segment-type generic-asset-type <complex>-asset-type. The field will be <none> when the Family type is Relationship.
Primary Family Key Fields	PRIMARY_FAMILY_KEY_FIELDS	Character	This column contains the Field IDs associated with the Primary Family that are used to uniquely identify a record. If more than one field is to be used, each Field ID needs to be separated by a \| (pipe) character. If you are loading data into a relationship and no keys fields exist or are used, then use the <none> constant. If the Primary Action are ACTION_INSERTUPDATE, ACTION_LOCATE and ACTION_NONE, then no key fields need to be specified, and you can use the <none> constant.
Family Type	FAMILY_TYPE	Character	The value is the column should be Entity or Relationship depending on the type of data that is being loaded.
Predecessor Family ID	PRED_FAMILY_ID	Character	When the Family Type is Relationship, this column will contain the value of the Entity Family ID that acts as predecessor of the relationship. It should contain the <none> for the Entity FAMILY_TYPE row.
Predecessor GEDA Type Name	PRED_GEDA_TYPE_NAME	Character	When the Family Type is Relationship, this column will contain the value of the type of the Entity Family ID that is the predecessor in the relationship. Otherwise, it should contain the <none> constant.
Predecessor Family Key Fields	PRED_FAMILY_KEY_FIELDS	Character	This column contains the Field ID or IDs associated with the Predecessor Family that are used to uniquely identify the predecessor record. If more than one field is to be used, each Field ID needs to be separated by a \| (pipe) character. If the Predecessor Action is ACTION_INSERTONLY, no key fields need to be specified, so you can use the <none> constant.
Successor Family ID	SUCC_FAMILY_ID	Character	When the Family Type is Relationship, this column will contain the value of the Entity Family ID that acts as successor of the relationship. It should contain the <none> for the Entity FAMILY_TYPE row.
Successor GEDA Type Name	SUCC_GEDA_TYPE_NAME	Character	When the Family Type is Relationship, this column will contain the value of the type of the Entity Family ID that is the successor in the relationship. Otherwise, it should contain the <none> constant.
Successor Family Key Fields	SUCC_FAMILY_KEY_FIELDS	Character	This column contains the Field ID or IDs associated with the Successor Family that are used to uniquely identify the successor record. If more than one field is to be used, each Field ID needs to be separated by a \| (pipe) character. If the Successor Action is ACTION_INSERTONLY, no key fields need to be specified, and you can use the <none> constant.
PrimaryAction	PRIMARY_ACTION	Character	The value in this column will determine the action that will be applied to the Primary Family records. If the Family Type is Entity, the possible values are: ACTION_INSERTUPDATE ACTION_LOCATE ACTION_NONE Deleting a record and purging a record will both delete the current record, the difference being that the purge action will delete the record and all the links or relationships tied to that record. The delete action will simply attempt to delete the record, and if it is related to another record, the delete will fail. If the Family Type is Relationship, the possible values are: ACTION_INSERTONLY ACTION_INSERTUPDATE ACTION_UPDATEONLY ACTION_DELETE
Predecessor Action	PRED_ACTION	Character	The value in this column will determine the action that will be applied to the Predecessor Family records. The possible values are: ACTION_INSERTONLY ACTION_INSERTUPDATE ACTION_UPDATEONLY ACTION_DELETE ACTION_PURGE ACTION_LOCATE If the family type is Entity, the value needs to be: ACTION_NONE
Successor Action	SUCC_ACTION	Character	The value in this column will determine the action that will be applied to the Successor Family records. The possible values are: ACTION_INSERTONLY ACTION_INSERTUPDATE ACTION_UPDATEONLY ACTION_DELETE ACTION_PURGE ACTION_LOCATE If the family type is Entity, the value needs to be: ACTION_NONE
Insert with Null Values?	OPTION_INSERT_ON_NULL	Boolean	When setting the field values on a new record, if a value coming across is NULL, the field values will be set to NULL if this option is set to True.
Update with Null Values?	OPTION_UPDATE_ON_NULL	Boolean	When setting the field values on an existing record, if a value coming across is NULL, the field values will be set to NULL if this option is set to True.
Replace an Existing Link?	OPTION_REPLACE_EXISTING_LINK	Boolean	The Replace Existing Relationship option is used to determine how a relationship is to be maintained by its cardinality definition. For example, the relationship Location Contains Asset is defined in the Configuration Manager. It has a cardinality defined as Zero or One to Zero or One, has a Location LP-2300, and contains the Asset P-2300. If in the data load, you assign the Asset P-5000 to be contained in the Location LP-2300, and you have set the Replace Existing Link property to True, the data loader will link P-5000 to LP-2300 and unlink P-2300 from LP-2300. This assumes that the P-5000 is not currently linked to another location. The same is true for a relationship that is defined as Zero or One to Zero or Many, or Zero or Many to Zero or One.
Allow Change of Family?	OPTION_ALLOW_CHANGE_OF_FAMILY	Boolean	Allows the data loader to move an entity from one family to another. For example, this would allow an entity that is currently assigned to the Centrifugal Pump family to be moved to the Reciprocating Pump family. All relationships will be maintained as long as the family to which the entity is being moved allows the same relationships. Note: Because of the extra processing required, by selecting this option, the interface performance will decrease.