Convert multiple spreadsheets to geodatabase tables

For years I used the old ArcGIS way of connecting ArcGIS to Excel – saving the tables as dbf files. That was terrible. In 10.2 ESRI added a new conversion toolset for moving back and forth between Excel and ArcGIS.

It is a set of Python scripts using xlrd/xlwt. For a while, I was reading in individual sheets into memory and doing things with them using openpyxl. While that workflow is still valid, most of the time I actually want a gdb table copy of my spreadsheet before I start working with it.

I received a folder containing multiple subfolders with months/years in the folder names. Each subfolder contained multiple spreadsheets with different names. The spreadsheets followed two different field naming conventions.

Since I was not going to be manually processing these dozens of files, I wrote this script to help me.

#
# @author Cindy Williams
# @date 14/11/2014
#
# Takes a folder containing deeds data in spreadsheet
# format and converts it into a GIS table for further processing.
# It must adhere to a specific format.
#
# For use as a script tool in an ArcGIS Toolbox.
#
import arcpy
import os
import sys
# Input variables
fld = arcpy.GetParameterAsText(0)
gdb = arcpy.GetParameterAsText(1)
sheet_name = "Sheet"
for root, dirs, filenames in os.walk(fld):
for f in filenames:
try:
arcpy.AddMessage("Processing " + f)
xls = os.path.join(root, f)
name = os.path.splitext(f)[0]
for i in name:
if i in ["-", " ", "."]:
name = name.replace(i, "_")
tbl = os.path.join(tbl, name)
arcpy.conversion.ExcelToTable(xls, tbl, sheet_name)
arcpy.AddMessage("Processed " + name)
except:
e = sys.exc_info()[0]
arcpy.AddMessage("Error caught: ")
arcpy.AddMessage(e)
arcpy.AddMessage("Processing complete.")

view raw
convertXLStoGIS.py
hosted with ❤ by GitHub

The script is fairly simple. It loops over all the files in the main folder, as well as recursively into the subfolders. Any characters ArcGIS does not like are replaced with underscores, and each sheet is converted to a geodatabase table.

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.