schulthe

Problem with xlsx file conversion and creation of XY event layer

Discussion created by schulthe on Feb 28, 2012
Latest reply on Mar 8, 2013 by khibma-esristaff
I have a bunch of xlsx files with XY data for health care facilities. As an exercise to learn python better, I'm trying to (1) convert the xlsx files to dBase files, (2) create an event layer from the dBase files using the XY attribute fields, and (3) copy the event layers to shp files. Any help would be greatly appreciated. My code is bellow (note: all xlsx files have a single sheet called 1. The XY data in the xlsx files are in fields/columns POINT_X and POINT_Y.


import arcpy, os, sys, traceback

arcpy.env.workspace = 'D:\\AG_Vet\\ForImportGDB\\'
outPath = 'D:\\AG_Vet\\ForImportGDB\\Output\\'

# Local variables

xVal = "POINT_X"
yVal = "POINT_Y"

# Factory code for NAD 1983
factory_code_CS = 4269

sr = arcpy.SpatialReference()
sr.factoryCode = factory_code_CS
sr.create()

# List all files in folder then list all tables in file
fileList = arcpy.ListFiles("*.xlsx")
print 'Listing .xlsx files in workspace'

try:

    # Loop through each file
    for file in fileList:

        # Use splitext to set the output name of the table
        dbfFile = os.path.splitext(file)[0] + ".xlsx"

        # Convert excel to DBF
        arcpy.TableToTable_conversion(file, outPath, dbfFile+ ".dbf")

        print 'File conversion. Converted ' + file

    #List dBase tables
    tableList = arcpy.ListTables('',dBase)
    print ' List dBase tables in workspace'

    # Loop through each table
    for table in tableList:
    
        # Make XY event layer
        arcpy.MakeXYEventLayer_management(table, xVal, yVal, "points_" + table, sr)

        # Save output
        arcpy.CopyFeatures_management("points_"+table, outpath + table)

        print 'Created XY shp file for ' + table

    print 'Script complete.'


except:

    tb = sys.exc_info()[2]
    tbinfo = traceback.format_tb(tb)[0]
    pymsg = "PYTHON ERRORS:\nTraceback Info:\n" + tbinfo + "\nError Info:\n     " +        str(sys.exc_type) + ": " + str(sys.exc_value) + "\n"
    msgs = "ARCPY ERRORS:\n" + arcpy.GetMessages(2) + "\n"

    arcpy.AddError(msgs)
    arcpy.AddError(pymsg)

    print msgs
    print pymsg
    
    arcpy.AddMessage(arcpy.GetMessages(1))
    print arcpy.GetMessages(1)

Outcomes