Join multiple tables to multiple feature classes arcpy

[NataliaGutierrez1]

Hello,

I need help to create a script that would allow me to loop through multiple tables in a geodatabase and join them to the correct feature class in another geodatabase. There are in total 77 tables and 77 feature classes. I would tell GIS to join those where a portion of the table name matches a portion of the Feature class name. 

For example:

      Table Name 1: "NAL11F201901"

      Feature class 1: "T11"

       Table Name 2: "NAL12F201901

       Feature Class 2: "T12"

Important:

• All of the tables and the feature classes have the same unique field name. 
• I need to join all of the fields. 
• The join needs to be permanent.

I am not sure if Add join is better to use or if I should use Join field.

I don't really know what to do next.

I would really appreciate any help with this. 

Thanks!

import arcpy

in_features = r"D:\APRX_MXDS\USA_Parcels_2019_Project\Florida_Parcels_Per_County_Final.gdb"
join_tables = r"D:\APRX_MXDS\USA_Parcels_2019_Project\Florida_Property_Data_Per_County.gdb"

list_infeatures = arcpy.ListFeatureClasses()
list_join_tables = arcpy.ListTables(*NAL)
UniqueField = "CO_PARCELID"

for fc in list_infeatures:
    arcpy.AddJoin_management()‍‍‍‍‍‍‍‍‍‍‍

[JoeBorgione]

I'll start the discussion with this little tidbit from the help pages:

The workspace environment must be set before using several of the list functions, including ListDatasets, ListFeatureClasses, ListFiles, ListRasters, ListTables, and ListWorkspaces.

Your variables mentioned in lines will only return the path to each of the respective databases.  Try it with a print() statement,

Given the limitation of the list functions, you want to work things a little differently in order to get what you want.  Something like this might work for you: (completely untested: run it on some backup data first!)

import arcpy

featureGDB = r"D:\APRX_MXDS\USA_Parcels_2019_Project\Florida_Parcels_Per_County_Final.gdb"
tableGDB = r"D:\APRX_MXDS\USA_Parcels_2019_Project\Florida_Property_Data_Per_County.gdb"

arcpy.env.workspace = featureGDB
featureWS = featureGDB
featureList = arcpy.ListFeatureClasses()

arcpy.env.workspace = tableGDB
tableList = arcpy.ListTables()
tableWS= tableGDB



for table in tableList:
    tableNum = table [3:-7]
    for feature in featureList:
        if feature == f'T{tableNum}':
            inData = f'{featureWS}\\{feature}'
            inField = 'the join field name in the feature class'
            joinTable = f'{tableWS}\\{table}'
            joinField = 'the join field name in the table'
            arcpy.JoinField(inData, inField, joinTable, joinField)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

This makes a couple of assumptions: the table names are just who you showed them and the feature class names are just how you showed them.  It also assumes you want to join the table data into the appropriate feature class.  According to to Join Field help doc this will be a permanent join to the feature class.

Hope this helps-

[NataliaGutierrez1]

Thank you Joe for your help!

I am internally fixing something I found before I am able to run the script.

I have two questions in regards to the script:

1. Line 16: You created a variable called tableNum = table [3:7]. Arcpy recognizes that everytime you write table that is in fact calling a table in GIS? How does python know that we are in fact calling a table in GIS?

2. what do the \\ do to the f' strings? 

[JoeBorgione]

Line 16: You created a variable called tableNum = table [3:7]. Arcpy recognizes that everytime you write table that is in fact calling a table in GIS? How does python know that we are in fact calling a table in GIS?  With each pass through the tableList, I get a value for table and then I strip out the number that appears in that table name. I use that number to identify the feature class to join to.  That's why the assumption that all you table names follow the same convention as well as the feature classes. If you have any of either that do not follow the convention in your original post, we have another problem to solve.

what do the \\ do to the f' strings?   It may be overkill, but with I'm escaping the second backslash with the first backslash. A backslash is a special character in python and I want a literal backslash as part of the path. I just checked and it'll work with or without:

path = r'C:\temp'

file = 'xyz'

newPath = f'{path}\{file}'

newPath
Out[9]: 'C:\\temp\\xyz'

newPath = f'{path}\\{file}'

newPath
Out[11]: 'C:\\temp\\xyz'‍‍‍‍‍‍‍‍‍‍‍‍‍

It acts like the os.path.join() method.  

Make sense?

[NataliaGutierrez1]

Line # 18 is saying: 

if the name of the feature class is the same as the name of the table: T{tableNum} then, join them.

But as you can see above the name of the feature class is T11, T12, T13 etc...

and the name on the tables start with NAL and then have the number. Whenever the number matches, then the join must happen.

I am not sure if this is what the code says. 

[JoeBorgione]

Lines 18 and 19 work together.  Again, I've made the assumption that your featureList looks like:

['T11', 'T12', 'T13'] #and so on...‍

The first for loop at line 16 gets us the table name in the form of  "NAL12F201901".  But that's we have; just the name.  Since you have your tables and feature classes in different workspaces, we have to accommodate for that fact.  In line 17 we get just the number part of the table name. At line 18 we scan the featureList for an element which is T along with the number from the tableName. Line 19 says:

if feature == f'T{tableNum}':‍

in f strings, you enclose the variable name, and hence it's current value with the curly braces. If line 16 gives us 'NAL12F201901' as the value of variable table, the value of variable tableNum is 12.  Consequently, the feature we are going to work with is T12.

May I suggest you create a couple of lists, one of table names and one of feature classes.  Try my logic but don't worry about the arcpy stuff.  Just use straight.  I like to do this sort of thing in the Spyder ide because you can highlight the given text in the script window and with F9, execute it in the console window.  Use print() statements along the way to expose your variable names....

[NataliaGutierrez1]

Perfect Joe, I understand and I will try it as you suggest with the print statements to better see what I am doing.

Before I do that I have one last question though .. in line 17 we are using slicing right: 

tableNum = table [3:7] 

would 3 to 7 give us: 11F2 ?

wouldn't it be [3:5] ? 

Sorry if I am being stubborn, I really want to understand it. 

[JoeBorgione · Accepted solution]

oops... should be :

tableNum = table [3:-7] ‍‍

good catch. Your approach works just as well.

Take a look at Python Strings  |  Python Education  |  Google Developers  and the 'Hello' example of string slices....

[NataliaGutierrez1]

Great thanks!! I will run the code in a little bit. Will let you know how that goes

[JoeBorgione]

The slice thing is just how my brain works: I slice off the first few characters and then work my way back from the end to slice off the excess. That's what's cool about python; several means to the same end...