English

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

Summary

Instructions provided describe how to bring Excel spreadsheet (.xls) files into ArcMap.
To join .xls files to a feature class using the JOIN function in ArcMap, the .xls file must meet certain standards:

  • The fields being used in the join from the spreadsheet and the feature class must be of the same type, such as Double, Text, Short, and Integer.
  • The name of the .xls file as well as the name of the worksheet must not have spaces or special characters other than underscores, and must not begin with a number.
  • Row 1 in the 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 or special characters other than underscores, and none of the fields can begin with a number.

Procedure

The following is a list of possible formatting problems and solutions to help ensure that the Excel spreadsheet joins correctly with a feature class.

  • The fields being used in the join from the spreadsheet and the feature class must be of the same data type, such as Double, Text, Short, or Integer.

    To check the data type of field, right-click the feature class in ArcMap's Table of Contents and select Properties > Fields > the field type for each of the fields in the attribute table to be listed.

    Do the same to check the Excel spreadsheet fields.

    Use the following method if one of the Numeric fields, such as Double, Integer, Float, in the Excel Spreadsheet needs to be converted to text. The TEXT function in Excel converts a value to text in a specific number format. For this example, assume that cells A2:A100 have numbers. To convert them to text, do the following:
    1. Insert a temporary blank column B.
    2. In cell B2, type '=TEXT(A2,"0")' without single quotes.
    3. Using the plus sign that appears in the right-hand side of the B2 cell, drag the formula to fill B3:B100.
    4. Highlight cells B2:B100.
    5. Use Ctrl+C to copy and then navigate to Edit > Paste Special > Values 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.
    An example of a good file name is:
    Spreadsheet_Join.xls
    Examples of names that can cause joined fields to return as null include:
    Spreadsheet Join.xls
    SpreadSheet/join.xls
    1SpreadsheetJoin.xls
  • Row 1 in the spreadsheet is read as field names in ArcMap. In order 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 none of the fields can begin with a number.
    Examples of good field names would be:
    ObjectID
    Location
    X_Long
    Y_Lat
    Test2
    Examples of names that can cause joined fields to return as null include:
    X Long
    Y Lat
    2ndField
    Roads@Cross">Roads@Cross
    House Numbers