Automating Excel to Table in larger script in ArcMap using Python?

Question

This is the first part of a larger script i'm working on to automate heatmaps.

Currently I'm receiving this error with ArcMap 10.5:

Traceback (most recent call last): File "\coacd.org\dfs\SWS\Code Enforcement\ACD Admin Operations\GIS\Operations\Council District Analysis Reporting\Auto_HeatMap_WIP\autoheatmap.py", line 16, in xyPointsPlotted = arcpy.ExcelToTable_conversion(SOURCE_DATA, autoLayer,"") File "C:\Program Files (x86)\ArcGIS\Desktop10.5\ArcPy\arcpy\conversion.py", line 63, in ExcelToTable raise e arcgisscripting.ExecuteError: Traceback (most recent call last): File "c:\program files (x86)\arcgis\desktop10.5\ArcToolbox\Scripts\ExcelToTable.py", line 253, in arcpy.GetParameterAsText(2)) File "c:\program files (x86)\arcgis\desktop10.5\ArcToolbox\Scripts\ExcelToTable.py", line 204, in excel_to_table out_fields = gen_out_fields(workbook, sheet, out_path, is_gdb) File "c:\program files (x86)\arcgis\desktop10.5\ArcToolbox\Scripts\ExcelToTable.py", line 175, in gen_out_fields out_fields.append(clsField(f, out_path, is_gdb, out_fields)) File "c:\program files (x86)\arcgis\desktop10.5\ArcToolbox\Scripts\ExcelToTable.py", line 33, in init if not name[0].isalpha(): IndexError: string index out of range

Failed to execute (ExcelToTable).

This is the first part of the script which is supposed to convert an Excel doc to a table, plot the lat/long, and reproject it:

import arcpy
import xlrd
import os


workspace = r"\\coacd.org\dfs\SWS\Code Enforcement\ACD Admin Operations\GIS\Operations\Council District Analysis Reporting\Auto_HeatMap_WIP\shps"
arcpy.env.workspace = workspace
arcpy.env.overwriteOutput = True
autoLayer = r"\\coacd.org\dfs\SWS\Code Enforcement\ACD Admin Operations\GIS\Operations\Council District Analysis Reporting\Auto_HeatMap_WIP\shps\autoLayers.gdb"
shps = r"\\coacd.org\dfs\SWS\Code Enforcement\ACD Admin Operations\GIS\Operations\Council District Analysis Reporting\Auto_HeatMap_WIP\shps"
mxd_path = os.path.join(autoLayer, workspace)

SOURCE_DATA = r"\\coacd.org\dfs\SWS\Code Enforcement\ACD Admin Operations\GIS\Operations\Council District Analysis Reporting\Auto_HeatMap_WIP\Source_Data\SOURCE_DATA.xlsx"

#convert the workbook called XY doc to a table
xyPointsPlotted = arcpy.ExcelToTable_conversion(SOURCE_DATA, autoLayer,"")
spatial_ref = arcpy.SpatialReference("WGS 1984")
output_layer = "sample_xy_points"
saved_Layer = shps +"\\sample_xy_points.lyr"


arcpy.MakeXYEventLayer_management(xyPointsPlotted, "Longitude", "Latitude", output_layer,"WSG 1984","" )

arcpy.SaveToLayerFile_management(output_layer, saved_Layer)

#project layer into Texas FIPS 
arcpy.CopyFeatures_management(saved_Layer, "coded_xy_points")

repr_spatial_ref = arcpy.SpatialReference("NAD 1983 StatePlane Texas Central FIPS 4203 (US Feet)")
arcpy.Project_management(shps +"\\coded_xy_points", shps +"\\coded_xy_points_FIPS4203", repr_spatial_ref)

df = arcpy.ListDataFrames(mxd)[0] # address location data frame and turn on select layers
df.zoomToSelectedFeatures()

arcpy.env.extent = df.extent #set extent to current one
arcpy.env.overwriteOutput = True #overwrite existing files


scratch_output = working_gdb+"\\scratch"

Answer 1

There are a few unnecessary steps in your code. MakeXYEventLayer should work from a CSV file, and possibly a .xlsx file*. You can also take advantage of having one single variable storing the path and then build upon that. If you want shapefile output, specify names with .shp. I used the GDB as output because I didn't see any extensions or another use for your GDB. Example below. If you haven't made the GDB yet you will need to add a CreateFileGDB_management() step.

(*You might need to save the .xlsx to .csv, but I've definitely had the MakeXYEventLayer work straight from CSV -- if needed you can convert using the xlrd libary per https://stackoverflow.com/questions/20105118/convert-xlsx-to-csv-correctly-using-python)

import arcpy
import os

workspace = r"\\coacd.org\dfs\SWS\Code Enforcement\ACD Admin Operations\GIS\Operations\Council District Analysis Reporting\Auto_HeatMap_WIP\shps"

autoLayerGDB = os.path.join(workspace, "autoLayers.gdb")
arcpy.env.workspace = autoLayerGDB # here I assumed you wanted to output to the GDB
arcpy.env.overwriteOutput = True
mxd_path = workspace # mxd can't be in a GDB

SOURCE_DATA = r"\\coacd.org\dfs\SWS\Code Enforcement\ACD Admin Operations\GIS\Operations\Council District Analysis Reporting\Auto_HeatMap_WIP\Source_Data\SOURCE_DATA.xlsx"

spatial_ref = arcpy.SpatialReference("WGS 1984")
output_layer = "sample_xy_points"
in_x_field = "Longitude"
in_y_field = "Latitude"
arcpy.MakeXYEventLayer_management(SOURCE_DATA, in_x_field, in_y_field, output_layer, spatial_ref)
#Copy output to make this permanent and assign an object ID field
arcpy.CopyFeatures_management(output_layer, "coded_xy_points")
#project layer into Texas FIPS     
repr_spatial_ref = arcpy.SpatialReference("NAD 1983 StatePlane Texas Central FIPS 4203 (US Feet)")
arcpy.Project_management(output_layer, "coded_xy_points_FIPS4203", repr_spatial_ref)

Answer 2

autoLayer is a path for a geodatabase. It needs to be the path for a table.

autoLayer = r"\\coacd.org...\autoLayers.gdb\OutputTable"