ArcPy: Excel to Table Conversion and Join Management

Question

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

Answer 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

Answer 2

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.