Exporting multiple feature classes with joined fields using ArcPy?

Question

I have multiple FGBs (10) with many feature classes (about 30). Most of the feature classes are joined to a table which is specific to each feature class (i.e. 30 feature classes to 30 tables). I would like to make the joined fields permanent so that I can merge the datasets as required.

The manual process to do this would involve r-click > 'export data' for each feature class in the TOC but this is not feasible given the number of datasets.

Based on esri help pages and other SE threads I started with this code to copy features:

# Copy features from a FGD to FGD

import arcpy
import os

# Set environment settings
arcpy.env.workspace = "C:/GIS/Groundwater_Inventory/Data/GW_Theme_Recharge/Study_Areas/Recharge_Summary_areas.gdb"
arcpy.env.qualifiedFieldNames = False

# Set local variables
out_workspace = "C:/GIS/Workspace/Inventory_data/Recharge_simple.gdb"

# generate a list of features in the workspace 
fc_list = arcpy.ListFeatureClasses()

# Execute CopyFeatures for each input feature
for feature in fc_list:
    out_featureclass = os.path.join(out_workspace, os.path.splitext(feature)[0])
    arcpy.CopyFeatures_management(feature, out_featureclass)

Images of tables and feature classes attached. I suspect I need to recreate the join in this script but cannot find any threads which describe this process.

I am using ArcGIS, with entry level python/programming experience.

EDIT: I resolved this issue by copying the required data in the tables into a single csv file using a VBA script based on this help page: https://www.thespreadsheetguru.com/the-code-vault/2014/4/23/loop-through-all-excel-files-in-a-given-folder

I then merged all feature classes and from there I could perform a simple attribute join to the new table.

Answer 1

Note: The response below is for ArcGIS Pro 2.x, check the Esri help pages for your ArcGIS version to make sure there aren't any syntax differences (I think it's the same, but just in case).

Since you have more than one table and I assume that the field names between the tables may also change, within your loop you should identify the field name(s) for the layer and table. You'll also need to make sure you're matching up against the correct table. I assume that the layer and table match based on the R## at the beginning, but modify if that's not the case.

Similar to how you've used ListFeatureClasses, you can use ListTables to find all of the tables in your workspace. You can also use ListFields to identify field names.

I've made a first pass at modifying your code, but haven't tested so please research/update/add questions if there are questions or errors.

import arcpy
import os

# Set environment settings
arcpy.env.workspace = "C:/GIS/Groundwater_Inventory/Data/GW_Theme_Recharge/Study_Areas/Recharge_Summary_areas.gdb"
arcpy.env.qualifiedFieldNames = False

# Set local variables
out_workspace = "C:/GIS/Workspace/Inventory_data/Recharge_simple.gdb"

# generate a list of features in the workspace 
fc_list = arcpy.ListFeatureClasses()
# generate a list of Tables in the workspace
tbl_list = arcpy.ListTables()

# Execute CopyFeatures for each input feature
for feature in fc_list:
    fcbasename = os.path.splitext(feature)[0]
    # Figure out which R# this feature is, to match with table
    tblnum = fcbasename.split('_')[0]
    # find the table. There is probably a more pythonic way to do this. (Could use regular expressions or list comprehension as an example)
    # may need to adjust if the table list has the path too.
    for tblname in tbl_list:
        if tblname.startswith(tblnum):
            selectedtbl = tblname
            break
    # Add Join. assumes you already know the field names. 
    # If you don't, use ListFields here, separately, on the feature and the table
    arcpy.management.AddJoin(feature, "FieldNameToJoin_Layer1", selectedtbl, "FieldNameToJoin_Table1", "KEEP_ALL")
    out_featureclass = os.path.join(out_workspace, os.path.splitext(feature)[0])
    arcpy.CopyFeatures_management(feature, out_featureclass)