By Aileen Buckley, Esri Cartographer
To use an Excel table in ArcGIS, it should first be formatted correctly so that ArcGIS can read the table without errors. This blog entry contains links to a table with guidelines for how to format your Excel table, as well as a couple of other useful related resources.
If you don’t format the Excel table correctly, you might run into problems when using tables from Excel when you:
- Add an Excel table to ArcMap as data
- Convert an excel table to a geodatabase table using the Excel to Table geoprocessing tool.
- Create a Join or Relate with an Excel table or a table converted from Excel.
The first two of these three cases were tested and are reported on here.
The guidelines include references to special characters you cannot use when naming fields in ArcGIS and reserved words you cannot use when naming fields in Excel.
The guidelines are for the use of geodatabases and feature classes, not shapefiles. When using shapefiles, additional dbase-based restrictions will apply, such as limiting field names to 10 characters.
Note that the Excel reserved words were extracted from the driver via ArcObjects. We have a function that returns the Keywords for a data source. In the case of Excel, it gets the list from the driver itself.
Excel Tables and ArcGIS Tables
This is an overview of the relationship between Excel tables and ArcGIS tables:
- A column in an Excel table is a field in ArcGIS. All rows in a table have the same columns. In a spatial table, columns store categories of information about the features, such as names, ID numbers, and area or length.
- A row in an Excel table is a record in ArcGIS. In a spatial table, each row represents one feature.
- A cell in an Excel table is a value in ArcGIS.
These are a few things to note about using Excel tables in ArcGIS:
- ArcGIS supports both Excel 2003 and earlier .xls files and Excel 2007 .xlsx files. One advantage of Excel 2007 is that it allows much larger worksheets (1,048,576 rows by 16,384 columns) than you can have in Excel 2003 (65,536 rows by 256 columns.)
- If you have an .xlsx file you want to use in ArcGIS but do not have Excel 2007 installed, you will need to install the 2007 Office System Driver. It can be downloaded from the Microsoft Download Center. If you have Microsoft Excel 2010 or no version of Microsoft Excel installed, you must install the 2007 driver before you can use either .xls or .xlsx files.
- Excel tables are read-only in ArcGIS as well as in Excel when you have a workbook open in ArcGIS.
- Excel workbook and worksheet names should not contain spaces.
- Field names are derived from the first row in each column of the worksheet.
- You can view the properties of fields, set aliases for the field names, and set field visibility on the Fields tab of the table’s Properties dialog box.
To learn more:
- Online Help: Understanding how to use Microsoft Excel files in ArcGIS
- Online Help: Formatting a table in Microsoft Excel for use in ArcGIS
- Online Help: Excel to Table Tool
- Defining fields in tables
- What are the reserved words for Esri’s file geodatabase? http://support.esri.com/en/knowledgebase/techarticles/detail/37763
Thanks to Veronica Rojas for helping me to test the various issues. Thanks to Lance Shipman and Colin Zwicker, Projects Engineers on the ArcMap Team, who helped in the review of this article. Any errors, however, are solely the responsibility of the author.