We have already seen how to work with and manipulate CSV files with python for network automation. This post is going to talk about ways you can manipulate excel for network automation using python.
Please read How to read write Excel with python for Network Automation Part 1
From one of the previous posts, we were able to fetch formatted data from cisco devices using netmiko. Let’s take that output data and try to put it into formatted excel sheets.
# modification to the script in last post.
import pandas as pd ## import pandas module.
data = []
all_devices = [csr1000v1, csr1000v2, iosxrv9000, nxosv9000]
for device in all_devices:
net_connect = ConnectHandler(**device)
data.extend(net_connect.send_command('show version', use_textfsm=True))
# step above will yeild a list of dictionaries.
# we are extending the empty dictionary called data with the parsed output by netmiko to obtain a list of dictionaries.
print(data)
╰─ python3 script7.py ─╯
[{'version': '17.3.1a', 'rommon': 'IOS-XE', 'hostname': 'csr1000v-1', 'uptime': '1 day, 9 hours, 6 minutes', 'uptime_years': '', 'uptime_weeks': '', 'uptime_days': '1', 'uptime_hours': '9', 'uptime_minutes': '6', 'reload_reason': 'reload', 'running_image': 'packages.conf', 'hardware': ['CSR1000V'], 'serial': ['9ESGOBARV9D'], 'config_register': '0x2102', 'mac': [], 'restarted': ''}, {'version': '16.9.3', 'rommon': 'IOS-XE', 'hostname': 'csr1000v-1', 'uptime': '2 days, 23 hours, 52 minutes', 'uptime_years': '', 'uptime_weeks': '', 'uptime_days': '2', 'uptime_hours': '23', 'uptime_minutes': '52', 'reload_reason': 'reload', 'running_image': 'packages.conf', 'hardware': ['CSR1000V'], 'serial': ['926V75BDNRJ'], 'config_register': '0x2102', 'mac': [], 'restarted': ''}, {'version': '6.5.3', 'uptime': '2 weeks 2 days 3 hours 12 minutes', 'location': '/opt/cisco/XR/packages/', 'hardware': 'IOS-XRv 9000', 'build_host': 'iox-ucs-019'}, {'uptime': '11 day(s), 3 hour(s), 20 minute(s), 12 second(s)', 'last_reboot_reason': 'Unknown', 'os': '9.3(3)', 'boot_image': 'bootflash:///nxos.9.3.3.bin', 'platform': 'C9300v', 'hostname': 'sbx_nxosv1', 'serial': '9N3KD63KWT0'}]The next step is to put this list of dictionaries into pandas dataframe and finally to excel sheet or csv like this
data = []
all_devices = [csr1000v1, csr1000v2, iosxrv9000, nxosv9000]
for device in all_devices:
net_connect = ConnectHandler(**device)
data.extend(net_connect.send_command('show version', use_textfsm=True))
# step above will yeild a list of dictionaries.
# we are extending the empty dictionary called data with the parsed output by netmiko to obtain a list of dictionaries.
df = pd.DataFrame(data)
print(df)
df.to_excel("Output.xlsx", index=False)
╰─ python3 script7.py ─╯
version rommon hostname uptime uptime_years ... build_host last_reboot_reason os boot_image platform
0 17.3.1a IOS-XE csr1000v-1 1 day, 9 hours, 11 minutes ... NaN NaN NaN NaN NaN
1 16.9.3 IOS-XE csr1000v-1 2 days, 23 hours, 57 minutes ... NaN NaN NaN NaN NaN
2 6.5.3 NaN NaN 2 weeks 2 days 3 hours 17 minutes NaN ... iox-ucs-019 NaN NaN NaN NaN
3 NaN NaN sbx_nxosv1 11 day(s), 3 hour(s), 24 minute(s), 44 second(s) NaN ... NaN Unknown 9.3(3) bootflash:///nxos.9.3.3.bin C9300v
[4 rows x 22 columns]Here is the final output from the script
