Knowledge Base - Technical Articles


Technical Article   HowTo:  Format a table in Microsoft Excel for use in ArcMap

Article ID: 30727
Software:  ArcGIS - ArcEditor 8.1, 8.1.2, 8.2, 8.3, 9.0, 9.1 ArcGIS - ArcInfo 8.0.1, 8.0.2, 8.1, 8.1.2, 8.2, 8.3, 9.0, 9.1 ArcGIS - ArcView 8.1, 8.1.2, 8.2, 8.3, 9.0, 9.1
Platforms: N/A

Summary

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
-PRN
-CSV

Create these files in Microsoft Excel or Microsoft Access.

 For instructions on formatting tables in Microsoft Access, see "HowTo:
Format a table in Microsoft Access for use in ArcMap" in Related Information.


 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.


Procedure

To format table in Microsoft Excel that can be read in ArcMap:

  1. 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

     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.

  2. Use Excel to format the columns containing the Longitude (X) and Latitude (Y) values as type NUMBER, with a minimum of eight decimal places.
  3. 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.
  4. Delete extra sheets. The DBF, PRN and CSV formats do not support multiple sheets.
  5. Select all the cells in the populated rows and columns in the table, including the field headings.
  6. 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.
  7. 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.
  8. 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.
  9. 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.

Related Information


Created: 5/2/2006
Last Modified: 12/15/2009

Article Rating: (1)
If you would like to post a comment, please login

Comments

By Anonymous - 10/22/2009 4:13 PM

The article needs to be updated.

Does this articale apply to Office 2007 and its new file format (.xlsx)?.

Rating: