Connectors

Import from Related Excel Spreadsheets (COBie)

When you need to import data from more than one spreadsheet it may be simpler to set up multiple connectors: one for each sheet. These sheets may be interrelated, which also may not be a problem if those relationships are explicitly specified in each sheet and they are related in a hierarchy. It's just a matter of ordering their execution correctly.

It becomes more complicated when you need to:

• import more than one type of record from a particular sheet.
• fill in data from another sheet based on an implicit relationship.
• import data that has a many-to-many relationship between record types.

The Xls Sheets connector type and accompanying connector rules are designed to handle these scenarios.

The Controller

The Xls Sheets connector specifies the file to be read and functions as a controller for Excel connectors. Each Source Field in this connector is a reference to another connector. The positions of these references determines the order in which they will be interpreted.

Each of these referenced connectors represent an interpretation of a record from their sheet, so one record may be interpreted by more than one connector. The order of interpretation is also determined by the sheet these connectors apply to. If two consecutive connectors (interpretations) use the same sheet, then each record will be interpreted by both connectors before moving onto the next record in that sheet.  If two consecutive interpretations apply to different sheets, then all the records in the first sheet will be interpreted before moving onto the next sheet.

Interpretations

Each Excel connector referred to be the Xls Sheets connector (controller) represents an interpretation of records in a particular sheet. Since these connectors are related to each other by the Xls Sheets connector they can refer to each other in various ways.

• COPY INHERITED VALUE connector rule
  • The last instance of an interpretation of a record can be remembered and used for the current interpretation. This means if a sheet has one record which has an implicit relationship to records in other sheets, it can be referred to this way by providing the connector id and field id for the field to be copied. It also means that two interpretations of the same record can refer to each other, to avoid redundant processing.
• SEGREGATE BY DATA connector rule
  • For many-to-many relationships, the values imported by a previous interpretation can be queried from Archibus and using skip condition rules be selectively associated with a previous interpretation of the current sheet.

Example

There is an example packaged with Web Central. Relative to the Web Central installation, the instructions for setup are in: projects/hq/datafeeds/solutions/connectors/installs/cobie_readme.txt.