ArcGIS Pro - Join excel table on text field

[roemhildtg]

I have two tables, one is a feature class in a file geodatabase, the other is a sheet in an excel spreadsheet (2003-2007) version (.xls extension)

When I join the table in ArcMap, the join is successful:

When I join the same two tables in ArcGIS Pro, there are no joined results.

Anyone run into issues like this?

[DanPatterson_Retired]

matching records may not appear due to join validation errors

seems to be the key line in the first dialog, Why not change the field names in the excel sheet so they aren't reserved words.

[roemhildtg]

Thanks for the reply Dan, I gave it a shot and replaced the field names with new ones that don't have reserved words. ArcMap now shows no errors in the join.

But it looks like the join still has no results in ArcGIS Pro. If you have any other suggestions, I'd love to give them a shot.

[DanPatterson_Retired]

grief... I hope it isn't one of those cases that joining text to text fails because there is a leading or trailing space in some of the values in the field...

I think it is time to find a few that you think should match... but don't, if you can isolate those cases, then perhaps some non-guessing answers can be dredged up from the litany of case exceptions when comparing strings with strings .... and there are many

[roemhildtg]

Hmm, its either I'm missing something obvious or a problem with ArcGIS Pro. In theory I should have 3020 matching records, ArcMap successfully joins that many.

ArcGIS Pro on the other hand finds 0 matches.

[DanPatterson_Retired]

Well, I don't like spreadsheets for joins, and I don't use excel unless forced to do so, but this help topic might reveal some 'obvious's that we have missed

Work with Microsoft Excel files in ArcGIS Pro—Excel | ArcGIS for Desktop

[roemhildtg]

Well, I contacted support on the issue, and their solution was to convert the spreadsheet to a .dbf file first. Their explanation for the reason it wasn't joining wasn't very clear, there may have been one or multiple issues with the formatting of the spreadsheet.

And while converting it to dbf does work, it doesn't really solve the issue since we're updating this table through Excel and need to continue to do so.

[DanPatterson_Retired]

Unless you format each column explicitly (ie.  don't rely on what it looks like), then the field can contain a mix of text and/or numbers.  That gets solved when you export to a dbase or text file format, because they are required to be uniform in cell contents.  Everything is either text or numbers.  And you should never rely on a blank cell to be or represent null data.  Non-explicit values to represent nulls are pure evil and the cause of many problems over the years.