cable barcode id matcher

match-cables.py

$ cat gpt-matchcables.py
#!/usr/bin/python3

import re
import csv

# Initialize dictionaries to store data from both CSV files
switchports_data = {}
patch_panels_data = {}

# Specify the CSV file names
switchports_csv_file = "switchports-cables.csv"
patch_panels_csv_file = "patchpanels-cables.csv"

# Function to format port IDs as "A 1.1" style
def format_switchport_id(Pref, Port):
    return f"{row['Pref']}{row['Port']}"

# Function to format port IDs as "A 1.1" style
# Serverschrank (A) UG ;6/17
# Schrank (E) OG Kollektorgang;6/17
# Schrank (G) OG Kopierraum;6/17
def format_port_id(serverschrank, panel_port):
    serverschrank = re.match('(Serverschrank|Schrank)?\s*\((\w)\)', serverschrank).group()
    panel_port = panel_port.replace('/', '.')
    return f"{serverschrank} {panel_port}"


# Read data from switchports CSV
with open(switchports_csv_file, mode='r') as switchports_file:
    switchports_reader = csv.DictReader(switchports_file)

    for row in switchports_reader:
        switchport_id = format_switchport_id(row['Pref'], row['Port'])
        if row['ID'] == "":
            continue
        switchports_data[row['ID']] = {
            'Switch Name': row['Switch'],
            'Switch Port': switchport_id
        }

# Read data from patch panels CSV
with open(patch_panels_csv_file, mode='r', encoding='utf-8') as patch_panels_file:
    patch_panels_reader = csv.DictReader(patch_panels_file, delimiter=';')

    for row in patch_panels_reader:
        #    port_id = format_port_id(row['Serverschrank'], row['Panel/ Port'])
        port_id = format_port_id(row['Serverschrank'], row['Panel/ Port'])
        patch_panels_data[row['ID']] = {
            'Port ID': port_id,
            'Zielbezeichnung': row['Zielbezeichnung']
        }

# Find matching IDs and output the information
print((";").join(["State", "Cable ID", "Rack & Patch Panel Port", "Switch Name", "Switch Port", "Zielbezeichnung", "MAC Addr", "LLDP Info"]))
for id in switchports_data:
    _cid         = id
    _switch_info = switchports_data[id]
    _ppdid       = "N/C"
    _zielinfo    = "N/C"
    _state       = "U"
    _mac         = "not_impl"
    _lldp        = "not_impl"
    if id in patch_panels_data:
        _ppdata      = patch_panels_data[id]
        _ppdid       = _ppdata['Port ID']
        _zielinfo    = _ppdata['Zielbezeichnung']
        _state       = "C"

    print((";").join([_state, _cid, _ppdid, _switch_info['Switch Name'], _switch_info['Switch Port'], _zielinfo, _mac, _lldp]))

Updated new version

• mildly refactored
• makes csv output (more useful)
• added row for connection state U = Unplugged and P = Plugged
• added rows for lldp info (not yet implemented)
• added rows for mac adresss on port (not yet implemented)

Limitations

• data is still read off stale .csv files
• it should be pluggable to allow for reading from any source

Examples

Input example

switchports-ug.csv

Switch,Pref,Port,ID
sw-1,ge-0/0/,0,
sw-1,ge-0/0/,1,W20211221012345

patchpanels-cables.csv

Serverschrank;Panel/ Port;Zielbezeichnung;ID;
Serverschrank (A) UG ;x/x;EG Region Trasse 2 Print;;
Serverschrank (A) UG ;x/x;EG Region Tray 6 Pizza Maker ;;

Example Output

State;Cable ID;Rack & Patch Panel Port;Switch Name;Switch Port;Zielbezeichnung;MAC Addr;LLDP Info
U;CG2202228945000512;N/C;sw-1;ge-0/0/0;N/C;not_impl;not_impl
C;CG2206233436000481;Rack (E) 2.7;sw-1;ge-0/0/1;EG Region 1 Trasse 9 hinten ;not_impl;not_impl