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?

6
  • 1
    Since Arcgis 10.2 the conversion tools include converting from excel to table. That might be useful.
    – Fezter
    Commented Mar 8, 2015 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 Commented Mar 8, 2015 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
    Commented Mar 8, 2015 at 15:29
  • You can do this in pure python. Here is a duplicate of your question. stackoverflow.com/questions/14196013/… Commented Mar 8, 2015 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
    Commented Mar 8, 2015 at 18:42

1 Answer 1

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 and acknowledge you have read our privacy policy.

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