Creates an Excel inventory from Cisco Show Version text files

Question

This has been written for network engineers to be able to create an Excel inventory of switch/router device info from a folder of 'Show version' .txt files. I've put it here to help me learn about Python and how I could potentially improve my coding, so please, feel free to rip into this and criticise!

#!/usr/bin/env python
'''
Title: Show File Inventory Creation
Usage: Creates an excel spreadsheet inventory from a folder containing 
 Cisco 'show version' text files. This inventory contains the 
 hostname, model & serial number of each device, including switch 
 stacks, and embeds each text file in the spreadsheet.
Platform: Windows
Warning: Only works with .xls files, NOT with .xlsx files
 'show' files must be .txt files
Date: 06/01/2015
Version: 2.1
''' 
import os
import csv
import xlwt
from xlwt import Workbook, easyxf
import win32com.client as win32
import win32gui
from win32com.shell import shell, shellcon
#Define global variables for excel spreadsheet workbook and worksheet, 
#and a 'visual demarcation' line
book = Workbook()
sheet = book.add_sheet('Inventory',cell_overwrite_ok=True)
vis_demarc_line = '\n\n' + ('=' * 100)
#Function to parse show files and store data in csv format, takes the 
#directory path of the folder containing the show files as a parameter.
def inventory_data_to_csvfile(path):
 #Inform user of progress
 print (vis_demarc_line + 
 "\n\n\tRetrieving device data from your 'show' files..."
 )
 #open temporary text file to store device data, 'a' :- append
 inventorytxt = open('inventory.txt', 'a')
 #write header line to text file
 inventorytxt.write('Hostname' + ',' + 'Model' + ',' + 
 'Serial Number' + '\n'
 )
 #iterate through each file in the specified folder
 for filename in os.listdir(path):
 #specify each show file in turn
 with open(os.path.join(path,filename),'r') as showfile:
 lines = showfile.readlines()
 #iterate through each line in the show file
 for line in lines:
 i = 1
 #specify hostname as preceding 'show version' command in file
 if '#sh ver' in line:
 i += 1
 if i <= 2:
 hostname = line.split('#')[0]
 else:
 hostname = ' '
 inventorytxt.write(hostname)
 elif '#show ver' in line:
 i += 1
 if i <= 2:
 hostname = line.split('#')[0]
 else:
 hostname = ' '
 inventorytxt.write(hostname)
 elif '#sho ver' in line:
 i += 1
 if i <= 2:
 hostname = line.split('#')[0]
 else:
 hostname = ' '
 inventorytxt.write(hostname)
 #specify model as last entry on line containing 'model number'
 if 'Model number' in line:
 model = line.split()[-1]
 inventorytxt.write(',' + model + ',')
 elif 'Model Number' in line:
 model = line.split()[-1]
 inventorytxt.write(',' + model + ',')
 #specify serial number as last entry on line containing 
 #'system serial number'
 if 'System serial number' in line:
 serial = line.split()[-1]
 inventorytxt.write(serial + '\n')
 elif 'System Serial Number' in line:
 serial = line.split()[-1]
 inventorytxt.write(serial + '\n')
 #close text file
 inventorytxt.close()
 #change temporary text file to temporary csv file
 os.rename('inventory.txt', 'inventory.csv')
