Title: How to Perform a Join in Arcmap

Title: How to perform a join in ArcMap

Purpose: This is a useful tool to append or join to an existing attribute table (from a Shape file) additional data such as from an Excel file. For example, let us say you have a Shape file for the US states and you want to join additional information concerning a recent election that is stored in an Excel file, these are the steps you should take.

Procedure:

Preliminary Step

1. Write Access: This proclib assumes two things. The first is that you have a shapefile you want to add attribute data to; the second is that you have data in Excel you want to add (join) to the shapefile attribute table.

Finding a identical variable for performing the join

2. Open ArcMap and add your vector data (shape file). Right click on the layer name of the added layer, and select Open Attribute Table. Look at the entities and corresponding attributes listed. You should be looking for a common field, which you could identify on BOTH sets of data, such as STATE NAMES in the ArcMap attribute table, and STATE NAMES in the Excel spreadsheet containing data you would like to append to the end of the attribute table.

3. Upon identifying a common field, you may discover that the data names do not exactly match. This is a problem. An example would be the attribute lists the state name as Arizona, while the Excel spreadsheet containing unemployment data lists the state by its abbreviation of AZ or one data set uses all CAPITALS and the other does not (WA versus Wa). To perform a join, these identifiers must match EXACTLY. Thus, you may need to perform preprocessing, to make things exactly the same. -- Make any necessary changes in the spreadsheet to match exactly the values listed in the attribute table in ArcMap, as this is much faster and less cumbersome than editing in ArcMap.

4. Upon making sure you have unique identifiers, which match exactly, then close the attribute table in ArcMap.

Join Procedure

5. Right click the layer (shape file) which you want to append the Excel spreadsheet data to. Scroll down and click Joins and Relates to reveal the menu, and then select Join.

6. Accept the default selection of Join attributes from a table.

7. In “1. Choose the field in this layer…” Select the field (attribute) from the Shape File you want to execute the join based on. (For example Country_name)

8. In “2. Choose the table to join to this layer…” Click on the Yellow folder icon and browse to your Excel spreadsheet. Then choose the correct worksheet within the Excell file that is the worksheet with the data you want.

9. In “3. Choose the field in the table to base the join on” open the pick window and scroll down to the attribute that matches the one chosen in the Shape file under step 7 (for example Country).

10. Depending on whether you want to keep all the entities regardless if they have data in both the Excel spreadsheet and the Shape file you can use the Radio Button to either Keep all records or Keep only matching records. You can try doing this each way to see the difference. Also be aware that if join attribute names are not identical the data won’t join for that entity.

11. Click on OK.

12. Open the Shape file attribute table, scroll to the extreme right and look for the new attribute data. Make sure none of the newly added values are set as Null if the entity appears in both the shape file and Excel file. This would be an indicator that there is an error. If you have null values, confirm that you have IDENTICAL values in your spreadsheet and Shape file. If not exit ArcMap without saving your project, then go to the Excel file and make the necessary names, then start over with this proclib.

13. You may now display your new attribute data on your map.

14. If you save your project at this point the join will remain when you re-open your project. When reopening, take special care that you have access to both files, and their location has not been moved.

15. If you want to make your join permanent, you will need to export your data to a new file name.

Part 2 – saving the Joined materials in a new Shape File: Exporting data after it has already been joined. Joining attributes to an existing shape file does not maintain the joined date in the database file (DBF or whatever). You need to actually export the file to get this change.

1. Join two files, say you have a point file of Chinese Provinces and you want to join an Excell file of percapita income for each province.

2. Once joined right click on the shape file name in the TOC, scroll down to data, click again and find the Export command, and click a third time. Set the destination and name and you’re all done.

P. Buckley
May 2, 2013

For example data on the j: drive go to J:\saldata\Egeo250\Data – join\ and load the Country shape file then the Big Mac Index.xls file and perform the join commands.

Join Command Page 4 of 4