Broadly speaking I'm trying to take an excel file that contains our inventory and create different dictionaries for each location containing the {hostename : ip_address} for each switch in that location.

Ideally I'd like it to work something along the lines of

for row in range(1, worksheet.max_row   1):
    if cell.column == existing_dictionary_name
        continue
    else
        cell.column = {}
    
    key = worksheet.cell(row, #).value
    value = worksheet.cell(row, #).value
    cell.column[key] = value

I tried the following

from openpyxl import Workbook
from openpyxl import load_workbook

workbook = load_workbook(filename="Test db.xlsx")
worksheet = workbook.active
host_ip = {}

for row in range(1, worksheet.max_row   1):
    key = worksheet.cell(row, 4).value
    value = worksheet.cell(row, 2).value
    dict_name = worksheet.cell(row, 6).value
    dict_name[key] = value

print(a)
print(b)
Print(c)

but I just get

Traceback (most recent call last):
  File "main.py", line 20, in <module>
    dict_name[key] = value
TypeError: 'str' object does not support item assignment

CodePudding user response：

It's not easy creating a variable name with a name from input, but if you can settle for a dict of dicts, then it becomes a lot easier.

This uses a defaultdict defaulting to dict itself as the overall holder.

The iterates through the rows with iter_rows, skipping the header row and returning values only. Every iteration updates the dict for the row's location with the switch and ip values.

With this input:

from openpyxl import load_workbook
from collections import defaultdict

net_locations = defaultdict(dict)

wb = load_workbook("/tmp/75072112.xlsx")
ws = wb.active

for row in ws.iter_rows(min_row=2, values_only=True):
    net_locations[row[2]].update({row[0]: row[1]})

net_locations

# defaultdict(dict,
#             {'a': {'switch1': '192.168.1.1', 'switch2': '192.168.1.2'},
#              'b': {'switch3': '192.168.1.3', 'switch5': '192.168.1.5'},
#              'c': {'switch4': '192.168.1.4'}})