-
Notifications
You must be signed in to change notification settings - Fork 19
cable import script developer notes
This page describes the Python script created to assist with importing cable plant data to CDB. The script reads a "Kabel workbook", an Excel workbook created by the R&I team, performs validation, and generates an output workbook with multiple tabs for importing data to CDB (and comparing kabel workbook data to items that already exist in CDB). The R&I team is creating many of these workbooks, each containing a subset of the cables for a particular technical system. Dam Stasic is the kabel workbook owner/expert.
The code is contained in a single Python file for simplicity. As requirements have evolved, the number of classes has grown and it is fairly large. It could be broken out into separate modules if we wanted to create other Python scripts with similar capabilities.
The main purpose of this document is not to give comprehensive documentation for the script, but it is intended to identify the import classes in the script and point a developer in the right direction for making some change in functionality.
The script is located in the ComponentDB github repo in the directory tools/developer_tools/utilities/cable_import
This wiki also contains a couple of documents intended for script users. The first gives details about running the script including command line parameters and config file options. The second was created for Ned to describe how to set up a pycharm project for running the script.
Before we dive into the script, it is important to say a bit more about the kabel workbook input file, how it is processed by the script, and the contents of the output workbook.
The kabel workbook is an Excel (xlsx) file that contains multiple sheets:
-
Cables - contains one row per cable, including identifiers, cable type, and information about the endpoint devices
-
CableTypes - contains a column for each technical system owner (e.g., Controls, Diagnostics, Information_Tech, etc) with a list of the valid cable type names for that technical system
-
CableSpecs - provides a row for each cable type listed in the CableTypes tab with information about that cable type such as manufacturer, part number, and physical properties of the cables that are important for routing. This sheet is currently very sparsely populated.
-
Notes - used by Dam and the cable owner to capture relevant notes
The remaining sheets define locations within the facility for endpoint racks and devices, and are used to provide constrained selection menus for locations in the Cables sheet. These menus use the Excel "named range" mechanism. The sheets include:
- SR_Mezzanine
- SR_Room
- SR_Tunnel
- SR_Utility
The primary sheets of interest to the script are "CableSpecs" and "Cables". That is, we ultimately want to import each row in the CableSpecs sheet to the CDB cable catalog, and each row in the Cables sheet to the CDB cable design domain. The architecture of the script reflects this, and will be discussed in more detail below. The "CableTypes" sheet is parsed to validate the legal cable types for the specified technical system, and the other sheets are not parsed directly, but accessed as Excel named ranges to validate location details in the Cables sheet.
As detailed in the link for running the preimport script, the script is run for a particular technical system (e.g., "Controls" or "Diagnostics").
The script finds cable types for the specified subsystem in the CableTypes tab, and then uses that information to locate and process those cable types in the appropriate section of the CableTypes tab. It also retrieves information using the CDB API about existing sources, connector types, and cable types so that it can identify items that already exist in CDB and those that don't. It uses this information to generate tabs in the output workbook formatted for importing connector types, sources, cable types, and cable catalog connectors to CDB.
It then reads all the rows in the Cables tab, performing validation of cable type and location details as it goes. The script retrieves information using the CDB API about existing cable design items, machine design endpoint devices, catalog items and port information. It uses this information to generate tabs in the output workbook for importing catalog item ports, cable inventory items, and cable design items to CDB.
As mentioned in the previous section, the output workbook generated by the script includes a number of sheets, including:
-
CableSpecs Sheet Summary - Summarizes processing of the "CableSpecs" input sheet. The first column "summary messages" includes number of rows in input sheet, number of connector types that exist in CDB or are newly encountered, number of existing/new source items, and number of existing/new cable types. There are additional detail columns with the names of the existing/new items when non-zero.
-
Connector Type Import - CDB import sheet for any new connector types (those not already in CDB)
-
Source Item Import - CDB import sheet for any sources (manufacturers) not already in CDB
-
Cable Catalog Item Compare – CDB import sheet for comparing cable catalog items that already exist in CDB with the kabel workbook cable type of the same name. This sheet can be imported to CDB using "compare" mode to highlight the differences between the kabel workbook and CDB cable types.
-
Cable Catalog Item Import – CDB import sheet for creating the cable types that don’t already exist in CDB
-
Cables Sheet Summary – summarizes processing of Cables tab from kabel workbook. First column "summary messages" includes number of rows in input sheet, number of new cable design items for import to CDB, cable types not defined in CDB (and included in the import sheet above), and warnings about port values ignored in input processing.
-
Catalog Port Import – CDB import sheet for all ports used in the Cables sheet
-
Cable Inventory Item Import - Contains cable inventory items, one per cable design item in the cable design import sheet. Cable inventory items are named using the corresponding cable design item's name. If cable inventory items will be imported, it would be a good idea to fill in the cable design item import sheet's "Assigned Inventory Tag" column with the inventory unit names, which should be a simple matter of copying the values in the "Name" column to the "Assigned Inventory Tag" column.
-
Cable Design Item Compare - This sheet contains one row for each cable design item name from the input sheet that is found to exist in CDB. This sheet can be imported to CDB using "compare" mode to highlight differences between the kabel workbook and CDB items.
-
Cable Design Item Import - CDB import sheet for importing cable design items. This now includes all items from the Cables tab whether or not they exist in CDB. The user will decide how to handle cables that already exist in CDB.
This section describes the import classes and other features of thee import script. The subsequent section provides details about the use of those classes to process the kabel workbook and generate the output workbook.
This is the most important class in the import script framework. InputSheetHelper subclasses read a sheet from the input workbook row by row, perform validation, and organize information for use in generating sheets in the output workbook. The role of the two subclasses, CableSpecsSheetHelper and CablesSheetHelper, is described in more detail in the subsequent section.
Some methods that might be overridden in subclasses include:
-
set_config_preimport(), set_config_workbook(): called to handle processing of config file options
-
sheet_name(), sheet_number(): return name and index of sheet in input workbook
-
item_name(): returns name of primary CDB domain handled by helper
-
get_header_row(), get_first_data_row(), get_last_data_row(): used to return row numbers for processing the input sheet
-
generate_input_column_list(): returns list of InputColumnModels for input sheet format (more details below)
-
generate_output_column_list(): returns list of OutputColumnModels comprising the output sheet format (more details below)
-
generate_handler_list(): returns list of InputHandlers to assist with processing spreadhseet rows (more details below)
-
pre_initialize_custom(), initialize_custom(): called before and after determining the first and last data rows in the sheet to perform initialization by reading data from input sheets or fetching data from CDB APIs
-
handle_valid_row(): called after processing a row that is determined to be valid
-
input_is_valid(): called after processing the input sheet to determine if processing of the sheet is valid
-
get_summary_messages_custom(), get_summary_sheet_columns(), get_processing_summary(): used to generate content for the input sheet's processing summary
-
write_helper_sheets(): used to generate sheets with CDB import data in the output workbook
-
get_error_messaages_custom(), get_error_sheet_columns(): used to generate content for the error sheet summary
InputHandler subclasses assist the helper with processing rows from the input sheet. An InputHandler reads the values for one or more columns and takes some action such as validating input values and organizing data for use in generating sheets in the output workbook. The class defines methods that can be overridden in subclasses including initialize() and handle_input(). The subsequent section details some of the InputHandler subclasses used by CableSpecsSheetHelper and CablesSheetHelper in processing their input sheets.
Encapsulates a description of a column from an input sheet including a key for referring to the column value in a row dictionary, column label, and a flag indicating whether or not a value is required for the column in the input sheet.
Contains description of a column in an output sheet, including a method name for accessing the column value in an OutputObject class, and a column label.
OutputObject subclasses are used to generate the values for CDB import sheets in the output workbook. The subsequent section covers the OutputObject subclasses used by CableSpecsSheetHelper and CablesSheetHelper to create the output workbook.
ItemInfoManager is a container for most of the data read, collected, and produced by the script. It is a convenient way to share data between producers and consumers such as helper and handler subclasses.
This is a utility class for managing and providing access to the Excel named ranges used in the kabel workbook to constrain one column's value based on the value in another column (e.g., for hierarchical locations of devices). An instance of the ConnectedMenuManager is created by the ItemInfoManager during its initialization.
This utility class provides a wrapper around a dictionary that is organized for managing information about the mapping of CDB item names to id's. Instances are created by the ItemInfoManager for managing mappings of CDB name/id mappings for source and cable design items.
This utility class is also a wrapper around a two-level dictionary for managing the CDB machine design item id's of endpoint devices contained within the racks specified in the input workbook. It is created in ItemInfoManager initialization.
This section describes how the classes introduced above are used in the script implementation by CableSpecsSheetHelper and CablesSheetHelper to facilitate processing data from the input sheet and generating content in the output workbook. This might be helpful to a developer trying to add new functionality, or track down a problem.
This method performs the following actions:
- determines technical system owner for invocation of script
- finds column for specified technical system in CableTypes sheet, reads corresponding cable type names
- finds header row for specified technical system in CableSpecs sheet, sets values for header, first, and last data rows accordingly
- reads connector types utilized in the specified range of data in the CableSpecs sheet, retrieves information about existing CDB connector types via ItemInfoManager
- reads cable types names used in the specified range of the CableSpecs sheet, retrieves information about existing CDB cable types via ItemInfoManager
The following InputHandler subclasses are employed by the CableSpecsSheetHelper:
-
TechnicalSystemCableTypeValidationHandler - validates that each cable type name read from the CableSpecs tab is defined for the technical system in the CableTypes tab
-
UniqueNameHandler - validates that each cable type name read from the CableSpecs tab for the technical system is unique
-
CableTypeConnectorHandler - six instances of this handler are used to collect information from the six cable connector columns in the CableSpecs sheet (e1-1, e2-1, ..., e2-3), used to create sheet for CDB connector type import in the output workbook
-
CableTypeExistenceHandler - maintains lists of cable types that exist in CDB and new cable types, for use in generating content in the output workbook such as the sheets for comparing existing CDB cable types and importing new ones
-
SourceHandler - retrieves list of ids for existing CDB source items for list of manufacturer names from relevant range of CableSpecs tab, used to identify sources that already exist in CDB and those that need to be added for use in generating source item import sheet in output workbook
Collects lists of CableTypeOutputObjects for existing and new CDB cable types, used in generating import sheets in output workbook.
Checks that all cables for the technical system read from the CableTypes sheet are defined in the CableSpecs sheet.
Adds messages to summary tab with number of existing/new connector types, existing/new sources, and existing/new cable types with details columns containing the corresponding item names.
Adds the following sheets to the output workbook:
-
connector type import - using ConnectorTypeOutputObjects collected in ItemInfoManager output_connector_types
-
source item import - using SourceOutputObjects collected in ItemInfoManager output_objects_source
-
cable catalog item compare - using CableTypeOutputObjects collected in ItemInfoManager output_objects_cable_type_compare
-
cable catalog item import - using CableTypeOutputObjects collected in helper's output_objects list
-
cable catalog connector import - using CableTypeConnector objects collected in ItemInfoManager output_cable_type_connectors
Adds a message to error summary sheet with number of cable types specified in CableTypes tab but not defined in CableSpecs tab, with detail column showing corresponding cable type names.
No special helper-level initialization is performed. InputHandlers perform their own-specific initialization as described below.
The following InputHandler subclasses are employed by the CablesSheetHelper:
-
UniqueNameHandler - Checks that names for cable design items to be included in output workbook's import sheet are not duplicated within cables tab
-
NamedRangeHandler - Used for laying, voltage, owner, and location columns that the specified column value is valid for the corresponding excel named range
-
CableTypeValidForTechnicalSystemHandler - Validates that cable type name used in Cables sheet is valid for specified technical system
-
ConnectedMenuHandler - Uses ConnectedMenuManager and Excel named range data to validate that the values in the endpoint1 and endpoint2 A/N/S/U and E/T/P/M/C columns are valid (e.g., given the value in the preceding column)
-
DeviceAddressHandler - Validates that value in endpoint1 and endpoint2 address columns are valid given the location and E/T/P/M/C column values
-
CableDesignExistenceHandler - Retrieves information in initialization about existing cable design items via CDB API, uses to tag each cable as new or existing
-
EndpointHandler - Retrieves information in initialization about endpoint devices and rack/device relationships via CDB API, uses to validate that endpoint device names in End1 device and End2 device columns are valid and contained in the expected rack.
-
DevicePortHandler - Collects information from the End1 and End2 device and port columns for use in generating the catalog port import sheet in the output workbook
- collects CableDesignOutputObjects for existing cable design items for generating sheet for comparing existing cable design items in output workbook
- collects CableInventoryOutputObjects, one for each cable in the input sheet, for use in generating cable inventory import sheet in output workbook
- collects CableDesignOutputObjects, one for each cable in the input sheet, for use in generating the cable design import sheet in the output workbook
No sheet level validation is performed.
Adds messages to summary sheet with number of existing/new cable designs, and values ignored in the End1/End2 port columns if the ignorePorts flag is set to true, along with a details column for each including the corresponding item names.
Adds the following sheets to the output workbook:
-
catalog port import - using CatalogPortOutputObjects collected in helper's list catalog_port_output_objects
-
cable inventory item import - using CableInventoryOutputObjects collected in ItemInfoManager output_objects_cable_inventory
-
cable design item compare - using CableDesignOutputObjects collected in ItemInfoManager output_objects_cable_design_compare
-
cable design item import - using CableDesignOutputObjects collected in helper's list of output_objects
Adds messages to error summary with number of missing and nonunique endpoint device names, along with details columns with corresponding item names.