Convert spreadsheets to GDB tables and display XY

I’m sick today, so I decided to choose one of the simpler scripts for today’s post. This one automates a common task of the GIS professional: converting a spreadsheet of coordinates to GIS. The script assumes you’ve formatted each sheet appropriately.

	#
	# @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.")

view raw

convertSheetsAndDisplayXY.py

hosted with ❤ by GitHub

For each sheet in the workbook, the script creates a file geodatabase table, and displays the coordinates as a layer in ArcMap using the WGS 1984 spatial reference. The script is very basic, and therefore will be easy to change to suit your own data.