Wednesday, December 2, 2015

Being a lazy networking guy - Accessing Excel data from Python

Most of the time during the initial network design stage I keep lots of data in Excel. While implementation usually do manual work to extract the data from excel and convert it as CLI configuration. following example I've simulated to create the interface description from the data i've populated in excel.

Following tools required in windows : (if you are a Linux guy most probably not reading this :) )
  1. you can find the python windows executable in following URL : Python download link
  2. I'm using notepad++  with PyNPP , you can install this from plugin manager. you need to edit the python location in PyNPP option.
  3. Installing openpyxl is quite easy on Windows, you can find pip at C:\Python34\Scripts\pip.exe just run following command to install openpyxl "C:\Python34\Scripts> pip install openpyxl "
You can do lot of stuff your imagination is the limit . have a look on the openpyxl
import openpyxl
wb = openpyxl.load_workbook('test.xlsx')
sheets = wb.get_sheet_names()
#print (sheets)
sheet = wb.get_sheet_by_name("Sheet2")
for i in range (1,16):
 print ("interface ",sheet.cell(row=i,column=2).value,"\n description *** Link to ",sheet.cell(row=i,column=4).value,"-",sheet.cell(row=i,column=5).value," ***")
This is the input data i had
Input - Data

Following is the output created by the script
 ['Sheet1', 'Sheet2', 'Sheet3', 'Sheet4']
interface  Port Number
 description *** Link to  Remote Device - Remote Port  ***
interface  Eth1/1
 description *** Link to  ABC_7710_CORE2 - Eth1/1  ***
interface  Eth1/2
 description *** Link to  ABC_7710_CORE2 - Eth1/2  ***
interface  Eth1/3
 description *** Link to  N/A - N/A  ***
interface  Eth1/4
 description *** Link to  N/A - N/A  ***
interface  Eth1/5
 description *** Link to  LOC1_7710_DIST1 - Eth1/1  ***
interface  Eth1/6
 description *** Link to  LOC1_7710_DIST2 - Eth1/1  ***
interface  Eth1/7
 description *** Link to  LOC2_7710_DIST1 - Eth1/1  ***
interface  Eth1/8
 description *** Link to  LOC2_7710_DIST2 - Eth1/1  ***
interface  Eth1/9
 description *** Link to  LOC3_7710_DIST1 - Eth1/1  ***
interface  Eth1/10
 description *** Link to  LOC3_7710_DIST2 - Eth1/1  ***
interface  Eth1/11
 description *** Link to  LOC4_7710_DIST1 - Eth1/1  ***
interface  Eth1/12
 description *** Link to  LOC4_7710_DIST2 - Eth1/1  ***
interface  Eth1/13
 description *** Link to  ABC_7710_SFDIST1 - Eth1/1  ***
interface  Eth1/14
 description *** Link to  ABC_7710_SFDIST2 - Eth1/1  ***
References :