2

I am trying to join an Excel table to a shapefile in ArcPy. My first step is to convert the Excel sheet to a table, but I am receiving an error:

#Convert xlsx to table:
import os
import xlrd
import arcpy

in_excel= r'C:\\Temp\\Planning\\Planning_Out\\JoinTest.xlsx'
out_gdb= r'C:\\Temp\\Planning\\Planning_Out\\JoinTest.gdb'

def importallsheets(in_excel, out_gdb):
    workbook = xlrd.open_workbook(in_excel)
    sheets = [sheet.name for sheet in workbook.sheets()]


out_table=  os.path.join(out_gdb,arcpy.ValidateTableName("{0}_{1}".format(os.path.basename(in_excel), sheet),out_gdb))

print('Converting {} to {}'.format(sheet, out_table))

        # Perform the conversion
arcpy.ExcelToTable_conversion(in_excel, out_table, sheet)

Error:

Traceback (most recent call last):
  File "C:\Temp\Planning\LAMP.py", line 52, in <module>
    out_table=  os.path.join(out_gdb,arcpy.ValidateTableName("{0}_{1}".format(os.path.basename(in_excel), sheet),out_gdb))
NameError: name 'sheet' is not defined

My code was taken from: http://pro.arcgis.com/en/pro-app/arcpy/get-started/working-with-geodatabases.htm

My thought process is that once the Excel file is converted to a table, I can join it to my shapefile using the following code:

# Make the shapefile a layer
arcpy.MakeFeatureLayer_management("merged_lines.shp", "tempLayer")

# Set the local parameters
inFeatures = "tempLayer"
joinField = "Name"
joinTable = r'C:\\Temp\\Planning\\Planning_Out\\JoinTest.gdb '
fieldList = ["Name", "Status"]

# Join feature classes by joinField
arcpy.JoinField_management (inFeatures, joinField, joinTable, joinField, fieldList)

I am using version 10.3.1

4
  • Try making it a CSV rather than an excel table
    – Stella
    Commented Mar 30, 2016 at 21:07
  • 2 things - 1) not sure ValidateTableName can work off the excel spread sheet name directly. (would have to test) and 2) ExcelToTable was introduced @ arcgis 10.2
    – KHibma
    Commented Mar 30, 2016 at 21:11
  • Sorry... version 10.3.1. Commented Mar 30, 2016 at 21:14
  • For starters, when defining input and output file paths, you are using escape characters and raw string syntax. Either drop the leading r, or change '\\' to '\'.
    – bebego
    Commented Apr 1, 2016 at 14:40

2 Answers 2

1

From python-excel.com:

xlrd

This package is for reading data and formatting information from older Excel files (ie: .xls)

If all you need to do import one table from one Excel workbook, use the arcpy.ExcelToTable_conversion tool. http://pro.arcgis.com/en/pro-app/tool-reference/conversion/excel-to-table.htm. Note that there is an optional parameter for the sheet name.

There are several problems with your script, but you should post separate questions. For starters, functions have to have a return. Then the return is assigned to a variable when the function is run. Something like the following.

 def say_hello(someones_name):
     return 'Hello, ' + someones_name

 greeting = say_hello('Tim')
 Print greeting
1
  • Using ExcelToTable is the way to go if you only have one table. Minor quibble... Python functions do not require a return. I wouldn't recommend it because it's harder to read, but it won't raise an error. stackoverflow.com/questions/15300550/…
    – KJYDavis
    Commented Mar 31, 2016 at 5:12
0

A few observations:

  1. The function def importallsheets(in_excel, out_gdb) is never actually called in your code.
  2. sheet is not defined

It looks like you have an excel table with multiple sheets? Is this correct?

If so, you would want to define a variable that contains the sheet names:

excel_sheets = importallsheets(in_excel, out_gdb)

Then call arcpy.ExcelToTable_conversion() using a for loop. The code from your example was taken from here and uses a for loop. That will convert all the sheets in the excel documents to tables in your geodatabase.

If you just have one sheet, then you can leave out all the stuff about which sheet and just do

arcpy.ExcelToTable_conversion(in_excel, out_table)

And that will grab the first sheet by default.

1
  • This answer is not correct. The ExcelToTable call is part of the loop through the sheets in the importallsheets function; it doesn't come after.
    – RHB
    Commented Mar 31, 2016 at 1:48

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.