add join and unusable header names in arcpy

1752
10
01-02-2018 04:17 PM
PhilBeilin1
Occasional Contributor

I am writing a script that uses the Add Join function. This works fine but the result changes the header names to stuff I cannot use like Parcel_D_1, Parcel_D_2, Parcel_D_3. 

It is important that I have usable header names for the next step of my script.

Has anyone else had this problem and know a way around it?

0 Kudos
10 Replies
RandyBurton
MVP Regular Contributor

When you join a feature to another feature or table, the header names will be concatenations of feature/table's name, a dot, and the field's name.  I suspect the naming issue you are experiencing is with the aliases of the fields.  You may wish to experiment with the following code in ArcMap's python window to see the joined field names after you run the AddJoin tool.

mxd = arcpy.mapping.MapDocument("CURRENT")
for f in arcpy.mapping.ListLayers(mxd):
    field_info = arcpy.Describe(f).fieldInfo
    for i in xrange(0,field_info.count):
        print field_info.getfieldname(i)

You should see something like this:

Feature1.OBJECTID
Feature1.fldname1
Feature1.fldname2
Table1.OBJECTID
Table1.fldname1
Table1.fldname2‍‍‍‍‍‍
PhilBeilin1
Occasional Contributor

That script worked and I was given field names similar to the ones you provided in the output but when I try to use these later I am not getting any results. 

I am trying to use Parcel_8.P_OWNER_NM and Parcel_6.P_OWNER_NM in the following code:

arcpy.SelectLayerByAttribute_management("Parcel_8_Layer2", "ADD_TO_SELECTION", "'Parcel_8.P_OWNER_NM' = 'Parcel_6.P_OWNER_NM'")

When I do it manually using what's seen here:

I get the results I need.

If I can't get this to work I'll try the method Curtis suggested.

0 Kudos
RandyBurton
MVP Regular Contributor

When you get the results you want with the tool, I would suggest looking at Geoprocessing > Results.  You can copy the successful run of the tool as a python snippet by right clicking on the last result.  Paste this into your editor and examine the code.   This will show you what the tool is actually using for the first parameter.

RebeccaStrauch__GISP
MVP Esteemed Contributor

Although it may just be a in your post for demo sake, but in the command line you are selection for them to be = bug in the dialog you have <>         And you have "add to selection" vs "new selection".    Just want to make sure the different results weren't just because of this.

RandyBurton
MVP Regular Contributor

I notice you have single quotes around the field names in your where clause.  I think this is causing the tool to select all features because you are comparing two strings that are not equal.  Try:

arcpy.SelectLayerByAttribute_management("Parcel_8_Layer2", "ADD_TO_SELECTION", "Parcel_8.P_OWNER_NM = Parcel_6.P_OWNER_NM")

# no single quotes around fields in where_clause

And as Rebecca Strauch, GISP‌ suggested, you may wish to use "NEW_SELECTION".

0 Kudos
DanPatterson_Retired
MVP Esteemed Contributor

Add Join .... set the Qualified field names in the Environments ... if you want a permanent join then try Join Field 

In any event... you need to set the  Qualified field names  

curtvprice
MVP Esteemed Contributor

If you are working with shapefiles, turning QualifiedFieldNames to False will help, but not if your field names are longer than 8 characters. (filename + _1 = 10) The ten-char field name is a hard .dbf format limit. The only way to create short field names that you can work may be to work with the FieldMappings object. Better yet, keep QualifiedFieldNames False and have your temporary dataset written to a scratch geodatabase (arcpy.env.scratchGDB for example), this will avoid the 10 character limit.  Geoprocessing considerations for shapefile output—Appendices | ArcGIS Desktop 

PhilBeilin1
Occasional Contributor

I've gotten this to work in the python command window but cannot get it to run as a stand along script.

I keep getting problems on my AddJoin (line 53). 

As you can see in lines 29-32 and 36-38 I have tried several methods of avoiding this error and just cannot seem to figure out what is going wrong!

0 Kudos
DanPatterson_Retired
MVP Esteemed Contributor

you do have some file path issues with mixed forward and backslashes... you should fix those and I see you are using shapefiles but I would expect filenames to explicitly put the extension on... try a geodatabase instead if you can get the shapefile creation, working