Using ArcPy to Read from Excel Spreadsheet to Python Dictionary

Question

How would I go about creating a script that reads a specified excel spreadsheet and places the values in a dictionary?

I have read about the xlrd library and how the library in such endeavors to read Excel spreadsheets in Python. However, I was hoping to strictly do this in the ArcPy module. Is this possible or would you be required to use the xlrd library?

• Fezter: Since Arcgis 10.2 the conversion tools include converting from excel to table. That might be useful.
• Alex Tereshenkov: resources.arcgis.com/en/help/main/10.2/index.html#//… Excel to Table GP tool, accessible both as a GP tool and as an arcpy function
• crmackey: You can use a search cursor on an excel table if it is an .xls file. That being said, I'd still recommend xlrd instead.
• Farid Cheraghi: You can do this in pure python. Here is a duplicate of your question. stackoverflow.com/questions/14196013/…
• user48149: I was trying to avoid the use of xlrd and was attempting to complete this task within the ArcPy. Is there a way of doing that?

Answer 1

Per other users suggestion, you should use Excel to Table GP tool with SearchCursor to construct the dictionary in python. Here is a sample code:

sample data in an excel file:

col1 col2 col3
---------------------
val1 val2 val3
val4 val5 val6
import arcpy
inputExcel = r"D:\Test.xls"
sheetName = "Sheet1"
memoryTable = "in_memory" + "\\" + "memoryTable"
#Make sure the memory is empty
arcpy.Delete_management(memoryTable)
arcpy.ExcelToTable_conversion(inputExcel, memoryTable,sheetName )
d = {}
fieldIndices = {}
fields = arcpy.ListFields(memoryTable)
for i,field in enumerate(fields):
 d[field.name]=[]
 fieldIndices[i] = field.name
with arcpy.da.SearchCursor(memoryTable, '*') as cursor:
 for row in cursor:
 for i in range(0,len(row)):
 d[fieldIndices[i]].append(row[i])
del cursor

And the output python dictionary:

>>> d
{u'ObjectID': [1, 2],
 u'col1': [u'val1', u'val4'],
 u'col2': [u'val2', u'val5'],
 u'col3': [u'val3', u'val6']}

I hope it helps