Join Field Not Working

[LeviCecil]

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. 

[DanPatterson]

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)

[LeviCecil]

I got "WARNING 003237: Join had no matches" from validate join. 

[Mary_Murphy]

Are you trying to join to a .csv or other excel table?

[LeviCecil]

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? 

[DanPatterson]

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.

[LeviCecil]

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. 

[DanPatterson]

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

[LeviCecil]

Thanks. I'll give this a try. It is a text field, but the key is seven digit numbers. 

[LeviCecil]

Joining is still a problem in Pro 2.8