Join an Excel spreadsheet (.xls) to a feature class in ArcMap

Summary

ArcMap provides the capability to join an Excel spreadsheet to a feature class with either the Join Data dialog box or the Add Join tool. For best performance, use the Join geoprocessing tools when working with large datasets.

Procedure

The instructions provided describe how to join an Excel spreadsheet (.xls) file to a feature class in ArcMap using the Join Data dialog box and the Add Join tool.

Join Data dialog box

The Join Data dialog box is accessed by right-clicking a layer in ArcMap and selecting the Join option. Refer to the following document for steps to join an Excel spreadsheet to a feature class using the Join Data dialog box: Joining attributes in one table to another.

Add Join tool

The Add Join tool joins a layer to another layer or table based on a common field. Refer to the following document for more information on the Add Join tool: Add Join.

1. In ArcToolbox, navigate to Data Management Tools > Joins > Add Join.
2. In the Add Join dialog box, select the layer to join in the Layer Name or Table View.
3. Select the Input Join Field in the drop-down menu.
4. Select or browse for the Join Table.
5. Select the Output Join Field from the drop-down menu.
6. Optionally, check the checkbox for Keep All Target Features (optional).

7. Click OK.

Note:
Joining an Excel spreadsheet to a feature class using the Join Data dialog box and Add Join tool outputs a temporary join. To make the join permanent, export the joined feature class to a new feature class or save the joined table view to a new table. Alternatively, use the Join Field tool to output a permanent join.

Prior to joining an Excel spreadsheet to a feature class, the Excel spreadsheet must be formatted and meet certain standards. Refer to the following document for more information on formatting a Microsoft Excel table for use in ArcGIS: Formatting a table in Microsoft Excel for use in ArcGIS.

The following is a list of a few Excel spreadsheet formatting guidelines and solutions to ensure that the Excel spreadsheet joins correctly with a feature class:

• The fields used in the join from the Excel spreadsheet and the feature class must be of the same data type, such as text, date, or number.
  
  To check the data type of a field, right-click the feature class in the Table Of Contents and select Properties > Fields > the data type for each field is listed in the Field Details section. Use the same method to check the data type of the Excel spreadsheet fields.
  
  Use the following method to convert one of the numeric fields, such as double, integer, or float, in the Excel spreadsheet to text. The TEXT function in Excel converts a value to text in a specific number format. In this example, assume that cells A2:A100 contain numbers. To convert the numeric field to text, use the following workflow:

1. Insert a temporary blank column B.
2. In cell B2, type =TEXT(A2,"0").
3. Select cell B2 and copy the formula in the cell by using the fill handle to fill B3:B100.
4. Highlight cells B2:B100.
5. Press Ctrl+C on the keyboard to copy and navigate to Home > Paste > Paste Special > select the Values option and click OK. The entries in column B are now text versions of the numbers in column A.
6. Right-click in column A and select Paste.
7. Delete the temporary column B.

• The name of the .xls file and the name of the worksheet must not have spaces or special characters other than underscores, and must not begin with a number.
  The following is an example of a good file name:
  • Spreadsheet_Join.xls
• The following are examples of file and worksheet names that cause joined fields to return as null:
  • Spreadsheet Join.xls
  • SpreadSheet/join.xls
  • 1SpreadsheetJoin.xls
• Row 1 in an Excel spreadsheet is read as field names in ArcMap. To successfully join the spreadsheet to a feature class in ArcMap, the first row of the spreadsheet must not contain spaces, special characters other than underscores, and must not begin with a number.
• The following are examples of good field names:
  • ObjectID
  • Location
  • X_Long
  • Y_Lat
  • Test2
• The following are examples of field names that cause joined fields to return as null:
  • X Long
  • Y Lat
  • 2ndField
  • Roads@Cross">Roads@Cross
  • House Numbers