Extend the Functionality of the Table to Excel tool in ArcGIS Pro with ArcPy

Table of Contents

Introduction

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.

Leverage ArcPy for geospatial data management workflows within ArcGIS Pro. Learn the fundamentals of utilising ArcGIS Pro geoprocessing tools with ArcPy for data management, conversions, and analysis. This course is packed with amazing content that will help you discover the power of automating tasks within the ArcGIS Pro environment. Take your ArcPy skills from beginner to snake charmer. A little code goes a long way, a little ArcPy provides you with an in-demand skill. Sign up now for our highly rated course.

Custom Tool Syntax

The syntax for the Table to Excel (Extra) tool is as follows…

				
					tableToExcelExtra(
    input_table,
    out_xl,
    {fld_alias},
    {descriptions},
    {where_clause},
    {sort_field},
    {out_flds}
)
				
			

We will utilise seven user-input parameters, two required, and five optional. 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 two required parameters are the input_table and the out_xl.

Import the ArcPy module

ArcPy is a Python site package that enables automation, analysis, and management of geographic data within the ArcGIS software environment. 

				
					import arcpy
				
			

Define the 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)
				
			

Required Python object for the tool

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
				
			

Add the input_table to the memory workspace

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

Make Table View for Custom Output

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)
				
			

The Main Event: Export to Excel

Not much to see here!  we use the out-of-the-box Table to Excel tool.

				
					## export to Excel
arcpy.conversion.TableToExcel(out_subset, out_xl, fld_alias, descriptions)
				
			

Clean-up memory workspace

Not much to see here!  we use the out-of-the-box Table to Excel tool.

				
					## 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_clausefield_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.

ArcGIS Pro Definition Query Masterclass with ArcPy & Python

Unlock the power of Definition Queries in ArcGIS Pro through this comprehensive course that dives deep into the intricacies of managing and utilizing definition queries with spatial data using ArcPy and Python scripting. Definition Queries provide a dynamic way to filter and display specific subsets of your data, enabling you to create more insightful maps and analyses. In this course, we will focus exclusively on Definition Queries, covering their creation, modification, and application through hands-on exercises and real-world scenarios. Accredited by the Association for Geographic Information (AGI) for 1 CPD point.

Create the tool in ArcGIS Pro

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 : FileType Required, and Filter set to xls;xlsx

Use field alias as column header has Data Type : StringType Optional, and Filter set to a Value List of NAME and ALIAS, and Default NAME

Use domain and subtype description has Data Type : StringType 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!

				
					    def initializeParameters(self):
        # Customize parameter properties. This method gets called when the
        # tool is opened.
        self.params[5].enabled = False
        return
 
    def updateMessages(self):
        # Modify the messages created by internal validation for each tool
        # parameter. This method is called after internal validation.
        if self.params[1].value:
            self.params[1].clearMessage()
        if self.params[3].value:
            self.params[3].clearMessage()
        return
				
			

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. This tool is in the Custom Tools on a Basic License with ArcPy section.

All the code in one place

You can find the entire code workflow below with links to important components in the documentation that were used.

				
					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)
 
################################################################################
				
			

Leave a Comment

Your email address will not be published. Required fields are marked *