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

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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!

<SPAN class="keyword token">import</SPAN> arcpy

in_features <SPAN class="operator token">=</SPAN> r<SPAN class="string token">"D:\APRX_MXDS\USA_Parcels_2019_Project\Florida_Parcels_Per_County_Final.gdb"</SPAN>
join_tables <SPAN class="operator token">=</SPAN> r<SPAN class="string token">"D:\APRX_MXDS\USA_Parcels_2019_Project\Florida_Property_Data_Per_County.gdb"</SPAN>

list_infeatures <SPAN class="operator token">=</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>ListFeatureClasses<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>
list_join_tables <SPAN class="operator token">=</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>ListTables<SPAN class="punctuation token">(</SPAN><SPAN class="operator token">*</SPAN>NAL<SPAN class="punctuation token">)</SPAN>
UniqueField <SPAN class="operator token">=</SPAN> <SPAN class="string token">"CO_PARCELID"</SPAN>

<SPAN class="keyword token">for</SPAN> fc <SPAN class="keyword token">in</SPAN> list_infeatures<SPAN class="punctuation token">:</SPAN>
    arcpy<SPAN class="punctuation token">.</SPAN>AddJoin_management<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

Encontre mais publicações marcadas com

Accepted answers

JoeBorgione

oops... should be :

tableNum <SPAN class="operator token">=</SPAN> table <SPAN class="punctuation token">[</SPAN><SPAN class="number token">3</SPAN><SPAN class="punctuation token">:</SPAN><SPAN class="operator token">-</SPAN><SPAN class="number token">7</SPAN><SPAN class="punctuation token">]</SPAN> ‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN>

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

All comments

NataliaGutierrez1

Thank you again! Will keep on learning and using the online tools

JoeBorgione

I'm glad it worked. There is an instructor lead esri course; I took it it some time back. It was okay. I've mostly just picked it up on my own with a lot of help from this forum. Using ArcGIS Pro you can run a tool, get the syntax figured out and then create a stand alone script based on that.

There are lots of on-line python tutorials. I like the w3schools stuff as they spell things out pretty succinctly. Python documentation is all over the web, so if just preface a question with 'python' you can usually find what you are looking for like 'python return last 7 letters in a string' or 'python for loop' or 'python if else'.....

NataliaGutierrez1

Joe, the code worked!! I am so happy! thank you so much! I was really stuck and needed to do this automatically. I used to do all of this manually for such a long time and I am learning to automate this with your help and with as many videos I can find online.

Actually, do you know of a good course that would teach me python applied to arcgis ? All I find are separate videos each one teaching different things. I would love to find something that goes from the very beginning until more advanced processes.

Anyways.. thank you again!

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

NataliaGutierrez1

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

JoeBorgione

oops... should be :

tableNum <SPAN class="operator token">=</SPAN> table <SPAN class="punctuation token">[</SPAN><SPAN class="number token">3</SPAN><SPAN class="punctuation token">:</SPAN><SPAN class="operator token">-</SPAN><SPAN class="number token">7</SPAN><SPAN class="punctuation token">]</SPAN> ‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN>

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

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 <SPAN class="" style="color: #a67f59; background: rgba(255, 255, 255, 0.5); border: 0px; font-weight: inherit;">=</SPAN> table <SPAN class="" style="color: #999999; border: 0px; font-weight: inherit;">[</SPAN><SPAN class="" style="color: #990000; border: 0px; font-weight: inherit;">3</SPAN><SPAN class="" style="color: #999999; border: 0px; font-weight: inherit;">:</SPAN><SPAN class="" style="color: #990000; border: 0px; font-weight: inherit;">7</SPAN><SPAN class="" style="color: #999999; border: 0px; font-weight: inherit;">] 
</SPAN>

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

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

