Dynamicaly Build Python lists from Sheets in Excel Workbook

2
votes

I am attempting to compress some code I previous wrote in python. I have some drawn out code that loops through a number of lookup tables in an excel workbook. There are about 20 sheets that contain lookup tables in the workbook. I want to loop through the values in each lookup table and add them to their own list. My existing code looks like this:

test1TableList = []
for row in arcpy.SearchCursor(r"Z:\Excel\LOOKUP_TABLES.xlsx\LookupTable1$"):
    test1TableList.append(row.Code)

test2TableList = []
for row in arcpy.SearchCursor(r"Z:\Excel\LOOKUP_TABLES.xlsx\LookupTable1$"):
    test2TableList.append(row.Code)

test3TableList = []
for row in arcpy.SearchCursor(r"Z:\Excel\LOOKUP_TABLES.xlsx\LookupTable1$"):
    test3TableList.append(row.Code)

test4TableList = []
for row in arcpy.SearchCursor(r"Z:\Excel\LOOKUP_TABLES.xlsx\LookupTable1$"):
    test4TableList.append(row.Code)

test5TableList = []
for row in arcpy.SearchCursor(r"Z:\Excel\LOOKUP_TABLES.xlsx\LookupTable1$"):
    test5TableList.append(row.Code)

yadda yadda

I want to compress that code (maybe in a function).

Issues to resolve:

  1. Sheet names are all different. I need to loop through each sheet in the excel workbook in order to a) grab the sheet object and b) use the sheet name as part of the python list variable name
  2. I want each list to remain in memory for use further along the code

I've been trying something like this, which work but the python list variables don't seem to stay in memory:

import arcpy, openpyxl
from openpyxl import load_workbook, Workbook

wb = load_workbook(r"Z:\Excel\LOOKUP_TABLES.xlsx")

for i in wb.worksheets:

    filepath = r"Z:\Excel\LOOKUP_TABLES.xlsx" + "\\" + i.title + "$"
    varList = []
    with arcpy.da.SearchCursor(filepath, '*') as cursor:
        for row in cursor:
            varList.append(row[0])

    # This is the area I am struggling with. I can't seem to find a way to return 
    # each list into memory. I've tried the following code to dynamically create 
    # variable names from the name of the sheet so that each list has it's own 
    # variable. After the code has run, I'd just like to set a print statement 
    # (i.e. print variablename1) which will return the list contained in the variable
    newList = str(i.title) + "List"
    newList2 = list(varList)
    print newList + " = " + str(newList2)

I've been working on this for a while and I have no doubt, at this point, i am over thinking my solution but I'm at a block. Any recommendations are welcome!

2
pythonexcellist
have you solved the problem? - alecxe

2 Answers

3
votes

Not sure if it is the best for you, but you could use pandas to import your sheets into a dataframes.

from pandas.io.excel import ExcelFile
filename = 'linreg.xlsx'
xl = ExcelFile(filename)

for sheet in xl.sheet_names:
    df = xl.parse(sheet)
    print df
1
votes

Instead of having breeding lists, use a dictionary for collecting the data per-sheet:

import arcpy
from openpyxl import load_workbook

wb = load_workbook(r"Z:\Excel\LOOKUP_TABLES.xlsx")

sheets = {}
for i in wb.worksheets:
    filepath = r"Z:\Excel\LOOKUP_TABLES.xlsx" + "\\" + i.title + "$"
    with arcpy.da.SearchCursor(filepath, '*') as cursor:
        sheets[i.title] = [row[0] for row in cursor]

print sheets