How To: Format a table in Microsoft Excel for use in ArcMap
Instructions provided explain how to format a table in Microsoft Excel for use in ArcMap. ArcMap does not read the native format for MS Excel (*.xls), but is able to read the following table formats:
- dBase IV
- dBase V
Note: For instructions on formatting tables in Microsoft Access, see "HowTo: Format a table in Microsoft Access for use in ArcMap" in Related Information.
Note: For instructions on formatting a table in Excel to be used directly in ArcMap 9.2, see "HowTo: Join an Excel spreadsheet, .xls, to a feature class in ArcMap" in Related Information.
To format table in Microsoft Excel that can be read in ArcMap:
- Column headings are required in the output file, such as Label, ID, Longitude, Latitude, or Description.
Column headings require the following:
- must contain only letters, numbers and underscores
- must start with a letter
- must not exceed ten characters
- must be formatted as text
Note: For more information about formatting field and table names, see "What characters should not be used in ArcGIS for field names and table names?" in Related Information, below.
- Use Excel to format the columns containing the Longitude (X) and Latitude (Y) values as type NUMBER, with a minimum of eight decimal places.
- Expand all the columns in the table, so the values in the columns are visible. If columns are not expanded, the values will be truncated to 10 characters.
- Delete extra sheets. The DBF, PRN and CSV formats do not support multiple sheets.
- Select all the cells in the populated rows and columns in the table, including the field headings.
- Select File > Save As. Select a supported format from the choices available in Excel and click Yes when Excel warns that certain formatting is not supported in this file format.
- Exit Excel. Click No when Excel prompts to save the changes. If the changes are saved, Excel converts the file to an XLS Workbook, which ArcMap cannot read.
- If the table contains only Latitude and Longitude values, and does not open in ArcMap after following this procedure, highlight the cells of the populated columns and rows, and select File > Print Area > Set Print Area. Repeat Steps 4, 5, and 6 above.
- If a field contains numeric and alpha-numeric values, and saving the file as a DBF in Excel does not preserve the alpha-numeric values, follow the above steps and save the file as Type CSV (Comma Delimited, *.csv). Import the CSV file into Microsoft Access using the steps in "HowTo: Format a table in Microsoft Access for use in ArcMap" in Related Information below.
- What characters should not be used in ArcGIS for field names and table names?
- Import XY data tables to ArcMap and convert the data to a shapefile
- dBASE tables created in Microsoft Excel are missing decimals
- XY Event layer does not display or is not added to the TOC
- Format a table in Microsoft Access for use in ArcMap
- Join an Excel spreadsheet (.xls) to a feature class in ArcMap
- Connect to an Excel spreadsheet in ArcCatalog