-
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 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