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

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.