Converts all the sheets in a spreadsheet to file gdb tables and displays the XY coordinates in ArcMap.

convertSheetsAndDisplayXY.py

#
# @date 28/07/2015
# @author Cindy Williams
#
# Converts all the sheets in a spreadsheet 
# to file gdb tables and displays the XY 
# coordinates in ArcMap.
#
# Assumes the spreadsheet has been formatted
# according to GIS standards.
#
# For use in the Python window in ArcMap.
#

import arcpy
import xlrd

arcpy.env.workspace = r"C:\Some\Arb\Folder\work.gdb"

sr = arcpy.SpatialReference(4326)
xls = r"C:\Some\Arb\Folder\Coords.xlsx"
wb = xlrd.open_workbook(xls)
sheets= [sheet.name for sheet in wb.sheets()]
field_x = "Long"
field_y = "Lat"

for sheet in sheets:
  print("Processing " + sheet)
  tbl = "tbl_" + sheet
  try:
    arcpy.conversion.ExcelToTable(xls, tbl, sheet)
    print("Converted " + sheet)
    arcpy.management.MakeXYEventLayer(tbl, field_x, field_y, sheet, sr)
    print("Displaying XY")
  except Exception as e:
    print(e)
    
print("Script complete.")