I've been having this problem lately, where the join field tool won't work in Pro 2.6 or 2.7, or Python 3.7.9. I'll try to join on two fields that I know are identical, and it will say "WARNING 003237: Join had no matches." Is anyone else having this trouble? When I run the same script in Python 2.7, the fields join. This happens whether I'm trying to join a csv to a feature class, fc to fc, or a gdb table to fc.
What does the Validate Join (Data Management)—ArcGIS Pro | Documentation
tool show?
What are the contents of the fields?
pure text or numeric as text?
any potential leading/trailing spaces in one of the table fields?
left vs right justified "numbers"? (first is text, second is probably a number)
Are you trying to join to a .csv or other excel table?
This happens whether I'm trying to join a csv to a feature class, fc to fc, or a gdb table to fc. Whatever I'm trying to join the table to, it doesn't work. There are a couple of null values in the key field, but that shouldn't matter, should it?
It does matter. The only way to limit the problems are to use TableToTable or ExcelToTable to bring tabular data into Pro first prior to trying a join. Nulls or any variant of missing data can convert numeric fields to text. Tabular data also may contain leading trailing spaces which need to be properly parsed. A trailing space won't be visible to you but will invalidate a join. If you know there are nulls in the key field, you need to exclude those records at source. There are other variants/issues that I haven't covered here, but in short... if the data are not pristine in the source or target tables, then complete or partial failed joins are the result.
I do use table to table in my script to convert the csv into a geodatabase table. This is the input for the join fields tool. It still gets thrown by the nulls. It’s not a problem when I run the script in Python 2. What do you recommend as a solution? There is always the possibility of nulls with the amount of data I’m converting.
convert your nulls to an acceptable value at source. I do this when I work with numpy and featureclasses. For text it is simple just query for nulls and replace with text like "NONE", for integers, you can use some number like -999 etc
Try this on one of your tables to see what you get
def _make_nulls_(in_fc, int_null=-999):
"""Return null values for a list of fields objects, excluding objectid
and geometry related fields. Throw in whatever else you want.
Parameters
----------
in_flds : list of arcpy field objects
Use arcpy.ListFields to get a list of featureclass fields.
int_null : integer
A default to use for integer nulls since there is no ``nan`` equivalent
Other options include
>>> np.iinfo(np.int32).min # -2147483648
>>> np.iinfo(np.int16).min # -32768
>>> np.iinfo(np.int8).min # -128
>>> [i for i in cur.__iter__()]
>>> [[j if j else -999 for j in i] for i in cur.__iter__() ]
"""
nulls = {'Double': np.nan, 'Single': np.nan, 'Float': np.nan,
'Short': int_null, 'SmallInteger': int_null, 'Long': int_null,
'Integer': int_null, 'String':str(None), 'Text':str(None),
'Date': np.datetime64('NaT')}
#
desc = arcpy.da.Describe(in_fc)
if desc['dataType'] != 'FeatureClass':
print("Only Featureclasses are supported")
return None, None
in_flds = desc['fields']
shp = desc['shapeFieldName']
good = [f for f in in_flds if f.editable and f.name != shp]
fld_dict = {f.name: f.type for f in good}
fld_names = list(fld_dict.keys())
null_dict = {f: nulls[fld_dict[f]] for f in fld_names}
# ---- insert the OBJECTID field
return null_dict, fld_names
Thanks. I'll give this a try. It is a text field, but the key is seven digit numbers.