Completely stumped as to why my Join isn't working

04-29-2021 07:37 AM
New Contributor II


1) Grab Assessor Data from FTP folder
2) Push Assessor Data to SDE database
3) Join Assessor Data to sde.SDE.Parcel
4) FC to FC so ParsJ overwrites from Parcel layer


import arcpy

TableOutLocation = r"\\files03\it_2021\all_gisusers\Connection_Files\GISDB06_SDE.sde"

# ##### Stopping connections to FC ######

# Block new connections to the database.
print('The database is no longer accepting connections.format{}'.format(TableOutLocation))
arcpy.AcceptConnections(TableOutLocation, False)
# Disconnect all users from the database.
print("Disconnecting all users")
arcpy.DisconnectUser(TableOutLocation, "ALL")

# Environment variables
arcpy.env.workspace = r"\\files03\it_2021\all_gisusers\Connection_Files\GISDB06_SDE.sde"
arcpy.env.overwriteOutput = True

# Variables for Table to Table conversion
CamaTable = r"\\ftp001\Assessor\Assessor_Extract.csv"

# Converting Cama-Excel to Cama-Table in GDB
arcpy.TableToTable_conversion(CamaTable, TableOutLocation, "CamaTable")

# Variables for Add Join Tool
ParcelLayer = r"\\files03\it_2021\all_gisusers\Connection_Files\GISDB06_SDE.sde\sed.SDE.ParcelFeatures\sde.SDE.Parcel"
ParcelLayerJoinField = "MAPNUMBER"
CamaTableToJoin = r"\\files03\it_2021\all_gisusers\Connection_Files\GISDB06_SDE.sde\CamaTable"
CamaTableJoinField = "ParcelNumber"

# Join the feature layer to a table
arcpy.JoinField_management(ParcelLayer, ParcelLayerJoinField, CamaTableToJoin, CamaTableJoinField)

# FC to FC variables
ParsJOutLocation = r"\\files03\it_2021\all_gisusers\Connection_Files\GISDB06_SDE.sde"

# Converting Parcel Layer to ParsJ Layer
arcpy.FeatureClassToFeatureClass_conversion(ParcelLayer, ParsJOutLocation, "ParsJ")


I want the 'CamaTable' to join to the parcel layer and where 'MAPNUMBER' does NOT match 'ParcelNumber', I want it appended to the table. Am I using the right geoprocessing tool? I thought a full outer join would add/append the records that do not match the primary and secondary keys in the join. 

0 Kudos
3 Replies
MVP Esteemed Contributor

I think you want to use 'add join'

That should just about do it....
0 Kudos
MVP Esteemed Contributor

To do a full outer join you need to use the Make Query Table or Make Query Layer tool. (Many to Many does not work with Add Join and Join Field, only 1-1 or M-1.)

MVP Regular Contributor

An outer join will smash up the whole table from both sides; matched and unmatched all together. You can do the full join, then query out where your join fields are null.


"{} is null or {} is null".format(ParcelLayerJoinField, CamaTableJoinField)