2

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?

  • 1
    Since Arcgis 10.2 the conversion tools include converting from excel to table. That might be useful. – Fezter Mar 8 '15 at 11:55
  • 1
    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 – Alex Tereshenkov Mar 8 '15 at 12:43
  • 1
    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. – crmackey Mar 8 '15 at 15:29
  • You can do this in pure python. Here is a duplicate of your question. stackoverflow.com/questions/14196013/… – Farid Cheraghi Mar 8 '15 at 17:25
  • 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? – user48149 Mar 8 '15 at 18:42
3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.