The Table to Excel tool from the Conversion toolbox is a stalwart amongst Geoprocessing tools but it does have its limitations, namely; you cannot subset the records directly with the tool itself, you cannot subset the columns to be exported and the order that you want them in the spreadsheet, and you cannot sort the output by a column(s) in ascending or descending order. Today, we are going to look at extending the functionality of the Table to Excel tool to overcome the limitations listed above. You can check out the Esri documentation for the Table to Excel tool here.
If you’re keen on learning ArcPy, check out this course.
We start by importing the ArcPy module.
import arcpy
Next, we require seven user-input parameters. The first four are the parameters for the original Table to Excel tool. We then add a where_clause to subset the records, a sort_field to sort the output records as desired, and out_flds to choose which fields and and in what order they will appear in the output. These three additional parameters will be set to optional along with the fld_alias and descriptions parameters as per the Esri documentation for the Table to Excel tool.
## the table to export to an Excel file
input_tbl = arcpy.GetParameterAsText(0)
## the filepath for the output Excel
out_xl = arcpy.GetParameterAsText(1)
## use field names field alias names as column headers
fld_alias = arcpy.GetParameterAsText(2)
## domain and subtype codes or descriptions as attribute output
descriptions = arcpy.GetParameterAsText(3)
## where clause to subset records based on SQL expression
where_clause = arcpy.GetParameterAsText(4)
## field(s) to sort by ascending or descending
sort_field = arcpy.GetParameterAsText(5)
## the subset and order of the fields to export
out_flds = arcpy.GetParameterAsText(6)
Now we dive into the belly of the beast. First up, we want to get a list of the field names and order that they will appear in the output Excel file. This section is two-fold, the user has either entered specific fields, in which case we will use FieldMappings and specified the order, or the parameter has not been used, in which case, all fields in their current order will be used. If the user chooses to export an OID field then we need to account for this too as the original OID values can be lost in translation. Check out the Esri documentation for FieldMappings here.
## get the OID field to check if the user wants as part of the output
oid_fld = [fld.name for fld in arcpy.ListFields(input_tbl) if fld.type == "OID"][0]
## if the user has defined fields to export
if out_flds:
## create a list from the out_flds parameter.
xl_flds = out_flds.replace("'", "").split(";")
## instantiate a FieldMappings object
field_mappings = arcpy.FieldMappings()
## for each field that is required in the output
for fld in xl_flds:
## handle the OID and call new field ORIG_OID
if fld == oid_fld:
fm = arcpy.FieldMap()
fm.addInputField(input_tbl, fld)
newField = fm.outputField
newField.name = "ORIG_OID"
newField.type = "LONG"
fm.outputField = newField
field_mappings.addFieldMap(fm)
## otherwise add a FieldMap to the FieldMappings for each field from the user input
else:
fm = arcpy.FieldMap()
fm.addInputField(input_tbl, fld)
field_mappings.addFieldMap(fm)
## if no selection is made, all fields are required for export
else:
xl_flds = [fld.name for fld in arcpy.ListFields(input_tbl)]
## set the field_mappings variable to None
field_mappings = None
The Export Table tool will aid in creating a subset of records, limiting the fields, and sorting the field(s), so basically doing most of the heavy lifting. Check out this post on using the Export Table to sort output by multiple fields, and also check out the Esri documentation for the tool itself. We will also take advantage of the memory workspace so this part is not written to disk. In this section we account for the OID field if specified by the user in the out_flds.
## add table to memory, subset with where_clause,
## limit field with field mappings and sort with sort_field
tbl = arcpy.conversion.ExportTable(input_tbl, "memory\\tbl", where_clause, field_mapping=field_mappings, sort_field=sort_field)
## if the OID field was selected by the user then alter the original OID
## field name with ORIG_OID
if out_flds and oid_fld in out_flds:
xl_flds = list(map(lambda x: x.replace(oid_fld, "ORIG_OID"), xl_flds))
The next task is to make a Table View with the fields from the Table in the memory workspace in order and whether they have a visible or hidden flag. We achieve this with a combination of a FieldInfo object and the Make Table View tool from the Data Management toolbox. Check out the Esri documentation for the FieldInfo object here, and the Make Table View here.
## get a list of the fields from the memory table
flds = arcpy.ListFields(tbl)
## instantiate a FieldInfo object
fld_info = arcpy.FieldInfo()
## for each field in the memory table
for fld in flds:
## if the field is required in the export
if fld.name in xl_flds:
## set with a visible flag
fld_info.addField(fld.name, fld.name, "VISIBLE", "")
## otherwise set to hidden
else:
fld_info.addField(fld.name, fld.name, "HIDDEN", "")
## create a Table View from the memory table based on the FieldInfos
out_subset = arcpy.management.MakeTableView(tbl, "Table_View", field_info=fld_info)
Time for the main event, we use the out-of-the-box Table to Excel tool.
## export to Excel
arcpy.conversion.TableToExcel(out_subset, out_xl, fld_alias, descriptions)
And just a little memory workspace clean-up to finish-off.
## clean up memory workspace
arcpy.management.Delete(tbl)
You can achieve most of the above by just using the Export Table tool with a where_clause, field_mapping, and sort_field, you can even use the field alias names, however, you need to use the Transfer Field Domain Descriptions environment setting and this will add an extra field to the output. The Export Table tool cannot export to Excel but can to CSV. And besides all that, its fun using and learning ArcPy.
Save your script and open ArcGIS Pro. Right-click on your Toolbox of choice and select New > Script. The New Script window will appear. In the General tab, set the Name to tableToExcelExtra, the Label to Table to Excel (Extra), and the Description to Extend the functionality of the Table to Excel tool to allow subset of records and fields, and to order output records by fields.
Click into the Parameters tab and set as per below.
Input Table has a Data Type : Table View and Type : Required
Output Excel File has a Data Type : File, Type : Required, and Filter set to xls;xlsx
Use field alias as column header has Data Type : String, Type : Optional, and Filter set to a Value List of NAME and ALIAS, and Default : NAME
Use domain and subtype description has Data Type : String, Type : Optional, and Filter set to a Value List of CODE and DESCRIPTION, and Default : CODE
Where Clause has Data Type : SQL Expression,Type : Optional, and Dependecy : input_tbl
Sort Field(s) has Data Type : Value Table (1), Type : Optional, a Filter set for the Fields (2) (Short, Long, Float, Double, Text, Date, OID, GUID, GlobalID), a Filter set for Value List (3) of ASCENDING and DESCENDING, and Dependency : input_tbl
Output Field(s) has Data Type : Fields (Make sure to set to Multiple Values), Type : Optional, a Filter set for the Fields (Short, Long, Float, Double, Text, Date, OID, GUID, GlobalID), and Dependency : input_tbl
In the Execution tab, click the folder icon in the top-right corner and add your saved Python script. Go forth and give it a whirl!
To Note: The sorting fields must also be present in the output fields or your data will not be sorted.
You can download the tool and other custom tools over on this page.
Here’s the script above in its entirety and you can download the Tool.
import arcpy
################################################################################
## Esri Documentation
## https://pro.arcgis.com/en/pro-app/latest/arcpy/functions/getparameterastext.htm
## https://pro.arcgis.com/en/pro-app/latest/arcpy/functions/listfields.htm
## https://pro.arcgis.com/en/pro-app/latest/arcpy/classes/fieldmappings.htm
## https://pro.arcgis.com/en/pro-app/latest/arcpy/classes/fieldmap.htm
## https://pro.arcgis.com/en/pro-app/latest/tool-reference/conversion/export-table.htm
## https://pro.arcgis.com/en/pro-app/latest/arcpy/classes/fieldinfo.htm
## https://pro.arcgis.com/en/pro-app/latest/tool-reference/data-management/make-table-view.htm
##
## ArcGIS Pro Version: 3.1.0
##
################################################################################
################################################################################
## USER INPUTS / PARAMETERS
## the table to export to an Excel file
input_tbl = arcpy.GetParameterAsText(0)
## the filepath for the output Excel
out_xl = arcpy.GetParameterAsText(1)
## use field names field alias names as column headers
fld_alias = arcpy.GetParameterAsText(2)
## domain and subtype codes or descriptions as attribute output
descriptions = arcpy.GetParameterAsText(3)
## where clause to subset records based on SQL expression
where_clause = arcpy.GetParameterAsText(4)
## field(s) to sort by ascending or descending
sort_field = arcpy.GetParameterAsText(5)
## the subset and order of the fields to export
out_flds = arcpy.GetParameterAsText(6)
################################################################################
## DEFINE FIELD REQUIREMENTS
## get the OID field to check if the user wants as part of the output
oid_fld = [fld.name for fld in arcpy.ListFields(input_tbl) if fld.type == "OID"][0]
## if the user has defined fields to export
if out_flds:
## create a list from the out_flds parameter.
xl_flds = out_flds.replace("'", "").split(";")
## instantiate a FieldMappings object
field_mappings = arcpy.FieldMappings()
## for each field that is required in the output
for fld in xl_flds:
## handle the OID and call new field ORIG_OID
if fld == oid_fld:
fm = arcpy.FieldMap()
fm.addInputField(input_tbl, fld)
newField = fm.outputField
newField.name = "ORIG_OID"
newField.type = "LONG"
fm.outputField = newField
field_mappings.addFieldMap(fm)
## otherwise add a FieldMap to the FieldMappings for each field from the user input
else:
fm = arcpy.FieldMap()
fm.addInputField(input_tbl, fld)
field_mappings.addFieldMap(fm)
## if no selection is made, all fields are required for export
else:
xl_flds = [fld.name for fld in arcpy.ListFields(input_tbl)]
## set the field_mappings variable to None
field_mappings = None
################################################################################
## CREATE MEMORY TABLE (with SUBSET OF RECORDS / FIELDS)
## add table to memory, subset with where_clause,
## limit field with field mappings and sort with sort_field
tbl = arcpy.conversion.ExportTable(input_tbl, "memory\\tbl", where_clause,
field_mapping=field_mappings, sort_field=sort_field)
## if the OID field was selected by the user then alter the original OID
## field name with ORIG_OID
if out_flds and oid_fld in out_flds:
xl_flds = list(map(lambda x: x.replace(oid_fld, "ORIG_OID"), xl_flds))
################################################################################
## MAKE TABLE VIEW with VISIBLE/HIDDEN TAGS
## get a list of the fields from the memory table
flds = arcpy.ListFields(tbl)
## instantiate a FieldInfo object
fld_info = arcpy.FieldInfo()
## for each field in the memory table
for fld in flds:
## if the field is required in the export
if fld.name in xl_flds:
## set with a visible flag
fld_info.addField(fld.name, fld.name, "VISIBLE", "")
## otherwise set to hidden
else:
fld_info.addField(fld.name, fld.name, "HIDDEN", "")
## create a Table View from the memory table based on the FieldInfos
out_subset = arcpy.management.MakeTableView(tbl, "Table_View", field_info=fld_info)
################################################################################
## EXPORT TO EXCEL
arcpy.conversion.TableToExcel(out_subset, out_xl, fld_alias, descriptions)
################################################################################
## CLEAN UP - AISLE 5
## clean up memory workspace
arcpy.management.Delete(tbl)
################################################################################
2 thoughts on “ArcGIS Pro: Table to Excel tool Extended Functionality with ArcPy”
Hi Glen. Thanks so much for this tool, already using it! Just one little remark is that, in the parameters, the Output Field(s) needs to have the Multiple values check box ticked otherwise only one field is selectable.
Thanks Annie, I have updated. Great to hear that the tool is useful.