Skip to content

cable import script developer notes

Jump to bottom
craigmcchesney edited this page Jun 6, 2022 · 25 revisions

Cable Import Script Overview

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.

Script Location and Links

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.

Script Inputs, Processing, and Outputs

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.

Kabel Workbook Input File

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

Script Processing

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.

Output Workbook Contents

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

Cable Import Script Features

As mentioned already, the requirements for the script changed radically from its inception to its current incarnation. I'm sure there are simplifications that could be made, but hopefully this section will help a developer understand the key parts of the script.

Cable Import Script Implementation

This section describes how the script uses the features described above to process the input kabel workbook.

Clone this wiki locally