#Function to write csv data to Excel spreadsheet
def csv_to_xls():
 #Inform user of progress
 print vis_demarc_line + '\n\n\tSaving device data to Excel spreadsheet...'
 #define spreadsheet cell styles
 header_style = xlwt.easyxf(
 'font: name Calibri, bold on, height 320, colour white;'
 'borders: left medium, right medium, top medium, bottom medium;'
 'alignment: horizontal centre, vertical centre;'
 'pattern: pattern solid, fore_colour black;'
 )
 cell_style = xlwt.easyxf(
 'font: name Calibri, bold off, height 240;'
 'borders: left medium, right medium, top medium, bottom medium;'
 'alignment: horizontal centre, vertical centre;'
 )
 #change cell height and width
 i = 0
 j = 0
 while i < 4:
 sheet.col(i).width_mismatch = True
 sheet.col(i).width = 256*25
 i += 1
 while j < 200:
 sheet.row(j).height_mismatch = True
 sheet.row(j).height = 896
 j += 1
 #freeze header row
 sheet.set_panes_frozen(True)
 sheet.set_horz_split_pos(1)
 #open temporary csv file containing device data
 csvfile_reader = csv.reader(open('inventory.csv', 'rb'))
 #write csv data to Excel spreadsheet
 for rowx, row in enumerate(csvfile_reader):
 for colx, value in enumerate(row):
 sheet.write(rowx, colx, value, cell_style)
 #re-write header row with new style
 sheet.write(0,0,'Hostname',header_style)
 sheet.write(0,1,'Model',header_style)
 sheet.write(0,2,'Serial Number',header_style)
 sheet.write(0,3,'Show File',header_style)
 #define inventory_dir as a global variable to be able to use outside 
 #of this function
 global inventory_dir
 inventory_dir = folder + 'Inventory\\'
 #create 'Inventory' folder if it doesn't already exist
 if not os.path.exists(inventory_dir):
 os.makedirs(inventory_dir)
 #save spreadsheet as temporary .xls file
 book.save(inventory_dir + 'INVENTORY.xls')
#Function to embed text files in spreadsheet
def embed_txt_files():
 #Inform user of progress
 print (vis_demarc_line + 
 "\n\n\tEmbedding your 'show' files into Excel spreadsheet..."
 )
 #open Excel
 xl = win32.gencache.EnsureDispatch('Excel.Application')
 #Make Excel invisible to user
 xl.Visible = 0
 #open specific Excel workbook
 wb = xl.Workbooks.Open(inventory_dir + 'INVENTORY.xls')
 #define range as first 200 cells in column A
 range = wb.ActiveSheet.Range("A2:A200")
 #iterate through cells
 for cell in range:
 #check to see if cell is not empty
 if cell.Value is not None:
 #specify 'show' text file based on hostname in cell in column A
 f = folder + str(cell.Value) + '.txt'
 #embed 'show' text file as OLEObject 
 ol = wb.ActiveSheet.OLEObjects().Add(Filename=f)
 #specify 'show' text file location as 3 cells to the right of 
 #cell in column A
 ol.Top = cell.GetOffset(0, 3).Top
 ol.Left = cell.GetOffset(0, 3).Left
 #save Excel workbook
 xl.ActiveWorkbook.Save()
 #quit Excel
 xl.Quit()
#Function to allow user to rename the inventory spreadsheet
def rename_file():
 #ask user for the name of their inventory spreadsheet
 print vis_demarc_line
 #global xl_inventory_fname
 inventory_fname = raw_input(
 '\n\n\tWhat would you like to name your Inventory spreadsheet?\n\n\t:'
 )
 xl_inventory_fname = inventory_fname + '.xls'
 #test validity of users filename and rename temporary .xls file to 
 #user's desired name if an exception is not raised
 try:
 os.rename(inventory_dir + 'INVENTORY.xls', inventory_dir + 
 xl_inventory_fname
 )
 print (vis_demarc_line + 
 '\n\n\tYour Inventory spreadsheet has been created here: \n\n\t%s%s' 
 % (inventory_dir, xl_inventory_fname)
 )
 print vis_demarc_line + '\n\n'
 except:
 print vis_demarc_line + "\n\n\tSorry, that is an invalid filename."
 rename_file()
#Function to open browser window to enable user to choose folder containing 
#their 'show' text files
def main(): 
 desktop_pidl = shell.SHGetFolderLocation (
 0, shellcon.CSIDL_DESKTOP, 0, 0
 )
 pidl, display_name, image_list = shell.SHBrowseForFolder (
 win32gui.GetDesktopWindow (), desktop_pidl, 
 "Please choose the folder containing your 'show' files...", 
 0, None, None
 )
 #define folder as a global variable to be able to use outside of this 
 #function
 global folder
 #set folder to choice made in browser window
 folder = shell.SHGetPathFromIDList (pidl) + '\\'
 #call inventory_data_to_csvfile, csv_to_xls, embed_txt_files & 
 #rename_file functions
 inventory_data_to_csvfile(folder)
 csv_to_xls()
 embed_txt_files()
 rename_file()
 #delete temporary csv file
 os.remove('inventory.csv')