<SPAN class="punctuation token">[</SPAN><SPAN class="string token">'T11'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'T12'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'T13'</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="comment token">#and so on...</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN>

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:

<SPAN class="keyword token">if</SPAN> feature <SPAN class="operator token">==</SPAN> f<SPAN class="string token">'T{tableNum}'</SPAN><SPAN class="punctuation token">:</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN>

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

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 <SPAN class="operator token">=</SPAN> r<SPAN class="string token">'C:\temp'</SPAN>

file <SPAN class="operator token">=</SPAN> <SPAN class="string token">'xyz'</SPAN>

newPath <SPAN class="operator token">=</SPAN> f<SPAN class="string token">'{path}\{file}'</SPAN>

newPath
Out<SPAN class="punctuation token">[</SPAN><SPAN class="number token">9</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">:</SPAN> <SPAN class="string token">'C:\\temp\\xyz'</SPAN>

newPath <SPAN class="operator token">=</SPAN> f<SPAN class="string token">'{path}\\{file}'</SPAN>

newPath
Out<SPAN class="punctuation token">[</SPAN><SPAN class="number token">11</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">:</SPAN> <SPAN class="string token">'C:\\temp\\xyz'</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

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.

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

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

<SPAN class="keyword token">import</SPAN> arcpy

featureGDB <SPAN class="operator token">=</SPAN> r<SPAN class="string token">"D:\APRX_MXDS\USA_Parcels_2019_Project\Florida_Parcels_Per_County_Final.gdb"</SPAN>
tableGDB <SPAN class="operator token">=</SPAN> r<SPAN class="string token">"D:\APRX_MXDS\USA_Parcels_2019_Project\Florida_Property_Data_Per_County.gdb"</SPAN>

arcpy<SPAN class="punctuation token">.</SPAN>env<SPAN class="punctuation token">.</SPAN>workspace <SPAN class="operator token">=</SPAN> featureGDB
featureWS <SPAN class="operator token">=</SPAN> featureGDB
featureList <SPAN class="operator token">=</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>ListFeatureClasses<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>

arcpy<SPAN class="punctuation token">.</SPAN>env<SPAN class="punctuation token">.</SPAN>workspace <SPAN class="operator token">=</SPAN> tableGDB
tableList <SPAN class="operator token">=</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>ListTables<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>
tableWS<SPAN class="operator token">=</SPAN> tableGDB



<SPAN class="keyword token">for</SPAN> table <SPAN class="keyword token">in</SPAN> tableList<SPAN class="punctuation token">:</SPAN>
    tableNum <SPAN class="operator token">=</SPAN> table <SPAN class="punctuation token">[</SPAN><SPAN class="number token">3</SPAN><SPAN class="punctuation token">:</SPAN><SPAN class="operator token">-</SPAN><SPAN class="number token">7</SPAN><SPAN class="punctuation token">]</SPAN>
    <SPAN class="keyword token">for</SPAN> feature <SPAN class="keyword token">in</SPAN> featureList<SPAN class="punctuation token">:</SPAN>
        <SPAN class="keyword token">if</SPAN> feature <SPAN class="operator token">==</SPAN> f<SPAN class="string token">'T{tableNum}'</SPAN><SPAN class="punctuation token">:</SPAN>
            inData <SPAN class="operator token">=</SPAN> f<SPAN class="string token">'{featureWS}\\{feature}'</SPAN>
            inField <SPAN class="operator token">=</SPAN> <SPAN class="string token">'the join field name in the feature class'</SPAN>
            joinTable <SPAN class="operator token">=</SPAN> f<SPAN class="string token">'{tableWS}\\{table}'</SPAN>
            joinField <SPAN class="operator token">=</SPAN> <SPAN class="string token">'the join field name in the table'</SPAN>
            arcpy<SPAN class="punctuation token">.</SPAN>JoinField<SPAN class="punctuation token">(</SPAN>inData<SPAN class="punctuation token">,</SPAN> inField<SPAN class="punctuation token">,</SPAN> joinTable<SPAN class="punctuation token">,</SPAN> joinField<SPAN class="punctuation token">)</SPAN>‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

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-

Participe da Conversa

Membros conectados podem postar, seguir atualizações e mais. Novo aqui? Registre uma conta gratuita.

Links Rápidos

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.