2

I have an attribute table with one field that needs to be populated, but because there are thousands of records, I would like to know a way that it can be done somewhat automatically. The information that the field needs to be populated with is currently stored in an excel table (this can be changed if necessary). In order to populate the fields correctly there are several other fields that need to match information in the fields within the attribute table. Because there are multiple fields that need to match, I cannot get a join to work.

  • 2
    Why don't you combine the fields that need to match into one field (in the field calculator) and join on that? – whatahitson May 13 '16 at 16:02
3

I would recommend saving your excel spreadsheet to a csv, export the CSV to a dbase in a file geodatabase, add a text field to the dbase and use the field calculator to concatenate the multiple fields into one standardized unique-identifying string. In the field calculator, you can use the Python parser with the expression str(!field1!) +"-"+ str(!field2!) + "-"+ str(!field3!). Do the same in your shapefile/feature class and join on that field.

An alternative to this methodology (still with the csv converted to dbase) would be to make a query layer (http://pro.arcgis.com/en/pro-app/tool-reference/data-management/make-query-table.htm) where the expression would be table1.field1 = table2.field1 AND table1.field2 = table2.field2 etc. You can use copy features or right click the query table/layer (if you add a shape field into the query table, it becomes a query layer with the geometries) and export the data to a feature class/shapefile for use later. The data wouldn't be permanent until you export it.

  • The Query Table won't show records that are unmatched in either table. You cannot edit or use the field calculator on the the Query Table until it is exported. It does not automatically refresh from the sources without rerunning the tool. The standard join on the concatenated field has none of these problems and is the way to go. You can alternatively use a tool I built to create a single field numeric key that represents the multi-field key. geonet.esri.com/blogs/richard_fairhurst/2015/06/07/… – Richard Fairhurst May 13 '16 at 18:06
  • A Python script could do it using a cursor and dictionary. See my Blog on Turbo Charging Data Manipulation with Python Cursors and Dictionaries. In particular look at the example of Creating a Multi-Field Python Dictionary Key to Replace a Concatenated Join Field geonet.esri.com/blogs/richard_fairhurst/2014/11/08/… – Richard Fairhurst May 13 '16 at 20:18
  • It's true, the query table performs an inner join which can leave out unmatched records in both tables; you would have to join the query table result to either table to specifically identify rows that aren't included in the join. I assumed based on the nature of the question and the tags related to the question that Rosemary did not want to use python to solve her problem, though your script tool seems like a great solution for this situation. I've never used arcpy.Parameter for a script tool before; thanks for sharing your script! – crld May 16 '16 at 15:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.