if __name__ == '__main__':
 main()

Answer 1

With

vis_demarc_line = '\n\n' + ('=' * 100)

Every time you use it is something like

print vis_demarc_line + '\n\n\t' + text

Except for one, which is just

print vis_demarc_line + '\n\n'

Since a trailing \t on its own is invisible, the simpler solution would be to write a funciton

def print_new_section(text):
 print('\n\n' + ('=' * 100) + '\n\n\t' + text)

You forgot a space here:

 ↓↓
sheet = book.add_sheet('Inventory',cell_overwrite_ok=True)

and similar for a few other function calls. You also don't have spaces after the comment hash (#). Other than that, the style is reasonable.

You use with to open showfile but you neglect to do so for inventorytxt. Don't; put it in a with.

Many of your comments aren't very useful. Consider

# Define global variables for excel spreadsheet workbook and worksheet
book = Workbook()
sheet = book.add_sheet('Inventory', cell_overwrite_ok=True)

You know that from reading the code so you don't need to repeat it. An example of a good comment would be

# open temporary text file to store device data, 'a' :- append
with open('inventory.txt', 'a') as inventorytxt:

shortened to

# Temporary file for device data. 'a' :- append
with open('inventory.txt', 'a') as inventorytxt:

Note that this now only tells you the information that's not already in the code:

• It's temporary
• It holds device data
• 'a' means 'append'

If it doesn't give you new or clarifying information, it shouldn't be a comment.

This comment should be a docstring:

# Function to parse show files and store data in csv format, takes the 
# directory path of the folder containing the show files as a parameter.
def inventory_data_to_csvfile(path):

eg.

def inventory_data_to_csvfile(path):
 """
 Parse show files and store data in csv format.
 Takes the path for the folder containing the show files.
 """

There's no real reason for concatenation here:

inventorytxt.write('Hostname' + ',' + 'Model' + ',' + 
 'Serial Number' + '\n'
 )

Just use

inventorytxt.write('Hostname,Model,Serial Number\n')

This:

lines = showfile.readlines()
# iterate through each line in the show file
for line in lines:

should just be

for line in showfile:

since files are iterable. This will prevent the need to hold the whole file in memory. Also, prefer list(showfile) to showfile.readlines.

i is a good variable name if it is uniformly increasing by a fixed amount each iteration and its use it obvious. In this case, that isn't true. Something more descriptive like count_sh_vers. Note that that would imply reducing the count by 1 everywhere. However, the count is reset every loop iteration so the check i <= 2 is always True. Remove the variable and check.

You repeat this three times:

i += 1
if i <= 1:
 hostname = line.split('# ')[0]
else:
 hostname = ' '
inventorytxt.write(hostname)

for three if/elifs. Just use or in the condition:

if '# sh ver' in line or '# sho ver' in line or '# show ver' in line:

Better might be using re:

if re.search(r'# sh(ow?)? ver', line):

You do

if 'Model number' in line:
 model = line.split()[-1]
 inventorytxt.write(',' + model + ',')
elif 'Model Number' in line:
 model = line.split()[-1]
 inventorytxt.write(',' + model + ',')

The second can never run since the condition is the same, but it doesn't matter as the body is the same too. Just remove the elif. The same thing happens again with if 'System serial number' in line. Are you sure you understand what elif does?

This simplifies the first function to

