Formatting Excel Data for Use in ArcGIS Desktop

Many ArcGIS users store nonspatial attribute data associated with GIS features in Microsoft Excel worksheets. Starting at the ArcGIS 9.2 release, it became possible to directly access Excel worksheets in ArcMap and ArcCatalog.

As a best practice, before using an Excel worksheet in ArcGIS, you should verify that its formatting will not cause any issues, particularly if the worksheet will be joined to another table or used in a geoprocessing operation. There are some common things to check. Depending on your organization’s data requirements, if you have an Excel worksheet that’s not formatted properly as described below, it may be a good idea to make a copy of it, then reformat the copy.

Column Names

  • The first row in an Excel worksheet should contain column names, as ArcGIS converts the first row of data to field names. If the first row contains other content, you should modify the worksheet so that the row that contains the column names becomes the first row.
  • Column names must begin with a letter (no numbers or underscores).
  • Column names must contain only letters, numbers, or underscores (no spaces, hyphens, or special characters).
  • Column names must be 64 characters or less.

Excel worksheet column names

Column Data Types

  • ArcGIS scans the first eight rows in each column to determine the column data type. If there are mixed types within a column, ArcGIS converts the data to text. Solution: In Excel, use the Format Cells dialog box to apply the same data type to all cells in each column.
  • ArcGIS reads only the first 255 characters in a cell. If a cell contains more than 255 characters, ArcGIS converts the column to the BLOB (Binary Large OBject) data type, and you will not be able to read that data in ArcGIS. Solution: Check your worksheet to look for cells with lots of data and truncate the data if necessary.
  • ArcGIS converts numeric data to the Double data type. If this is not desirable for your data, after joining an Excel table to another table in ArcMap, you can add a new field of the desired type to the joined table (e.g., a short integer field), then calculate its values to be the data from the source Excel column.

To preview an Excel worksheet in ArcCatalog:

  • In the Catalog tree, browse to the folder containing the Excel file.
  • Click the plus sign next to the Excel filename to expand it and display one or more worksheets. Note that ArcGIS appends a $ to the end of worksheet names.

Selected worksheet in Catalog tree

  • Click the worksheet you want to preview to select it, then click the Preview tab in the pane on the right.

Click to enlarge

To work with an Excel worksheet in ArcMap:

  • Open a map document, then click the Add Data button.
  • In the Add Data dialog box, navigate to the Excel file.
  • Double-click the file, click the desired worksheet name, then click Add.
    • Tip: You can also drag the worksheet into the map document from ArcCatalog.
  • The worksheet displays in the Table of Contents Source tab.

Worksheet in ArcMap Table of Contents

After adding a worksheet to a map document, you can join it to a layer attribute table, then use the data as a source for feature labels, symbology, graphs, and reports and to calculate field values. As noted above, Excel worksheets can also be used as inputs to geoprocessing tools.

Using Excel 2007 Files

ArcGIS supports .xls files (Excel 2003 and earlier) as well as .xlsx files (Excel 2007). If you want to use an Excel 2007 file in ArcGIS but do not have Excel 2007 software installed, download the 2007 Office System Driver from the Microsoft Download Center and install it on your machine.

Kevin Mumford, an instructor with Esri Training Services, contributed this post.

SuzanneB

About SuzanneB

Suzanne is a Maryland native with a degree in English Literature who enjoys writing about Esri technology and other topics. She works with Esri Training Services in Redlands, California.
This entry was posted in ArcGIS Step by Step and tagged , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

2 Comments

  1. cmoritz says:

    can you tell me why my 87000 row spreadsheet is only coming into ArcMap 9.3.1 with the first 2048 rows?

  2. cmoritz says:

    sorry! figured it out