Converting *.xlsx datasheet with point feature data into POINT feature class using ArcPy?

Question

I am trying to create a script tool to automate this process, eventually it will be used for batch processing but at the moment I can't even get test data to work. This is pretty basic at the moment, and I know it can be done through ArcGIS, but I would like to develop a python script for processing. My code is as below: It completes without error, but I am expecting the creation of a feature layer (even better would be a feature class with POINT geometry). I can't find or access the output of the SaveToLayerFile step.

#Import libraries
import arcpy
import os
import sys
arcpy.env.overwriteOutput = True

#Set workspace
home = arcpy.env.workspace
arcpy.env.workspace = arcpy.GetParameterAsText(0)
mxd = arcpy.mapping.MapDocument("Current")
df = arcpy.mapping.ListDataFrames(mxd, "*")[0]


#Create spatial reference object
prjFile = arcpy.GetParameterAsText(2)
spatialRef = arcpy.SpatialReference(prjFile)
arcpy.AddMessage('Setting Spatial Reference')

#Set Excel Datasheet for Migration
TEST = arcpy.GetParameterAsText(1)

try:
# Process: Table to Table Conversion with destination variable
    FarmerTable = 'Farmers1'
    arcpy.AddMessage('Migrating Data...')

    arcpy.TableToTable_conversion (TEST, home, FarmerTable)
except:
    arcpy.GetMessages()


# Set local variables
in_layer = "FarmerLyr"
out_layer = "FarmerLyr.lyr"
out_layer0 = "FarmerLyr"


try:   
#Make XY Events of each Farmer
    arcpy.AddMessage('Creating Features...')

    result = arcpy.MakeXYEventLayer_management(FarmerTable, "X", "Y", out_layer0, spatialRef, "")
    result.getOutput(0) = out_layer0
    arcpy.AddMessage(arcpy.GetCount_management(out_layer0))
    arcpy.AddMessage('Successfully Created Features')

    # Execute SaveToLayerFile
    arcpy.SaveToLayerFile_management(out_layer0, out_layer, "ABSOLUTE")
except:
    print arcpy.GetMessages()

Answer 1

I think your problem starts when you are doing the conversion of the .xlsx file to a table. The line where you define the home variable, is as follows:

#Set workspace
home = arcpy.env.workspace
arcpy.env.workspace = arcpy.GetParameterAsText(0)

You need to swap the setting of the environment variable, with the setting of the home variable, like this:

#Set workspace
arcpy.env.workspace = arcpy.GetParameterAsText(0)
home = arcpy.env.workspace

This way, the home variable will not be set equal to an empty environment setting.

The next potential problem that I see is where you define the name of your output table.

FarmerTable = 'Farmers1'

If you are adding this table to a geodatabase, then there is not an issue. If you are simply saving to a file system directory, then you need to append a .dbf to save as a DBase file

One other place that might throw an error is where you call the Make XY Event Layer

result = arcpy.MakeXYEventLayer_management(FarmerTable, "X", "Y", out_layer0, spatialRef, "")
result.getOutput(0) = out_layer0

According to the Help Document about Using Tools in Python,

ArcPy returns the output values of a tool when it is executed as a Result object. The advantage of a result object is that you can maintain information about the execution of tools, including messages, parameters, and output. These results can be maintained even after several other tools have been run.

The result object's getOutput method returns a Unicode string from result objects that have output values.

When you call the getOutput method, you are returning the name of your output layer. Trying to set the method to be equal to a layer may cause a problem. If you wanted, you could set a new variable equal to the output layer name, though you don't really need to as it is equal to the existing variable for your output layer name.

newoutlayername = result.getOutput(0)

Take a look at those items and see if they make a difference. Something that has worked for me is to take a script like this a step at a time.

1. Work on just the table conversion code, until you know that it works.
2. Add in the code for adding the XY Event table until that completes successfully.
3. Now place the code to save this new layer out to a .lyr file

Breaking it up like this can make the debugging process far easier. Lastly, consult the ESRI help documentation if you are unsure about a function's parameters or other information. Almost all of the tools have a code sample at the end for how to implement them in python, and many other operations include similar snippets.

Answer 2

A lot of your difficulties are actually addressed in the help in some form or another.

For example, instead of Save To Layer File, which, just as it sounds, creates a layer file (not a feature class), use one of the geoprocessing tools mentioned in this excerpt from the Make XY Event Layer help:

• The output point feature layer created by this tool is temporary and will not persist after the session ends. You can export this event layer to a feature class on disk using the Copy Features, Feature to Point, or Feature Class to Feature Class tool.

You can also skip the step where you copy the Excel spreadsheet to a table, as you can read Excel spreadsheets directly as table views: Understanding how to use Microsoft Excel files in ArcGIS

Additionally you make a common mistake with error handlers that do nothing but allow the program to continue even though it may be in an invalid state.

Instead of using try/except around various GP processes, use a single, top-level error handler or no error handler at all, to ensure that errors bubble all the way up the call stack and abort program execution.

I typically don't use error handlers at all in my GP scripts because unless you are looking for specific errors after specific function calls, all it really tends to do is obfuscate errors and make debugging more difficult.

Answer 3

This is a cleaner version of the code that ran successfully

#Import libraries
import arcpy, os, sys

arcpy.env.overwriteOutput = True

#Set workspace  
arcpy.env.workspace = arcpy.GetParameterAsText(0)
home = arcpy.env.workspace

#Create spatial reference object
prjFile = arcpy.GetParameterAsText(2)
spatialRef = arcpy.SpatialReference(prjFile)

#Set Excel Datasheet for Migration
TEST = arcpy.GetParameterAsText(1)

#Set Layer Name variable
FarmerLayernm = "FarmerLayer"

#Make XY Events of each Farmer
arcpy.MakeXYEventLayer_management(TEST, "X", "Y", FarmerLayernm, spatialRef, "")

#This tool will migrate and save the data to disk (as a shapefile I believe)
out_name = "FarmerPoints"
arcpy.FeatureClassToFeatureClass_conversion (FarmerLayernm, home, out_name, "", "", "")