Q:

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.

A:

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

