CODEWITHSUNDEEP
pythonpython-2.7loops
Damon
Damon

Reputation: 119

Reading data from columns in Excel

I am working on a python script to read data from columns in an Excel file and using that data to execute commands on a Cisco switch.

For example, I have below 2 columns

Switch        MAC
aaa.com      mac.aaa.000
bbb.com      mac.bbb.000
ccc.com      mac.ccc.000
.....        .....

The python will now go to switch aaa and will set port security with mac mac.aaa.000 as sticky.

This is the command:

switchport port-security **<mac-address>** sticky

Now I have login part done and I can execute commands. I need help with the part which reads 2 columns from file and know to login to switch a and sticky macaaa on corresponding column.

Here is how I am logging in and executing commands on switch:

term_len = "term len 0"
# Need a loop here that gets switchname from coulmn A and assign it to variable host and also corresponding MAC address and assigns it to variable MAC
host = ""
MAC = ""
session = telnetlib.Telnet(host)
session.write(cred.username + '\n')
session.read_until('Password: ')
session.write(cred.password + '\n')
session.read_until('>')
session.write(cred.en + '\n')
session.read_until('Password: ')
session.write(cred.en_pass + '\n')
session.read_until('#')
session.write(term_len + '\n')
session.write(switchport port-security **MAC** sticky + '\n')

Please help. The loop will read all switches and MAC from files and executes the sticky command.

Upvotes: 1

Views: 227

Answers (3)

khelili miliana
khelili miliana

Reputation: 3822

You can use xlrd, This script allows you to transform an excel data table to a list of dictionaries:

import xlrd

workbook = xlrd.open_workbook('foo.xls')
workbook = xlrd.open_workbook('foo.xls', on_demand = True)
worksheet = workbook.sheet_by_index(0)
first_row = [] # The row where we stock the name of the column
for col in range(worksheet.ncols):
    first_row.append( worksheet.cell_value(0,col) )
# transform the workbook to a list of dictionaries
data =[]
for row in range(1, worksheet.nrows):
    elm = {}
    for col in range(worksheet.ncols):
        elm[first_row[col]]=worksheet.cell_value(row,col)
    data.append(elm)
print data

You can also use Pandas

from pandas import *
xls = ExcelFile('foo.xls')
df = xls.parse(xls.sheet_names[0])
print df.to_dict()

Upvotes: 1

Arnab
Arnab

Reputation: 1037

You can use this code to read data from the excel files.

import os
import datetime
import sys
import re
import openpyxl
import uuid
from openpyxl import load_workbook

class ExcelDictSmall(dict):
    '''
    Dictionary with all Sheet Names as key, value is a List of Rows each item in a Row List is a List of Cells
    '''
    def __init__(self,dir,fname):
        fullFname = os.path.join(dir,fname)
        wb = load_workbook(filename=fullFname, read_only=True)
        allSheets= wb.get_sheet_names()
        for sheet in allSheets:
            sheetList = []
            rowlist=[]
            for row in wb[sheet].rows:
                for cell in row:
                    rowlist.append(cell.value)
                sheetList.append(rowlist)
                rowlist=[]
            self[sheet]=sheetList

Upvotes: 1

Jim Cole
Jim Cole

Reputation: 64

I have been using openpyxl to read excel data into scripts.

    from openpyxl import *
    from tkinter.filedialog import askopenfilename,askdirectory,asksaveasfilename
    #file selector using tkinter
    file = askopenfilename()
    #load the workbook
    wb=load_workbook(file)
    #get sheets
    sheets = wb.sheetnames
    #select sheet to use by index from sheetnames
    ws=wb[sheets[0]]
    #loop through the rows of worksheet and store in list
    switchMAC=[]
    for r in range (ws.max_row):
        row=[]
        for c in range(ws.max_column):
            row.append(ws.cell(row=r+1,column=c+1).value)
        switchMAC.append(row)

From here you now have a list of lists (switchMAC) you can loop through to pass your commands

    for r in switchMac:
        host=r[0]
        MAC=r[1]
        #add the rest of your code for each host within the loop

Upvotes: 1

Related Questions