def inventory_data_to_csvfile(path):
 """
 Parse show files and store data in csv format.
 Takes the path for the folder containing the show files.
 """
 print_new_section("Retrieving device data from your 'show' files...")
 # Temporary file for device data. 'a' :- append
 with open('inventory.txt', 'a') as inventorytxt:
 inventorytxt.write('Hostname,Model,Serial Number\n')
 for filename in os.listdir(path):
 with open(os.path.join(path, filename), 'r') as showfile:
 for line in showfile:
 # Specify hostname as preceding 'show version' command in file
 if re.search(r'# sh(ow?)? ver', line):
 hostname = line.split('# ')[0]
 inventorytxt.write(hostname)
 # specify model as last entry on line containing 'model number'
 if 'Model number' in line:
 model = line.split()[-1]
 inventorytxt.write(',' + model + ',')
 # specify serial number as last entry on line containing 
 # 'system serial number'
 if 'System serial number' in line:
 serial = line.split()[-1]
 inventorytxt.write(serial + '\n')
 os.rename('inventory.txt', 'inventory.csv')

This is still too deeply nested to another function woulud be appropriate. This gives

def read_showfile_into(showfile, inventorytxt):
 for line in showfile:
 # Hostname precedes 'show version' command
 if re.search(r'# sh(ow?)? ver', line):
 hostname = line.split('# ')[0]
 inventorytxt.write(hostname)
 # Model is last entry
 if 'Model number' in line:
 model = line.split()[-1]
 inventorytxt.write(',' + model + ',')
 # Serial number is last entry
 if 'System serial number' in line:
 serial = line.split()[-1]
 inventorytxt.write(serial + '\n')
def inventory_data_to_csvfile(path):
 """
 Parse show files and store data in csv format.
 Takes the path for the folder containing the show files.
 """
 print_new_section("Retrieving device data from your 'show' files...")
 # Temporary file for device data. 'a' :- append
 with open('inventory.txt', 'a') as inventorytxt:
 inventorytxt.write('Hostname,Model,Serial Number\n')
 for filename in os.listdir(path):
 with open(os.path.join(path, filename), 'r') as showfile:
 read_showfile_into(showfile, inventorytxt)
 os.rename('inventory.txt', 'inventory.csv')

In csv_to_xls, you do

i = 0
j = 0
while i < 4:
 sheet.col(i).width_mismatch = True
 sheet.col(i).width = 256*25
 i += 1
while j < 200:
 sheet.row(j).height_mismatch = True
 sheet.row(j).height = 896
 j += 1

This can just be

for i in range(4):
 sheet.col(i).width_mismatch = True
 sheet.col(i).width = 256*25
for j in range(200):
 sheet.row(j).height_mismatch = True
 sheet.row(j).height = 896

csvfile_reader should also use with:

# Temporary CSV file with device data
with open('inventory.csv', 'rb') as csvfile:
 for rowx, row in enumerate(csv.reader(csvfile)):
 for colx, value in enumerate(row):
 sheet.write(rowx, colx, value, cell_style)

You write

global inventory_dir
inventory_dir = folder + 'Inventory\\'

which is then used by embed_txt_files and rename_files. Since you call these as

csv_to_xls()
embed_txt_files()
rename_file()

You could trivially remove the global by doing

inventory_dir = csv_to_xls()
embed_txt_files(inventory_dir)
rename_file(inventory_dir)

