How to read write Excel with python for Network Automation Part 1

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.

There are a lot of modules available that can interact with excel programmatically using python. To name a few:-

1. xlrd – reading data and formatting information from older Excel files (ie: .xls)
2. xlwt – writing data and formatting information to older Excel files (ie: .xls)
3. xlsxwriter – writing data, formatting information and, in particular, charts in the Excel 2010 format (ie: .xlsx)
4. xlutils – utilities that require both xlrd and xlwt, including the ability to copy and modify or filter existing excel files.
5. openpyxl – The recommended package for reading and writing Excel 2010 files (ie: .xlsx)
6. pandas – fast, powerful, flexible and easy to use open source data analysis and manipulation too.

While there may be many more packages available, it’s worth noticing that some are good for reading while others only can write to excel and some can read and both to excel files. There is no point in learning the syntax and semantics of multiple packages to get the job rather stick to the one that is most versatile and will always do almost everything you need to do with excel files.

Personal favourite python excel network automation packages :-

1. Openpyxl – easy to use, faster learning curve and highly versatile.
2. Pandas – very fast, robust and allows you to work with excel in tabular format so it feels more native to excel GUI

Let’s take Cisco Devnet Always ON Sandbox Lab worksheet in xlsx format as an example:-

HOW TO READ EXCEL FILES WITH PYTHON PANDAS

Disclaimer:- This is not a pandas tutorial, it’s just basics that should be enough to read and write, otherwise, there may be hundred different ways of achieving the same result in pandas. It is extremely versatile and verbose.

import pandas as pd

df = pd.read_excel('cisco_always_on_devnet_sanbox_lab.xlsx')
print(df.head())


╰─ python3 script2.py                                                                                                                                                                      ─╯
                               HOST  SSH PORT  NETCONF PORT  RESTCONF PORT   USERNAME     PASSWORD       SECRET
0  sandbox-iosxe-latest-1.cisco.com        22           830          443.0  developer   C1sco12345   C1sco12345
1         sandbox-iosxr-1.cisco.com        22           830            NaN      admin   C1sco12345   C1sco12345
2          sandbox-nxos-1.cisco.com        22           830          443.0      admin  Admin_1234!  Admin_1234!

Let’s see how we can use pandas syntax to read each row of this dataframe into a dictionary that we can feed into netmiko or any other library to handle network device interaction.

import pandas as pd

df = pd.read_excel('cisco_always_on_devnet_sanbox_lab.xlsx')
print(df.to_dict(orient='record'))   >> just this one more line


[{'HOST': 'sandbox-iosxe-latest-1.cisco.com',
  'NETCONF PORT': 830,
  'PASSWORD': 'C1sco12345',
  'RESTCONF PORT': 443.0,
  'SECRET': 'C1sco12345',
  'SSH PORT': 22,
  'USERNAME': 'developer'},
 {'HOST': 'sandbox-iosxr-1.cisco.com',
  'NETCONF PORT': 830,
  'PASSWORD': 'C1sco12345',
  'RESTCONF PORT': nan,
  'SECRET': 'C1sco12345',
  'SSH PORT': 22,
  'USERNAME': 'admin'},
 {'HOST': 'sandbox-nxos-1.cisco.com',
  'NETCONF PORT': 830,
  'PASSWORD': 'Admin_1234!',
  'RESTCONF PORT': 443.0,
  'SECRET': 'Admin_1234!',
  'SSH PORT': 22,
  'USERNAME': 'admin'}]

Imagine you have a huge excel inventory with thousands of hosts. You can run a couple of more commands to filter what you need from the entire excel sheet and then prepare the dictionaries for further action.

Example1:- I want to extract only those hosts that have ‘nxos’ in the HOST column.

df = pd.read_excel('cisco_always_on_devnet_sanbox_lab.xlsx')
df = df[df['HOST'].str.contains('nxos')]
print(df)

╰─ python3 script2.py                                                                                                                                                                      ─╯
                       HOST  SSH PORT  NETCONF PORT  RESTCONF PORT USERNAME     PASSWORD       SECRET
2  sandbox-nxos-1.cisco.com        22           830          443.0    admin  Admin_1234!  Admin_1234!

Example2:- I want to extract only those hosts that have ‘nxos and iosxr’ in the HOST column.

df = pd.read_excel('cisco_always_on_devnet_sanbox_lab.xlsx')
df = df[df['HOST'].str.contains('nxos') | df['HOST'].str.contains('iosxr')]
print(df)

╰─ python3 script2.py                                                                                                                                                                      ─╯
                        HOST  SSH PORT  NETCONF PORT  RESTCONF PORT USERNAME     PASSWORD       SECRET
1  sandbox-iosxr-1.cisco.com        22           830            NaN    admin   C1sco12345   C1sco12345
2   sandbox-nxos-1.cisco.com        22           830          443.0    admin  Admin_1234!  Admin_1234!

There are tons of cool tips and tricks that you can use with pandas to make your code shorter, faster and more robust. If you would like to know more about pandas for network engineers, let me know and i will prepare content dedicated to using more and more of pandas for network automation engineers.

Part 2 will cover how to write to excel files using pandas.

How to read write Excel with python for Network Automation Part 2