You were very close, just a few workspace issues (you need to change the workspace to the actual .xlsx file to access the sheet, then back to the folder level to export the table and create the shapefile) and a couple small things. Give this a try, changing to your paths of course.import arcpy, os, sys, traceback
arcpy.env.workspace = r"C:\GIS\xlsxTest"
outPath = r"C:\GIS\xlsxTest\output"
arcpy.env.overwriteOutput = True
# 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:
print file
arcpy.env.workspace = os.path.join(r"C:\GIS\xlsxTest", file)
# Use splitext to set the output name of the table
dbfFile = os.path.splitext(file)[0] #+ ".xlsx"
if arcpy.Exists(dbfFile+".dbf"):
print "deleting"
arcpy.Delete_management(dbfFile+".dbf")
# Convert excel to DBF
arcpy.TableToTable_conversion("Sheet1$", outPath, dbfFile+ ".dbf")
print 'File conversion. Converted ' + file
#List dBase tables
arcpy.env.workspace = outPath
tableList = arcpy.ListTables("", "dBASE")
print ' List dBase tables in workspace'
# Loop through each table
for table in tableList:
print table
# Make XY event layer
arcpy.MakeXYEventLayer_management(table, xVal, yVal, "points_" + table, sr)
# Save output
arcpy.FeatureClassToShapefile_conversion("points_"+table, outPath)
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)