table join when the field is not exactly the same

Question

Hello,

I'm trying to join a feature class and an excel table by a field name is not exactly the same.

Our feature class has points named similar to this "XYZ 123", but the excel labels the field as "BM XYZ 123".

As you see they are very close to one another, but not exact. My department has a great amount of data to do this for that is why I was seeing if there is a script or a way to accomplish this.

Thank you very much!

RebeccaStrauch__GISP · Answer

...and my 2 cents...

I agree with not joining to an Excel and creating a FGDB table (like Dan_Patterson , my preference) or Access.  As Ted Kowal  mentioned, you need to figure out your rules first.  If the feature class only have the numbers, and the Excel may or may not, I would add a temp field and calc that to equal the to the featureclass format and then match on that.   Whether you populate this new field in Excel or the FGDB/Access table may depend on where you are more comfortable doing calculations, but you will want the field format to be the same once ready to join.  One you get what you need, you can always delete this temp field (assuming you don't need a join anymore.

If you need to make the join permanent, with all or a selected set of the fields, try using

Join Field—Help | ArcGIS Desktop

Joining attributes in one table to another—Help | ArcGIS Desktop

the same rules of getting you join field to match will apply.

TedKowal · Answer

My 2 cents,

There is no Good way of doing this.... because you have to attack the data from both ends.  I point you into a direction that I use in situations like this.

* What is the ultimate repository rule for you data.  Those fields that don't match the rules are incorrect and must changed.  The data rule for your feature class states that the "BM" is not required.  Your excel sheet has it in it.  Which data rule/format is correct?
* Once your rules have established, then you must correct the data to match those rules. (schema)* I personally import the excel data sheet into MS Access and  create manual pattern matching queries to update the bad data (data that did not meet the rules set above).
* SQL LIKE Operator   is a good starting resource on Pattern matching

* I then do my joins from Access to the feature class - but you can export the data back into an excel spreadsheet if your more comfortable that way

I do not know of any magical way of joining two fields that do not match in structure and format other than correcting the data.

DanPatterson_Retired · Answer

The field name is not relevant, it the contents within the field and its type. So if you are joining a text field to a text field, you need to remember that case is important and even though a number looks like a number, it may be text.

Also... avoid joining an excel file or even using an excel file if possible.  It take very little time to convert an excel file to a table using the Excel tools in Arctoolbox  (ie Excel to Table tool)