Microsoft Excel tables are a highly useful data source for ArcGIS. In addition to adding them to ArcMap, previewing them in ArcCatalog, and using them as inputs to geoprocessing tools, you can use the Excel To Table tool in the Conversion toolbox in ArcToolbox (available at all license levels) to convert an Excel table to a geodatabase attribute table. This article describes the things you should always do and the things you should never do when formatting Excel tables for use with the Excel To Table tool.
Understanding Excel and ArcGIS Tables
Here is a quick review of the relationship between Excel tables and ArcGIS attribute tables. A row in an Excel table is a record in an ArcGIS attribute table. All rows in an Excel table have the same columns. A column in an Excel table is a field in ArcGIS.
Fields in an ArcGIS attribute table can store spatial information (i.e., area, length) and nonspatial data (e.g., address, owner) about features. Each row in an ArcGIS attribute table represents the record for one feature. Field names in an ArcGIS table created from an Excel table are derived from the first row in each column of the Excel table. Excel tables are read-only in ArcGIS. If you have an Excel workbook open when you are in ArcGIS, the Excel table is also read-only.
Which version of Excel you use matters. ArcGIS supports several Excel versions: .xls files (Excel 2003 and earlier) and .xlsx files (Excel 2007). The advantage of using Excel 2007 is that it allows much larger worksheets (1,048,576 rows by 16,384 columns) than Excel 2003 (65,536 rows by 256 columns).
If you want to use an .xlsx file but do not have Excel 2007, you will need to install the 2007 Office System Driver. It can be downloaded from the Microsoft Download Center. If you are using Excel 2010, you will have to install the 2007 Office System Driver whether you are working with .xls or .xlsx files.
THINGS YOU SHOULD ALWAYS DO
You can avoid problems by making sure column names and cell formatting in Excel adhere to these guidelines so they will convert correctly. The best strategy is to make changes in Excel before using the Excel To Table tool to convert the table.
Always create unique column names.
Because ArcGIS requires unique field names, no two column names in an Excel table should be the same. If a table with duplicate column names is converted, the resultant field names will be truncated to 10 characters and a version number added (e.g., two fields called Street_Name become Street_Name and Street_Name1). Always rename duplicate columns before conversion.
Always start column names with a letter.
If you start a column name with something other than a letter, ArcGIS will add the letter F in front of the nonalpha character. For example, 2Name becomes F2Name. Avoid this problem by ensuring all column names begin with a letter. If you converted an Excel table with a leading nonalpha character, in ArcGIS change the field names with the Alter Field geoprocessing tool or right-click the table in ArcCatalog, click Properties, click the Fields tab, and type a new field name that starts with a letter.
Ensure that all columns and rows contain values.
Upon conversion, any empty column will be created as a field with a generic name (e.g., field_1) that contains no values. Any empty row will be created with no values. This takes up storage unnecessarily and requires more display area to view the contents of the table. In Excel, delete all empty columns and rows. If you have already converted a table with empty columns, use the Delete Field geoprocessing tool in the Data Management toolbox or open the attribute table in ArcMap, right-click the field heading for the empty column, and click Delete Field. If you have already converted a table with empty rows, delete empty rows with the Delete Rows geoprocessing tool in the Data Management toolbox or open the attribute table in ArcMap, start an editing session, right-click the table, click Open Attribute Table, highlight the empty rows, right-click the far-left box, and click Delete Selected.
Use basic cell formatting in Excel.
Cells in columns should be formatted as text, number, or date in Excel. The cell formatting in a column is used by ArcGIS to set the field type. If no formatting has been specified in Excel (i.e., the default General is used), the field type in ArcGIS is determined by a scan of the values in the first eight rows for that column. If the scan finds mixed data types in those rows, ArcGIS will assign the field type as Text and any values will be converted to strings. Number field types in Excel are converted to Long Integer field types in ArcGIS. Year and Time field types in Excel are converted to the Date field type in ArcGIS. Avoid problems by setting the field types for the columns to Text, Number, or Date in Excel by right-clicking the column, choosing Format Cells, clicking the Number tab, and setting the field type. If you have already converted an Excel table and you are not sure of the formatting, right-click the table in ArcCatalog, click Properties, click the Fields tab, and check the field type. If it is not the desired field type, add a field of the desired type, use the field calculator to copy the values from the original field to the new field, and delete the field that was converted that contained the incorrect format.
THINGS YOU SHOULD NEVER DO
Just as there are things you should always do, there are things you should never do when formatting Excel tables for use with the Excel To Table tool.
Never use special characters in column names.
Field names should contain only letters, numbers, and underscores—no special characters (e.g., #, !, $). If you use a column name with a special character, ArcGIS will replace the special characters with an underscore. Avoid this problem by renaming columns before conversion so they contain only letters, numbers, and underscores. If you converted an Excel table with special characters in column names, change field names with the Alter Field geoprocessing tool or right-click the table in ArcCatalog, click Properties, and click the Fields tab. Type a new field name that contains only letters, numbers, and underscores.
Never include spaces in column names.
ArcGIS will replace spaces in column names with underscores. If you converted an Excel table containing column names with spaces, use the Alter Field geoprocessing tool in the Data Management toolbox or right-click the table in ArcCatalog, click Properties, click the Fields tab, and type a new field name without underscores.
Don’t use excessively long column names.
Excessively long means column names longer than 64 characters for file geodatabases. Column names longer than the limit for a specific database will be truncated to the maximum length allowed. Rename fields in Excel so they do not exceed the character limit. After conversion, right-click the table in ArcCatalog, click Properties, click the Fields tab, and assign a new field alias for the truncated field.
Never use reserved words in column names.
Do not use ArcGIS reserved words as column names. In particular, do not create column names that use the ArcGIS reserved words ObjectID, OID, FID, Shape_Length, and Shape_Area. Fields with these names are managed by ArcGIS. If you convert an Excel table containing columns with these names, ArcGIS will create fields FID, Shape_Length, Shape_Area and rename the Excel fields with the same names with an underscore and number. For example, ObjectID would become ObjectID_1. If Values or Order are used as column names, those names will be used as field names with a trailing underscore. In some cases, using a reserved word as a column name will cause the field name to be unreadable after conversion and table contents will not be displayed. Using tables containing field names with reserved words may generate error messages when performing tasks such as joins and relates. Before conversion, rename fields so they do not contain ArcGIS reserved words.
Cells should never contain formulas.
Before conversion, replace cells with formulas with cells that have values by copying the cells and pasting them back into their original location using Paste Special > Values. If you converted an Excel table containing cells with formulas, add a field of the desired type, use the field calculator to calculate values for that field equal to the values generated by the formula in those cells and delete the original field containing the formula.
Never use cell values with more than 255 characters.
Only the first 255 characters of a cell are read by ArcGIS. If there are more than 255 characters, the field will be converted to the BLOB type by ArcGIS and the cell value will become <NULL>. You cannot read the contents of a BLOB field. Before conversion, edit the cells so they have no more than 255 characters.
For More Information
For more information on using Excel tables with ArcGIS, visit the ArcGIS Resources website and search on the following topics:
- Understanding how to use Microsoft Excel files in ArcGIS
- Formatting a table in Microsoft Excel for use in ArcGIS
- Excel To Table Tool
- Defining fields in tables
- What are the reserved words for Esri’s file geodatabase?
About the authors