Globals variables are evil; they prevent you from reusing your functions (such as how now you couldn't do this in parallel on several directories) and make data flow hard to figure out. Don't use them.

I do find it odd that you're doing

inventory_dir = folder + 'Inventory\\'

where moments before you were using

os.path.join

but since you're using Windows-only APIs it's a bearable crime.

Do note that

if not os.path.exists(inventory_dir):
 os.makedirs(inventory_dir)

is prone to TOCTOU race conditions. It's probably not especially worrisome here since your whole code is one big race condition of that kind, but you should be aware of the risks.

In embed_txt_files you write

range = wb.ActiveSheet.Range("A2:A200")

range is one of the most fundemental builtins; it's best not shadow it. You only use this once so I'd put it at point of use:

# First 200 cells in column A
for cell in wb.ActiveSheet.Range("A2:A200"):

With

xl.Visible = 0

you can probably do

xl.Visible = False

since 0 == False in Python. I suggest doing so since it makes the action self-explanatory.

This could be better with formatting:

f = folder + str(cell.Value) + '.txt'

to

f = "%s%s.txt" % (folder, cell.Value)

or moving to new-style formatting (this would change your other format) with

f = "{}{}.txt".format(folder, cell.Value)

The name f should also be improved to, say, filename. Same with ol (I chose ole_obj).

Now in rename_file, remove dead comments like

# global xl_inventory_fname

You can simplify

print_new_section('What would you like to name your Inventory spreadsheet?')
inventory_fname = raw_input('\n\t:')
xl_inventory_fname = inventory_fname + '.xls'

to

print_new_section('What would you like to name your Inventory spreadsheet?')
x1_inventory_fname = raw_input('\n\t:')
xl_inventory_fname += '.xls'

although now the raw_input line is so short it might as well just be

print_new_section('What would you like to name your Inventory spreadsheet?')
x1_inventory_fname = raw_input('\n\t:') + '.xls'

I wouldn't wrap

os.rename(inventory_dir + 'INVENTORY.xls', inventory_dir + xl_inventory_fname)

The wrapping seems more intrusive than a line of length 87.

You can probably move most print_new_section calls into main; where possible a function shouldn't be dealing with both logic and UI. rename_file seems to be the exception, so keep that as-is.

You do global folder in main; just pass it in as parameters to your functions.

main's docstring is now

"""
Open browser window to enable user to choose folder containing 
their 'show' text files.
"""

This seems misleading; the function does far more than that. It should be a comment above the code it's talking about, not the function.

Finally, you do

#!/usr/bin/env python

Use a version specifier:

#!/usr/bin/env python2

This all gives:

#!/usr/bin/env python
'''
Title: Show File Inventory Creation
Usage: Creates an excel spreadsheet inventory from a folder containing 
 Cisco 'show version' text files. This inventory contains the 
 hostname, model & serial number of each device, including switch 
 stacks, and embeds each text file in the spreadsheet.
Platform: Windows
Warning: Only works with .xls files, NOT with .xlsx files
 'show' files must be .txt files
Date: 06/01/2015
Version: 2.1
''' 
import csv
import os
import re
import win32com.client as win32
import win32gui
import xlwt
from win32com.shell import shell, shellcon
from xlwt import Workbook, easyxf
# Define global variables for excel spreadsheet workbook and worksheet
book = Workbook()
sheet = book.add_sheet('Inventory', cell_overwrite_ok=True)
def print_new_section(text):
 print('\n\n' + ('=' * 100) + '\n\n\t' + text)
def read_showfile_into(showfile, inventorytxt):
 for line in showfile:
 # Hostname precedes 'show version' command
 if re.search(r'# sh(ow?)? ver', line):
 hostname = line.split('# ')[0]
 inventorytxt.write(hostname)
 # Model is last entry
 if 'Model number' in line:
 model = line.split()[-1]
 inventorytxt.write(',' + model + ',')
 # Serial number is last entry
 if 'System serial number' in line:
 serial = line.split()[-1]
 inventorytxt.write(serial + '\n')
def inventory_data_to_csvfile(path):
 """
 Parse show files and store data in csv format.
 Takes the path for the folder containing the show files.
 """
 # Temporary file for device data. 'a' :- append
 with open('inventory.txt', 'a') as inventorytxt:
 inventorytxt.write('Hostname,Model,Serial Number\n')
 for filename in os.listdir(path):
 with open(os.path.join(path, filename), 'r') as showfile:
 read_showfile_into(showfile, inventorytxt)
 os.rename('inventory.txt', 'inventory.csv')
def csv_to_xls(folder):
 """Write csv data to Excel spreadsheet."""
 header_style = xlwt.easyxf(
 'font: name Calibri, bold on, height 320, colour white;'
 'borders: left medium, right medium, top medium, bottom medium;'
 'alignment: horizontal centre, vertical centre;'
 'pattern: pattern solid, fore_colour black;'
 )
 cell_style = xlwt.easyxf(
 'font: name Calibri, bold off, height 240;'
 'borders: left medium, right medium, top medium, bottom medium;'
 'alignment: horizontal centre, vertical centre;'
 )
 for i in range(4):
 sheet.col(i).width_mismatch = True
 sheet.col(i).width = 256*25
 for j in range(200):
 sheet.row(j).height_mismatch = True
 sheet.row(j).height = 896
 # Freeze header row
 sheet.set_panes_frozen(True)
 sheet.set_horz_split_pos(1)
 # Temporary CSV file with device data
 with open('inventory.csv', 'rb') as csvfile:
 for rowx, row in enumerate(csv.reader(csvfile)):
 for colx, value in enumerate(row):
 sheet.write(rowx, colx, value, cell_style)
 # Re-write header row with new style
 sheet.write(0, 0, 'Hostname', header_style)
 sheet.write(0, 1, 'Model', header_style)
 sheet.write(0, 2, 'Serial Number', header_style)
 sheet.write(0, 3, 'Show File', header_style)
 inventory_dir = folder + 'Inventory\\'
 if not os.path.exists(inventory_dir):
 os.makedirs(inventory_dir)
 # save spreadsheet as temporary .xls file
 book.save(inventory_dir + 'INVENTORY.xls')
 return inventory_dir
def embed_txt_files(folder, inventory_dir):
 """Embed text files in spreadsheet."""
 xl = win32.gencache.EnsureDispatch('Excel.Application')
 xl.Visible = 0
 wb = xl.Workbooks.Open(inventory_dir + 'INVENTORY.xls')
 # First 200 cells in column A
 for cell in wb.ActiveSheet.Range("A2:A200"):
 if cell.Value is not None:
 # Get 'show' text file from hostname in column A
 filename = "{}{}.txt".format(folder, cell.Value)
 # Embed as OLEObject 
 ole_obj = wb.ActiveSheet.OLEObjects().Add(Filename=filename)
 ole_obj.Top = cell.GetOffset(0, 3).Top
 ole_obj.Left = cell.GetOffset(0, 3).Left
 xl.ActiveWorkbook.Save()
 xl.Quit()
def rename_file(inventory_dir):
 """Prompt user to rename the inventory spreadsheet."""
 print_new_section('What would you like to name your Inventory spreadsheet?')
 x1_inventory_fname = raw_input('\n\t:') + '.xls'
 # test validity of users filename and rename temporary .xls file to 
 # user's desired name if an exception is not raised
 try:
 os.rename(inventory_dir + 'INVENTORY.xls', inventory_dir + xl_inventory_fname)
 print_new_section(
 'Your Inventory spreadsheet has been created here: \n\n\t{}{}'
 .format(inventory_dir, xl_inventory_fname)
 )
 print_new_section('')
 except:
 print_new_section('Sorry, that is an invalid filename.')
 rename_file()
def main(): 
 # Open browser window to enable user to choose folder containing 
 # their 'show' text files.
 desktop_pidl = shell.SHGetFolderLocation (
 0, shellcon.CSIDL_DESKTOP, 0, 0
 )
 pidl, display_name, image_list = shell.SHBrowseForFolder (
 win32gui.GetDesktopWindow (), desktop_pidl, 
 "Please choose the folder containing your 'show' files...", 
 0, None, None
 )
 # Choice made in browser window
 folder = shell.SHGetPathFromIDList (pidl) + '\\'
 print_new_section("Retrieving device data from your 'show' files...")
 inventory_data_to_csvfile(folder)
 print_new_section('Saving device data to Excel spreadsheet...')
 inventory_dir = csv_to_xls(folder)
 print_new_section("Embedding your 'show' files into Excel spreadsheet...")
 embed_txt_files(folder, inventory_dir)
 rename_file(inventory_dir)
 # delete temporary csv file
 os.remove('inventory.csv')
if __name__ == '__main__':
 main()

I haven't been able to test this so it's probably broken somewhere.

• bordeltabernacle: Thankyou very much @Veedrac for taking the time to do this. I'm going to read and process this and make sure I understand what you've said. Again thankyou, this kind of thing is invaluable! Rob