Join multiple tables to multiple feature classes arcpy

[Jörn]

Hi!

I have a list of tables and a list of feature classes and I would like to join them, based on their names. The tables and feature classes I would like to join have the same name, but the tables hold and additional annex. I think my problem is similar to the one I found here already (https://community.esri.com/t5/python-questions/join-multiple-tables-to-multiple-feature-classes-arcp...). I tried to copy the script and adapt it to my needs. The script is running without errors but I dont get the results. I would be very gratefull for suggestions. I would also appreciate a solution in model builder where I also could not find the way. Thank you!!

I use ArcMap 10.7 and run the script in the ArcMap python console. 

import arcpy

#Define Geodatabase Path
featureGDB = r"C:\C_M2\arcpy\list\list_Events_SBA_wp.gdb"
tableGDB = r"C:\C_M2\arcpy\list\list_distance.gdb"

#Create Feature List Variable
arcpy.env.workspace = featureGDB
featureList = arcpy.ListFeatureClasses()

#Create Table List Variable
arcpy.env.workspace = tableGDB
tableList = arcpy.ListTables()

#Set table Names to featureList Names equivalent
for table in tableList:
    tableNum = table [0:-5]
#Finde Table and Feature from Lists with same Name and join them   
    for feature in featureList:
        if feature == "%tableNum%":
            arcpy.JoinField_management(in_data="C:\\C_M2\\arcpy\\list\\list_Events_SBA_wp.gdb\\%WP_20200723_001%", in_field="OBJECTID", join_table="C:\\C_M2\\arcpy\\list\\list_distance.gdb\\%WP_20200723_001_dist%", join_field="OBJECTID", fields="DISTANCE")

[DavidPike]

I've not really studied the code, but the wildcard operator % will not work (to my knowledge).

You would need a regular expression or something like:

if tableNum in feature:

[Jörn]

Thank you @DavidPike for the suggestion. But do you think that the "%" operator works in the path name of the parameters for joinfield_management? The idea was to use the "%" operator in substition to the f' string, which was used in the posted solution in the link. I think f' strings only work in Python 3 and my version is 2.7.

[DavidPike]

why not 

if feature == tableNum:

?

And what are the % signs doing in your path names for the join?  I think you're mixed up with model builder inline variable substitution - basically get rid of all the % signs, I have no idea what they're doing.  the f string in Joe's post was to place a 'T' before the variable, from what I see, you don't need to do anything like that.

You also have a mix of raw and non raw encoded paths, I prefer r'' paths as they look cleaner.

import arcpy

#Define Geodatabase Path
featureGDB = r"C:\C_M2\arcpy\list\list_Events_SBA_wp.gdb"
tableGDB = r"C:\C_M2\arcpy\list\list_distance.gdb"

#Create Feature List Variable
arcpy.env.workspace = featureGDB
featureList = arcpy.ListFeatureClasses()

#Create Table List Variable
arcpy.env.workspace = tableGDB
tableList = arcpy.ListTables()

#Set table Names to featureList Names equivalent
for table in tableList:
    tableNum = table [0:-5]
#Finde Table and Feature from Lists with same Name and join them   
    for feature in featureList:
        if feature == tableNum:
            in_data = r"C:\C_M2\arcpy\list\list_Events_SBA_wp.gdb\WP_20200723_001"
            in_field = "OBJECTID"
            join_table = r"C:\C_M2\arcpy\list\list_distance.gdb\WP_20200723_001_dist"
            join_field = "OBJECTID"
            fields = ["DISTANCE"]
            arcpy.JoinField_management(in_data, in_field, join_table, join_field, fields)

[Jörn]

Thanks a lot @DavidPike  and @JoeBorgione !

I  indeed am confusing with model builder ... 

Now, as far as I can interpret the results, the table "WP_20200723_001_dist" joins the "WP_20200723_001" feature the number of times I have features in my featureList (98).

What I want is, that "WP_20200723_001_dist" and "WP_20200723_001" only join one time and that in the second "round" "WP_20200723_002_dist" joins "WP_20200723_002" and so forth till "WP_20200723_098_dist" finally joins "WP_20200723_098".

I thought that the for in statement is useful for this as it iterates through the lists one by one, if I got it right. But I am still unaware of how I should describe the data path dynamically in a form to receive the desired results 🙄

[JoeBorgione]

F strings are new to python 3.x but the format() method is it's predecessor in 2.x python.

[Jörn · Accepted solution]

Ok I think I got it:

import arcpy

#Define Geodatabase Path
featureGDB = r"C:\C_M2\arcpy\list\list_Events_SBA_wp.gdb"
tableGDB = r"C:\C_M2\arcpy\list\list_distance.gdb"

#Create Feature List Variable
arcpy.env.workspace = featureGDB
featureList = arcpy.ListFeatureClasses()

#Create Table List Variable
arcpy.env.workspace = tableGDB
tableList = arcpy.ListTables()

#Set table Names to featureList Names equivalent
for table in tableList:
    tableNum = table [0:-5]
#Finde Table and Feature from Lists with same Name and join them   
    for feature in featureList:
        if feature == tableNum:
            in_data = featureGDB + "\\" + feature
            in_field = "OBJECTID"
            join_table = tableGDB + "\\" + table
            join_field = "OBJECTID"
            fields = ["DISTANCE"]
            arcpy.JoinField_management(in_data, in_field, join_table, join_field, fields)

Thank you!!