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 Mar 30 '16 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 Mar 30 '16 at 21:11
  • Sorry... version 10.3.1. – Jessica Marie Mar 30 '16 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 '\'. – Ben Gosack Apr 1 '16 at 14:40
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 Mar 31 '16 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 Mar 31 '16 at 1:48

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.