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.|
|# 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:|
|arcpy.AddMessage("Processing " + f)|
|xls = os.path.join(root, f)|
|name = os.path.splitext(f)|
|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)|
|e = sys.exc_info()|
|arcpy.AddMessage("Error caught: ")|
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.